Whether in a spreadsheet that you are creating, or one that you’ve inherited from someone else, you’ve probably run across those cryptic Excel errors before.
To effectively troubleshoot them, it’s important to be able to understand what they actually mean, so I’ve assembled a list of the 8 errors you’re likely to see in Excel and how you can fix them.
######
The first “error” you are likely to see is a cell filled with pound or hashtag characters. However, it’s not really an error at all.
It occurs because the column isn’t wide enough for the number to display.
There are two ways to fix this:
1) we can either widen the column so there is enough space to display all of the digits, or
2) we can reduce the number of digits in the cell so that it can be displayed within the width of the column.
#NAME?
The second error generated by Excel is the #NAME? error.
This one occurs because Excel doesn’t recognize the name of the function that’s been entered.
In the example below, instead of using the correct function “SQRT”, the function has been entered as “SQR” (without the “T”), which Excel doesn’t recognize.
#VALUE?
The third error is the #VALUE? error.
It occurs when the wrong type of argument is entered into a function.
For instance, entering text instead of a number will generate this error.
#NUM!
A number error occurs because a number has been used incorrectly or the resulting number is too large or small to be displayed.
One common way that this error can occur is if you try to calculate the square root of a negative number. In the image below, the formula is calculating the square root of 0.4 minus 0.5, which is -0.1.
Less commonly, if your calculation returns a result that is smaller than negative 10 to the 307th power or greater than 10 to the 307th power, you’ll see this error as well.
#DIV/0!
This is an easy one, as the error code tells you exactly what is wrong.
Obviously this one occurs when you try to divide by zero. It can also occur when you divide by an empty cell in your formula.
#REF!
The next one is the reference error.
This error tells you that the formula is referring to an invalid cell.
This error commonly occurs because a column or row has been deleted and a cell in that column or row was being referenced in a formula. In the example below, Cell C7 (the value for Final Velocity) is used in the calculation for acceleration.
If Row 7 is deleted, Excel no longer has a value to use in the calculation, so it will return a #REF! error.
#N/A
The N/A error occurs when no value is available or an inappropriate argument was used. Most commonly, it’s associated with lookup functions when the value that’s being referenced cannot be found.
#NULL!
The #NULL! error is uncommon for most people in Excel. It occurs when you refer to the intersection of two areas in Excel that don’t actually intersect.
Wrap Up
It can be really frustrating to encounter an error in Excel. However, if you understand what the codes mean, you are well on your way to diagnosing and fixing the problem in your spreadsheet.
