As someone who works in a small business or elsewhere, as you work on an Excel spreadsheet, you might end up facing an error code on occasion. 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 in our latest Microsoft 365 guide, we’ll explain how you can fix them.
How to avoid errors
Before getting into the formula errors, we’ll touch on how you avoid them 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, you’ll likely not face the issues we’re about to discuss. But, if you still are, we have your back.
#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, we suggest checking 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 can’t 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 can’t be used as a value in the currency format. That’s because, in a formula, dollar signs are used as absolute 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 we 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.
We hope you found our guide helpful. Feel free to check our other Microsoft 365 content here, and check out our top 5 tips and tricks for Excel, as well.
