This comprehensive guide delves into the world of Excel Errors and Formula Auditing, providing essential knowledge for troubleshooting and optimizing spreadsheets.
by Mihir Kamdar / Last Updated:
In this guide, you’ll learn how to troubleshoot Excel formulas and fix errors effectively, including:
Fix 9 common Excel formula errors with practical solutions
Master 7 Formula Auditing tools
How to visualise formula relationships and dependencies
Step by step process to evaluate and debug complex formulas
Download our step-by-step tutorial file now by clicking on the icon 
below and follow along to enhance your Excel skills practically and efficiently!
Microsoft Excel is a data analysis and management beast but as spreadsheets get more complex so do the formula errors. Excel’s Formula Auditing tools are designed to help you understand, troubleshoot, and optimize your formulas, so you can get accurate and efficient results while minimizing errors in Excel. This guide will go through the most common formula errors and explain in detail the tools to fix them.
Excel errors can be frustrating and time-consuming to troubleshoot. Understanding the different types of Excel errors can help you avoid them and fix them quickly. Excel errors are usually presented with strange letters and numerals or exclamation marks containing little information about what is happening in the formula.
Excel formula errors like #NAME?, #DIV/0!, or #REF! indicate issues with syntax, data types, or cell references in spreadsheets. Understanding these errors and their solutions is crucial for maintaining accurate calculations and efficient data management in Excel.
Cause: Excel doesn’t recognize a function name used in a formula, often due to typos, incorrect range name formatting, or missing quotes.
Example: Using “SU” instead of “SUM” in a formula.
Solution:
Check for typos in function names
Ensure range names are properly formatted
Use the Insert Function dialog box (Formulas > Insert Function) to avoid syntax errors
Cause: Incorrect use of range operator (space) instead of range operator (:).
Example: =SUM(A1 A3) instead of =SUM(A1:A3)
Solution:
Check for colons (:) in range references
Review formula syntax when combining ranges
Cause: Occurs when a formula tries to divide by zero or an empty cell, resulting in an Excel error.
Solution:
Enter valid numbers in cells used for division
Use error handling functions like IFERROR or IF to manage divide by zero scenarios
Use the AVERAGE function to calculate the mean of a range of cells, which can help avoid division by zero errors
Cause: Appears when a lookup function (e.g. VLOOKUP, HLOOKUP, INDEX & MATCH) can’t find the value.
Example: Misspelling “Judith Mackenzie” as “Judith Mackenzi” in a VLOOKUP function can result in the lookup value not being found.
Solution:
Use the IFNA function to return alternative results when a value is not found
Double check spelling and data consistency in lookup values
Ensure lookup ranges are correctly defined
Cause: Invalid cell reference, often due to deleted cells or incorrect column/row references.
Solution:
Check all cell references in formulas
Adjust formulas if cells or ranges have been moved or deleted
Use named ranges to make formulas more robust to structural changes
Cause: Using the wrong data type in a formula or function.
Example: Trying to do a math operation on text data.
Solution:
Ensure cells referenced in formulas contain the correct data type
Use the VALUE() function to convert text to numbers when needed
Cause: Occurs when a formula returns a number too large or too small for Excel to handle or when using invalid numbers in mathematical functions, requiring you to fix Excel errors.
Solution:
Check for extremely large or small numbers in calculations
Verify mathematical functions are using valid inputs
Break down complex calculations into smaller steps
Cause: Happens when a dynamic array formula’s output range is blocked by existing data.
Solution:
Clear cells blocking the spill range
Ensure there are enough empty cells for the formula to spill into
Rearrange your worksheet layout to accommodate dynamic arrays
Monitor and manage outputs to multiple cells to avoid the #SPILL! error by ensuring no existing data obstructs the output range
Cause: Typically occurs when the FILTER function can’t return a result, often because no matches were found.
Solution:
Include a “Not Found” argument in the FILTER function
Check the filter criteria
Verify the data being filtered has the expected value
Excel’s Formula Auditing Tools provide powerful features for visualizing, understanding, and troubleshooting complex formulas. These tools, including Trace Precedents, Trace Dependents, and Evaluate Formula, help users identify error sources and optimize their spreadsheets for improved accuracy and performance.
Purpose: Find cells with specific properties, including errors or formulas.
How to:
Go to Home > Find & Select > Go To Special
Select “Formulas” and “Errors”
Click OK
Purpose: Show all formulas in the worksheet instead of their results.
How to:
Go to Formulas > Show Formulas, or
Press Ctrl + ~ (tilde)
How to:
Purpose: Find cells that are referenced in a formula (i.e. cells that affect the selected cell’s value).
Select the cell with the formula
Go to Formulas > Trace Precedents
Visual: Excel will draw arrows to the cells used in the formula.
Purpose: Find cells that depend on the selected cell (i.e. cells that use the selected cell in their formulas).
How to:
Select the cell you want to check
Go to Formulas > Trace Dependents
Visual: Arrows will point to cells that use the selected cell in their formulas.
Purpose: Find and fix Excel errors.
How to turn on:
Go to File > Options > Formulas
Check “Enable background error checking”
Click OK
How to:
Go to Formulas > Error Checking
Review and fix each error
Purpose: Find the source of an error in a formula by tracing its precedents to fix Excel errors.
How to:
Select a cell with an error
Go to Formulas > Error Checking > Trace Error
Visual: Excel will draw arrows to the cells causing the error, so you can quickly see where the problem is.
Purpose: Debug complex or nested formulas by stepping through them to fix Excel errors.
How to:
Select the cell with the formula
Go to Formulas > Evaluate Formula
Click “Evaluate” to step through the formula
Advanced Excel formula troubleshooting requires a deeper understanding of Excel formulas and functions. Here are some advanced techniques to help you troubleshoot complex formulas:
The IFERROR function can be used to return a value if an error occurs in a formula. This is particularly useful for handling errors gracefully and preventing them from disrupting your calculations.
Example 1 - #DIV/0! Error
Suppose you have a list of items sold and their respective costs. You want to calculate the cost per item, but some rows have zero items sold, which would result in a #DIV/0! error.
By using IFERROR, you can return a custom message or value instead of the error.
Example 2 - VALUE! Error
You have a list of products with quantities represented as text, and you want to calculate the total cost by multiplying the quantity by the unit price. The VALUE function can convert the text to numbers, but if it encounters text that can’t be converted to numbers, it returns a #VALUE! error.
By using IFERROR, you can return a custom message or value instead of the error.
Example 3 - #NUM! Error
You have a list of numbers and want to calculate each number’s square root. When a number is negative, the SQRT function will return a #NUM! error.
By using IFERROR, you can return a custom message or value instead of the error.
Check your IF syntax: =IF(logical_test, value_if_true, value_if_false). Ensure all arguments are present and correctly formatted. Verify cell references and data types match the expected inputs.
A formula error occurs when Excel cannot calculate a result due to incorrect syntax, invalid references, or incompatible data types. Common errors include #NAME?, #DIV/0!, #VALUE!, #REF!, and #N/A.
To fix a reference error:
To remove an error:
To disable error checking:
Note: It’s generally better to fix errors than disable checking.
To fix a #VALUE error:
Mastering Excel’s Formula Auditing tools is crucial for creating and maintaining accurate, efficient, and error-free spreadsheets. By understanding common formula errors and utilizing the powerful auditing features, you can quickly identify and resolve issues in your Excel workbooks, saving time and reducing frustration.
Regular use of these tools will not only improve the quality of your work but also enhance your overall Excel skills, making you a more proficient and confident user. As you become more familiar with these techniques, you’ll find yourself creating more robust and reliable spreadsheets, capable of handling even the most complex data analysis tasks.
