10 Excel Formula Errors: Find It and Solve It

How to Find All Errors in Excel

While working with Excel, some errors are common and occur frequently. Find all errors at once is necessary. Click Find & Select, and select Go To Special from the Home tab. 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

Method 1 – #NAME? 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

Method 2 – #DIV/0! Error


Insert a numerical value instead of 0. We 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: Uuse the IFERROR function to avoid #DIV/0! error.

Method 3 – #REF! Error


Check if the cell reference is valid and then change it accordingly. Before deleting or changing your Excel sheet, ensure all the formulas are pasted as values to prevent this error.

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

Adding proper data and reference to avoid ref error

Method 4 – #NULL! Error


The key to solving this error is to avoid typos or be careful while inserting formulas. We inserted the corrected formula and got the desired result.

adding asterisk sign to avoid #null error

Method 5 – #VALUE! Error


To avoid this error, insert the properly formatted value and check the valid type of value while inserting it. We inserted a numeric value to avoid the #VALUE! error.

entering numerical value to avoid #value error

Method 6 –  ###### Error


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

The error disappeared as I increased the column width.

increased column width removed ###### error

Method 7 – #N/A Error


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.

We 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

Method 8 – #NUM! Error


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

entring right formula solving #num error

Method 9 – #SPILL! Error


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. If a #SPILL! the error appears, check which cell entry is hindering the function from working, select the cell, and press the Delete button.

Deleting extra entry

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

#spill error removed

Method 10 – #CALC! Error


The solution to this error is to be cautious while working with the functions that give output in an array. We 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.

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

Download Practice Workbook

You can download the practice workbook here.

Excel Formula Errors: Knowledge Hub

<< Go Back to Errors in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!

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