How to Fix Excel Formula Errors?

Microsoft Excel is one of the most powerful and popular applications of the Office suite, owing to its numerous features that help to perform a variety of task. Excel is useful in any industry for data management, data analysis and other business tasks. The ability to perform complex calculations using a standard formula in Excel saves you time and enhances the accuracy of your calculations. However, Excel comes with its share of errors, especially when you are working with formula. Misplaced digits, wrong data formats or a simple spelling mistake can lead to formula errors, and this can be frustrating at times. In this article will look at how to fix formula errors in Excel.

Different Types of Excel Formula Errors

There are about 7 common errors that you may experience when working with Excel formula. These include:

#VALUE! – If the formula encounters wrong data type.
#NUM! – Occurs when there is an improper number supplied to the formula.
#NAME? – If you are trying to use a function that does not exist.
#DIV/0! – If you divide a number by zero or the other way round.
#REF! – Missing reference in the formula.
#NULL! – If you supply an invalid parameter to a function.
#N/A – Occurs if the function fails to find the required data.

How to Fix Excel Formula Errors?

Depending upon the type of formula error, you can follow one of the below solutions to fix.

1. Invalid Name (#NAME?) Error

In most cases, this will happen if you have misspelt the formula. For example, if you want to get the average numbers in cells A2 to E2, by using “=Averange(A2: E2)” you will get this error.

To fix this error, click the cell containing the formula and check whether you have spelt every word correctly. If not, correct the spelling mistake on the formula bar and this will remove the error.

If you are not sure about the spelling or the spelling is ok, you can resolve the error by having Excel insert the formula for you.
Click the cell that you want to insert the formula, go to the “Formulas” tab and from the tools ribbon, click “Insert function.”
Look for and click the function on the list and then click the “Ok” button.

2. Invalid Cell Reference (#REF!) Error

This occurs if you move or delete a cell that is being referenced in a formula. For instance, if we have we use the formula, “=D4+E4” and then delete column D, we will get the #REF error.

In case you deleted the reference cell, you may undo the action to resolve the error.
If you are moving a data set involving some formulas, paste the formulas as values. Right-click the cell where you need to paste the data and select the paste as values option.

3. Error in Value

Excel returns the #Value error if it encounters a non-numerical argument or wrong data type in the formula. For example, if Excel finds a character or text where it expects a numeric value.

This type of error can be hard to fix as it can result from a number of mistakes depending on the function you are using.
An easy way to resolve the Value error is to close check the formula and ensure that you have supplied numerical data to the functions. Replace any text or special character with a number (maybe ‘0′) to fix the issue.

4. Removing Spaces Causing Error in Value

If some cells contain spaces or even worse hidden spaces, you are likely to encounter the #VALUE! Issue.

To remove the hidden spaces, highlight the range containing your parameters.
On the “Home” tab, expand the “Find & Select” option and select “Replace”.

On the resulting dialogue box, find for space and replace with nothing. You can click the “Replace all” button to remove all the spaces at once.

5. Divide by Zero Error

The #Div/0! occurs when you divide a number by zero. To fix the error, you can use the function “IFERROR” with the formula.

6. Null Error

To resolve the null error, you need to review the formula and correct any invalid parameters such as incorrect or missing separators and so on.

For instance, in the example shown, you need to add the “:” between the supplied cells to indicate the range for which you want to calculate the sum correctly.

Conclusion

Both entry and expert users can experience a range of errors when working with Excel. A simple single mistake can cause lead to a serious error messing up your entire spreadsheet. Therefore, it is important to understand the different types of Excel formula errors and learn how you can fix them. Above we have discussed the common errors that you may encounter in Excel formula and their fixes. In case you need to know more details about the error, click the yellow icon beside the error to display more information and options.

How to Fix Excel Formula Errors?

Different Types of Excel Formula Errors