Formula errors can be frustrating, but they are also useful. The error messages may seem cryptic, but they actually tell you when something is wrong with a formula. This article will go through all 9 formula errors and how to correct them.

Which formula errors exist?

When working in Excel, there are 9 error codes you are likely to encounter. Below, each error and how to fix it will be explained.

1. #DIV/0!

As the name suggests, this error appears when a formula contains division by 0, or by a value equivalent to 0. This error often occurs when a cell does not contain any data. In Excel, empty cells are treated as 0. In many cases, empty cells or missing values are unavoidable. In such situations, you may want to consider using the IFERROR() function.

You may also encounter this error when using AVERAGEIF() or AVERAGEIFS() if the criteria do not match any cells in the range.

2. #NAME?

This error indicates that there are elements in the formula that Excel does not recognize. It could be a misspelled function name, a named range that doesn’t exist, or an incorrectly entered cell reference.

In the example above, we can see that the AVERAGE() function is misspelled as MVERAGE(…), and therefore the #NAME? error appears. If you encounter this problem, you should review the spelling and syntax of the formula or function.

---

3. #N/A

This error most often occurs when using one of the lookup functions or a formula that relies on these functions. It usually means that the value you are searching for is missing or misspelled.

To avoid this problem, make sure that lookup values and lookup tables are correct and actually exist in the dataset. Also keep in mind that the error may sometimes come from the dataset itself, rather than the function or formula. For example, it could be a misspelled name entered into the data source, while the function is spelled correctly.

4. #NUM!

This error occurs when a number is too large or too small, or when a calculation is impossible. An example is trying to calculate the square root of a negative number, or using a format not supported by a particular function. For instance, some functions cannot process values formatted as dates.

Often, fixing the issue is relatively simple. You may need to adjust the input to a function or review the calculations being performed. If you don’t understand what’s going wrong, it might be worth brushing up on your math skills.

5. #VALUE!

This error appears when a value does not have the expected or valid format. This can happen if the formula includes an empty cell or when dates are evaluated as text by Excel. Again, it’s important to make sure that your values are in the correct format.

As can be seen in the example, here we get a #VALUE! error for the formula = 9 + x.

In earlier versions of Excel, this error could also occur if you forgot to press Ctrl+Shift+Enter when entering an array formula. However, this is no longer relevant if you are an Excel 365 user.

To fix this type of error, evaluate your formula and make sure that all inputs have the correct format, and that your formula does not include empty cells.

6. #REF!

This is one of the most common errors and occurs when a formula refers to a cell that is not valid. It often happens when a reference becomes invalid—perhaps because cells or columns have been deleted, or because a formula with relative references has been copied to another location where the references no longer make sense.

#REF! errors can be tricky to fix, since the original cell reference may be gone. If you delete a row or column and a #REF! error occurs, you should undo the deletion and adjust your formulas first.

BONUS: #-error

This is not really an error, and it’s very easy to fix. Sometimes you may see a series of number signs (####) in a cell. This happens when the column is too narrow, and the result doesn’t fit inside the cell.

As mentioned, this is easily corrected. Simply adjust the column width, and the result will be displayed in the cell.

7. #NULL!

This is a rather uncommon error and is usually the result of a typo. In the example below, you can see that a space is used instead of a colon.

A colon is used between two cell references to indicate a cell range. With this error message, Excel is telling the user that the two cell ranges D3 and D13 do not intersect. As shown, this is an easy problem to fix.

8. #SPILL!

The #SPILL! error occurs when a formula outputs a spill range that extends into a cell that already contains data. This is best explained with an example.

The UNIQUE() function retrieves the unique names from the array on the left. However, here a value is “blocking” the function, and the function instead returns #SPILL!. If you remove the value that blocks the function’s output, the function will work normally again. If you have never encountered this error before, it may be because you are using an older version of Excel.

9. #CALC!

The #CALC! error can occur when performing calculations and operations using arrays. In the example below, we are requesting data from the group “Production” which does not exist, and therefore we get the #CALC! error.

Fix this error by adjusting the filter criteria to return a valid result.

What is Excel’s Error Checking?

A formula error can result in many other formula errors. Therefore, it can be a good idea to use Excel’s Error Checking to address the root problem.

To use Error Checking, go to the Formulas tab and click Error Checking. Try to trace the error back to its source—use Trace Error, which is one of the two options under the Error Checking button. To troubleshoot the formula, you may need to break the formula into parts. Once you’ve done this, you can hopefully correct the error.

How do you fix multiple formula errors?

You can find all errors at once by using the shortcut Ctrl + G. Then click Special. A dialog box with many options will appear. Select Formulas and Errors, and then click OK.

Can I troubleshoot with a function?

There are ways to prevent errors, especially when you know that error messages are likely and you want to stop them from showing. There are two options – the functions IFERROR() and ISERROR(). With this approach, you check for errors and provide an alternative when an error is detected.

Closing words

When you break down the errors in this way, you’ll notice that most of them are very simple to solve. What can complicate things, of course, is if you have a lot of data and/or many formulas that in some way depend on each other.

It should also be mentioned that the last two errors – #SPILL! and #CALC! – are only relevant for Excel 365 since they relate to dynamic arrays.

In the course Excel Functions Guide, you’ll learn plenty of functions, but also how to troubleshoot and fix formula errors.

/Niklas at Learnesy

“In this lesson, we will go through a few different errors and how to handle them, namely #NUM!, #NAME?, and #VALUE! errors.

The #NUM! error occurs when the value in a cell is too wide for the column. To fix this, you can simply adjust the column width. Remember, you can double-click the right edge of the column header to automatically resize it to fit the widest cell.

The #NAME? error happens when the text in a formula is not recognized by Excel. To fix this, review the cell containing the error for any spelling mistakes.

The #VALUE! error occurs when the wrong type of argument has been entered into a formula. To resolve this, make sure all argument types match… or ignore certain cells by using a function.

In this lesson, we have looked at a few different errors and how to correct them.”