If Excel detects that your Excel Formula or Function contains an error, it will return an error message (e.g. #VALUE!, #N/A).
The error message that you are presented with, provides information about the type and cause of the Excel formula error. It can therefore assist you with identifying and fixing the problem.
The notes below provide a quick reference guide of what each of the different error messages means.
#####
When you see ##### displayed in your cell, it can look a little scary. The good news is that this simply means the column isn’t wide enough to display the value you’ve inputted. And that’s any easy fix!
How to Resolve This Error:
Click on the right border of the column header and increase the column width.
Also you can double-click the right border of the header to automatically fit the widest cell in that column.
#DIV/0
Excel displays the #DIV/0! error when a formula tries to divide a number by 0 or an empty cell.
How to Resolve This Error:
This error is pretty easy to resolve. Simply change the value of the cell to a value that is not equal to 0 or add in a value if your cell was blank. Here’s an example:
#N/A
Indicates that a value is not available to a formula, typically means that the value you are referring to in your formula cannot be found.
You may have accidentally deleted a number or row that’s being used in your formula, or are referring to a sheet that was deleted or not saved.
For advanced users, one of the most common causes of the #N/A error is when a cell can’t be found from a formula referenced in a VLOOKUP.
How to Resolve This Error:
Triple check all your formulas and be sure to look closely at which sheets or rows may have been deleted or incorrectly referenced. If you have a few formulas linked together, check to see that everything in every formula has a value.
#NAME?
Occurs if Excel does not recognize a formula name or does not recognize text within a formula.
This error value appears when you incorrectly type the range name, refer to a deleted range name, or forget to put quotation marks around a text string in a formula.
How to Resolve This Error:
Triple check all your formulas & references and be sure to look closely at which sheets or rows may have been deleted or incorrectly referenced.
#NULL!
Occur when you specify an intersection of two areas that don’t actually intersect, or when an incorrect range operator is used.
To give you some additional context, here’s how Excel reference operators work:
- Range operator (semi colon):Defines a references to a range of cells.
- Union operator (comma):Combines two references into a single reference.
- Intersection operator (space):Returns a reference to the intersection of two ranges.
How to Resolve This Error:
First things first, check to make sure that you are using the correct syntax in your formula.
- You should be using a colon to separate the first cell from the last cell when you refer to a continuous range of cells in a formula.
- On the other hand, you should be using a comma should when you refer to two cells that don’t intersect.
#NUM!
If your formula contains numeric values that aren’t valid, you’ll see an #NUM! error appear in Excel. Often times this happens when you enter a numeric value that’s different than the other arguments used in your formula.
For example, when you’re entering an Excel formula, make sure you don’t include values like $1,000 in currency format. Instead, enter 1000 and then format the cell with currency and commas after the formula is calculated.
How to Resolve This Error:
Check to see if you have entered any formatted currency, dates, or special symbols. Then, make sure to remove those characters from the formula, only keeping the numbers themselves.
#REF!
This error occurs when you delete a cell referred to in the formula or if you paste cells over the ones referred to in the formula.
That means that you may have accidentally deleted or pasted over a cell that was used in your formula. For example, let’s say that the “Outcome” column references the formula: =SUM(A2,B2,C2).
If we were to accidentally delete the “Number 2” column, we’d see this error:
How to Resolve This Error:
Before you paste over a set of cells make sure that there are no formulas that will be affected. Also, when deleting cells it’s important to double check what formulas are being referred in those cells.
If you accidentally delete a few cells, you can click the Undo button on the Quick Access Toolbar (or press CTRL+Z for PC / Command + Z for Mac ) to restore them.
#VALUE!
This error is most often the result of specifying a mathematical operation with one or more cells that contain text.
How to Resolve This Error:
An easy solution to this error is to double check your formula to make sure that you used numbers only. If you’re still seeing an error, check for blank cells, missing formulas linking to cells or any special characters you may be using.
Using The Error Alert Button
When a formula yields an error value (other than #N/A) in a cell, Excel displays a green triangular error indicator in the upper-left corner of the cell, and an alert options button appears to the left of that cell when you make it active.
If you position the mouse pointer on that options button, a ScreenTip appears, describing the nature of the error value. Also, a drop-down button appears to its right that you can click to display a drop-down menu with the following options:
- Help on This Error:Opens an Excel Help window with information on the type of error value in the active cell and how to correct it.
- Show Calculation Steps:Opens the Evaluate Formula dialog box where you can walk through each step in the calculation to see the result of each computation.
- Ignore Error:Bypasses error checking for this cell and removes the error alert and Error options button from it.
- Edit in Formula Bar:Activates Edit mode and puts the insertion point at the end of the formula on the Formula bar.
- Error Checking Options:Opens the Formulas tab of the Excel Options dialog box, where you can modify the options used in checking the worksheet for formula errors.