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.

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

10 Common Formula Errors with Reason & Solution

Method 1 – #NAME? Error

Solution:

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.

Method 2 – #DIV/0! Error

Solution:

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.

Method 3 – #REF! Error

Solution:

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.

Method 4 – #NULL! Error

Solution:

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.

Method 5 – #VALUE! Error

Solution:

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.

Method 6 –  ###### Error

Solution:

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.

The error disappeared as I increased the column width.

Method 7 – #N/A 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.

We entered “Soap” as a product name instead of “Hand Wash” since the values for “Soap” are available in the table array.

Method 8 – #NUM! Error

Solution:

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.

Method 9 – #SPILL! Error

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

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

Method 10 – #CALC! Error

Solution:

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.

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