Excel Formula Errors (Find and Solve)

Get FREE Advanced Excel Exercises with Solutions!

Excel is frequently used in finance, accounting, data analysis, and project management. However, if there is any error in formulas, that can hamper your work. So, to ensure accurate calculations and error-free work, it is essential to know why the formula errors occur and how to resolve the issue. Therefore, in this article, I will show you 10 common Excel formula errors that occur frequently in Excel, along with the reason and solution of them.

Excel formula error


Download Practice Workbook

You can download the practice workbook here.


How to Find All Errors in Excel

While working with Excel, some errors are common and occur frequently. So, to avoid them, knowing how to find all errors at once is necessary. Click Find & Select, then select Go To Special… from the Home tab. Or you can use the keyboard shortcut Ctrl+G and select Special from Go To box.

Selecting Go To Special from ribbon

From the Go To Special box, check only the Errors option in the Formula section to find only the Formula Errors.

Error Codes

Each error contains an error code that can be found using ERROR.TYPE function. Below there is a list of these error codes.

#NULL! : 1

#DIV/0! : 2

#VALUE! : 3

#REF! : 4

#NAME? : 5

#NUM! : 6

#N/A : 7

#SPILL! : 9

#CALC! : 14

Error codes


10 Common Formula Errors with Reason & Solution

To demonstrate different formula errors, I have a dataset containing a company’s sales data. Here, I have the product name, units sold, unit cost, and total cost of each product.

Dataset

Now, I will show you the reasons and solutions for the 10 most common formula errors in Excel using this dataset.


1. #NAME? Error

Invalid name error, #NAME? , occurs when Excel does not recognize something, i.e., a function name, a misspelled function name or named range, cell reference not matching, etc. For example, in the image below, I wanted to calculate the sum of total costs. But I misspelled the function name resulting in #NAME? error.

#name error occurring for misspelling the function name

Solution:

To solve this error, insert the function name properly. Select the cell where the formula was before, enter the right formula carefully, and you will see the error removed.

Correcting #name error


2. #DIV/0! Error

#DIV/0! or divided by zero occurs when a value is divided by zero. Since any value divided by zero is undefined, Excel shows an error. In the image below, you can see no value in cell C9, meaning it contains 0. So, when I tried to divide the value of D9 by C9, Excel returned a #DIV/0! error.

Divide by zero Error occuring

Solution:

Insert a numerical value instead of 0. Here, I inserted $71.00 as the unit cost of perfume, and we can see the unit amount instead of the error value.

Entering a value in place of zero to avoid divide by zero error

Note: You can also use the IFERROR function to avoid #DIV/0! error.

3. #REF! Error

#REF! Error is a common error. Excel shows it if any cell reference is invalid.

Like in the image below, I calculate the total cost by multiplying Units and Unit Cost values. Now I will delete the Units column by selecting it and clicking Delete Sheet Columns from the Delete drop-down.

deleting a column

Next, you will see that the Total Cost column contains #REF! Error as cell references of the Unit column is invalid now.

deleting column occurred #ref error

Solution:

So, to resolve this error, you need to check if the cell reference is valid and then change it accordingly. Moreover, before deleting or making any change in your Excel sheet, ensure all the formulas are pasted as values to prevent this error.

As I inserted the Units column here, the errors are removed now.

Adding proper data and reference to avoid ref error


4. #NULL! Error

#NULL Error occurs if there is a typo in the formula, such as a space inserted instead of a comma or colon.

For example, an asterisk sign was supposed to be between the cell references in the image. Instead, a space was inserted, resulting in a #NULL error.

Not adding asterisk sign creating #null error

Solution:

Avoiding typos or being careful while inserting formulas is the key to solving this error. Here, I inserted the corrected formula and got the desired result.

adding asterisk sign to avoid #null error


5. #VALUE! Error

When a value is not inserted in an expected or valid format (Number, text, time, date, etc.), #VALUE! error occurs. For example, if a text value is entered where a numeric value is expected, a cell is left blank, or dates are formatted as text.

As you can see, I entered the unit value as “twenty,” whereas it should have been “20,” resulting in #VALUE! error.

text entry instead of numerical value causing #value error

Solution:

Insert the properly formatted value and check the valid type of value while inserting the value to avoid this error. Here, I inserted a numeric value to avoid the #VALUE! error.

entering numerical value to avoid #value error


6. ###### Error

###### error is common though it technically is not an error. You will see most often, after inserting a formula, Excel displays a string of hash signs (####) instead of the output. This happens if the value in the cell is too wide to fit in the column.

Here, the value in cell E5 does not fit in the column width it currently is in, thus showing a #### error.

showing ####### error

Solution:

