Are you getting frustrated with formula errors in Excel and don’t know how to fix them?

As someone who delivers training to a wide range of individuals and businesses of all sizes and levels of knowledge, I am regularly asked about how to resolve common Excel formula errors.

So, if you work on Excel spreadsheets, you might be faced with an error code occasionally. This can be for a variety of reasons, be it a fault in your data, or a fault in your formula. There’s a couple of different errors to represent this, and here I can help explain this and how you can fix them.

There are a couple of different formula errors you might see in Excel. Below are just a few of the most common ones and how you can fix them.

1. #Value! error: Try to remove spaces in your formula or data in the cell sheet and check your text for special characters. You also should try to use functions instead of operations.
2. #Name error: Use the Function Wizard to avoid syntactical errors. Select the cell with the formula, and on the Formula tab, click the Insert Function.
3. ##### error: Double click on the header at the top of the cell or side of the column to expand it out to automatically fit the data.
4. #NUM error: Check your numeric values and data types to fix this. This error happens when you put in a numeric value using a data type or a number format that’s not supported with the argument section of the formula.

How to avoid errors

Before getting into the formula errors, you should try to look at how to avoid the error altogether. You should always begin formulas with an equal sign, and make sure that you’re using the “*” to multiply instead of the “x.” Additionally, keep a watch for how you use parentheses ( ) in your formulas. Finally, make sure that you use quotation marks around the text in formulas. With these basic tips, it is likely you will not face the issues that are listed below:

#Value! error

This common formula error happens in Excel when something is wrong with the way your formula is typed. It also can refer to a situation where something is wrong with the cells you are referencing. Microsoft notes that this is considered a general Excel error, so it’s hard to find the right cause for it. In most situations, it’s either a problem with subtraction or spaces and text.

As a fix, you should try to remove spaces in your formula or data in the cell sheet, and check your text for special characters. You also should try to use functions instead of operations or try to evaluate the source of your error by clicking Formulas and then Evaluate Formula and then Evaluate. If all else fails, you can check out Microsoft’s support page, here for additional tips.

#Name error

Another common error is #Name. this occurs when you have put the wrong name into an operation or formula. This means that something needs to be corrected in the syntax. To avoid this error, it’s suggested to use the Formula Wizard in Excel. As you start typing a formula name in a cell or the Formula Bar, a list of formulas matching to the words you have entered displays in a dropdown. Choose the formula from here to avoid issues.

As an alternative, Microsoft suggests using the Function Wizard to avoid syntactical errors. Select the cell with the formula, and on the Formula tab, press the Insert Function. Excel will then automatically load the Wizard for you.

##### error

Third on our list is one you’ve likely seen a lot. With the ##### error, things are easily fixable. This happens when something is wrong with the width of the spreadsheet, and Excel cannot display the data or characters in the width of the column or row as you have it. To fix this problem, just double click on the header at the top of the cell or side of the column to expand it out to automatically fit the data. Or, drag the bars for that column or row out until you see the data appear inside.

#NUM error

Next up is #NUM. In this case, Excel will show this error when a formula or function contains numeric values that are not valid. It happens when you put in a numeric value using a data type or a number format that’s not supported with the argument section of the formula. As an example, $1,000 cannot be used as a value in the currency format. That’s because, in a formula, dollar signs are used as absolute (fixed) reference indicators and commas as argument separators in formulas. Check your numeric values and data types to fix this.

Other errors

We’ve only touched on some of the most common errors, but there are some others which I want to mention quickly. One of these is #DIV/0. This occurs if the number present in the cell is divided by zero or in case there is any empty value in the cell. There’s also #N/A, which means that a formula can’t find what it’s been asked to look for. Another is #Null. This is shown when you use an incorrect range operator in a formula. Finally, there is #REF. This happens most often when cells that were referenced by formulas get deleted or pasted over.

Want to update your Excel skills. Contact me for more information contact me: melisa@directivetraining.co.uk