Formula Errors are something that clearly tell you that something is wrong in your excel worksheet. In that sense, they actually help you fix anything that is not intended and get you the output as needed accurately. In this guide we shall see some of the most common Excel formula errors such as #DIV/0, #NAME?, #REF!, Circular Reference, etc., and ways to fix them.
Excel Formula Errors
Formulas are the core of Excel, enabling everything from basic calculations to complex data analysis. However, they don’t always work as intended. If you use Excel frequently, you’re likely familiar with common formula errors like #DIV/0, #NAME?, and #N/A. As you use formulas more, you’ll encounter these errors more often.
Although formula errors can seem alarming, they actually provide valuable information by indicating something is wrong. This is preferable to dealing with a spreadsheet that quietly produces incorrect results, which could be problematic. Most formula errors stem from minor mistakes such as typos, incorrect cell references, or deleting cells that are linked to other formulas. When you encounter an error, Consider what the error message is signaling and systematically troubleshoot to find the cause. . With time and experience, you’ll become better at avoiding these mistakes and fixing errors more efficiently.
#DIV/0! Error
The #DIV/0! error appears when a formula tries to divide by zero, or by a value equivalent to zero.
#NAME? Error
The #NAME? error indicates that Excel does not recognize something. This could be a function name misspelled, a named range that doesn’t exist, or a cell reference entered incorrectly.
#N/A Error
The #N/A error appears when something can’t be found. It tells you something is missing or misspelled. Often, #N/A errors are caused by extra space characters, misspellings, or an incomplete lookup table. The functions most commonly affected by the #N/A error are classic lookup functions, including VLOOKUP, HLOOKUP, LOOKUP, and MATCH.
The best way to prevent #N/A errors is to make sure lookup values and lookup tables are correct and complete. If necessary, you can trap the #N/A error with IFERROR or IFNA and display a more friendly message.
#NUM! Error
The #NUM! error occurs when a number is too large or small, or when a calculation is impossible. For example, if you try to calculate the square root of a negative number, you’ll see a #NUM error.
#VALUE! Error
The #VALUE! error appears when a value is not an expected or valid type (i.e. date, time, number, text, etc.) This can happen when a cell is left blank, when a text value is given to a function that expects a numeric value, or when dates are evaluated as text by Excel.
To fix a #VALUE! error, you need to track down the problematic value and supply the right type of value.
#REF! Error
The #REF! error is one of the most common errors you’ll see in Excel formulas. It occurs when a reference becomes invalid. In many cases, this is because sheets, rows, or columns have been removed, or because a formula with relative references has been copied to a new location where references are invalid.
#NULL! Error
The #NULL! error is quite rare in Excel, and is usually the result of a typo where a space character is used instead of a comma (,) or colon (:) between two cell references.
#### Error
the hash or pound characters (###) appear because the value in the cell is formatted with a long format and do not fit in it. To fix this, just make the column wider. Technically this is not an error, but a formatting problem.
#SPILL! Error
This error occurs when a formula outputs a spill range that runs into a cell that already contains data. To fix this error, move the existing data or add extra space (columns or rows) to make sure that the output of a formula does not spill onto an existing data.
#CALC! Error
The #CALC error occurs when a formula runs into a calculation error with an array.
#BLOCKED! Error
The #BLOCKED! error in Excel occurs when certain resources or functions are restricted.
Circular Reference Error
A circular reference error occurs when a formula refers directly to its own cell, or refers to another cell that depends on the original cell. This creates an infinite loop that cannot be resolved.
How to find find Formula Errors in an Excel Sheet
There are 2 common ways to find errors in an excel sheet
- Go To Special
- Find and Replace
Find errors with Go To Special
You can find all errors at once with Go To Special. Use the keyboard shortcut Control + G, then click the “Special” button. Excel will display the dialog with the options seen below. To select only errors, choose Formulas + Errors, then click “OK”.
Note: Go To Special will only find errors on the current worksheet. To find all errors in a workbook, we have to use Find and Replace
Find errors with Find and Replace
- Open the Find and Replace dialog with the keyboard shortcut Control + F.
- In the “Find what” input area, input the characters “#*” (without quotation marks).
- Next, select “Workbook” for “Within”, and change “Look in” to “Values”.
- Click the “Find All” button.
- Excel will display a list of matching errors in the workbook as seen below.
The asterisk (*) is a wildcard in Excel that will match any number of characters, so the idea here is to find values that start with a hash (#) . Notice the list of errors contains multiple sheets. To navigate to each error, use the “Find Next” button or click a row in the list. You can also enter individual errors directly. For example, you can enter “#N/A”, “#DIV/0!”, #REF!”, etc. to find specific errors.