The solution to this error is to widen the column. To do that, drag the column handle showing the ##### error and pull it to the right side until the error disappears and values appear, or double-click on it to adjust the width.

drag the handle to increase the width of column

Here, the error disappeared as I increased the column width.

increased column width removed ###### error


7. #N/A Error

The #N/A error appears when Excel cannot locate something, i.e., a misspelled name or function, extra spaces, etc. VLOOKUP, LOOKUP, HLOOKUP, and MATCH functions are impacted mainly by #N/A errors, as an incomplete lookup table is one of the most common reasons for the not available error.

For example, look at the image below; here, I was trying to find the total cost for “Hand Wash”, but this product name does not match the product names in the table array. So the lookup function shows the #N/A error.

lookup value not matching with table array creating not available error

Solution:

To avoid the #N/A error, check if the lookup value is available in the table array while working with LOOKUP functions, and for other cases, check for any misspelling or extra space characters.

In my case, I entered “Soap” as a product name instead of “Hand Wash” since the values for “Soap” are available in the table array.

lookup value matching with table array removing not available error


8. #NUM! Error

The #NUM error occurs if the calculated output is either too small, too large, or impossible to calculate, i.e., the square root of the negative number.

Here, the LARGE function is applied to calculate the maximum total cost. But the rank of the large number entered is 11 instead of 1, which caused the #NUM error. So, here the formula mainly tries to find out the 11th large number from the range, which is not possible as there are only 10 entries resulting in a #NUM error.

#num error occurring as the formula returns impossible values

Solution:

The solution to this error is to apply them properly so that the function can calculate properly. I replaced 11 with 1 to find the maximum total cost in the LARGE function.

entring right formula solving #num error


9. #SPILL! Error

In Excel, some functions return spill range, mainly a set of cells containing the output. If these functions run into any entry in a cell where the result was supposed to be entered, then a #SPILL! error occurs.

Like, the UNIQUE function finds the unique product names, which are three in number. So this function needs three cells for displaying the output, but it encounters an entry “John” in the second cell. As a result, it shows #SPILL! error.

Array function returning #spill error because of not having empty cell

Solution:

To avoid this error, check if there is any entry in the adjacent cells of the cell where you are applying a formula that will return a spill range. Also, if a #SPILL! error appears, then check which cell entry is hindering the function from working, select the cell, and press the Delete button.

Deleting extra entry

Finally, the #SPILL! error will disappear, and you will see the desired outcome.

#spill error removed


10. #CALC! Error

While applying a formula that works with an array, if the formula cannot calculate because of misinformation or anything else, then a #CALC! error occurs.

For example, the FILTER function filters the values from the range B5:E14 if B5:B14 matches “Hand Wash.” However, in cell range B5:B14, there is no “Hand Wash” entry, which does not let the FILTER function calculate, resulting in a calculation error.

Array function not finding value creating #calc error

Solution:

The solution to this error is to be cautious while working with the functions that give output in an array. I inserted “Soap” as the product name, which matches the range B5:B14 and filters all the entries of the mentioned product.

#calc error solved


Things to Remember

  • To avoid formula errors while working, you can use the IFERROR function.
  • ##### error does not contain any error code as it is not an error technically. It simply says that you need to widen your columns to make the output visible.

Conclusion

This article demonstrated the reasons and solutions for 10 common Excel formula errors. In addition to that, it also showed how to find all errors easily using the Go To Special option. Furthermore, it also provided a list of error codes in Excel. Excel formula errors help you make your calculations consistent and error-free. So, knowing about them is necessary for working efficiently in Excel. Lastly, I hope this article taught you why and when the Excel formula occurs and how to handle them efficiently.


Frequently Asked Questions

1. How do I turn off formula errors?

If you do not want formula errors to show up, then you can turn them off. To do that, select Options from the File tab. In the Excel Options box, select Formulas from Error Checking section, and uncheck the Enable background error checking check box.

2. How to Find Errors with Automated Error Checking?

By default, Excel highlights some formula errors displaying a green triangle on the top left side of a cell. Click on the triangle, and you will see the explanation of the error.

3. What is an error in Excel formula ####?

Excel shows a #### error if the column width cannot display


Excel Formula Errors: Knowledge Hub


<< Go Back to Errors in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Priti
Priti

Priti Halder holds a BSc degree in Naval Architecture and Marine Engineering from Bangladesh University of Engineering and Technology. She has been a part of the ExcelDemy project for 6 months and during this time, she has written over 30 articles and 5 comments for the platform. Priti is currently employed as an Excel and VBA content developer and provides effective solutions to various Excel-related issues. She is passionate about expanding her knowledge of data analysis and Microsoft... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo