Formulas are the heart of Excel.
They can do everything from basic math to complex data analysis.
But sometimes, they don’t work as expected.
This is much better thannot knowing.
The most disastrous Excel problems come fromnormal-lookingworksheets that quietly returnincorrectresults.
When you run into a formula error, don’t panic.
Stay calm and methodically investigate until you find the cause.
Ask yourself, “What is this error telling me?”
Experiment with trial and error.
This section shows examples of each formula error, with information and links on how to correct the error.
#DIV/0!
error
As the name suggests, the #DIV/0!
error appears when a formula tries to divide by zero, or by a value equivalent to zero.
You may see a #DIV/0!
error when data is not yet complete.
you’ve got the option to use theIFERROR functionto trap the #DIV/0!
and display a more friendly message if you like.
More:How to fix the #DIV/0!
error
#NAME?
error
The #NAME?
error indicates that Excel does not recognize something.
For example, in the screen below, theVLOOKUP functionin F3 is misspelled “VLOKUP”.
VLOKUP is not a valid name, so the formula returns #NAME?.
To fix a #NAME?
error, you must find the problem, and then correct spelling or syntax.
For more details and examples,see this page.
It tells you something is missing or misspelled.
Often, #N/A errors are resulting from extra space characters, misspellings, or an incomplete lookup table.
VLOOKUP will work normally and retrieve the item cost.
More information:How to fix the #N/A error.
#NUM!
error
The #NUM!
error happens when a number is too large or small, or when a calculation is impossible.
The formula in C5 returns the #NUM!
You might also run into the #NUM error if you reverse start and end dates inside theDATEDIF function.
In general, fixing the #NUM!
error is a matter of adjusting inputs as required to make a calculation possible again.
More information:How to fix the #NUM!
#VALUE!
error
The #VALUE!
error appears when a value is not an expected or valid throw in (i.e.
date, time, number, text, etc.)
error if you create anarray formulaand forget to enter the formula with Control + Shift + Enter.
To fix a #VALUE!
error, you oughta track down the problematic value and supply the right key in of value.
For more details and examples,see this page.
#REF!
error
The #REF!
error is one of the most common errors you’ll see in Excel formulas.
It takes place if a reference becomes invalid.
For example, in the screen below, the formula in C8 was copied to E4.
errors can be somewhat tricky to fix because the original cell reference is gone forever.
If you delete a row or column and then see #REF!
errors, you should undo the action immediately and adjust the formulas first.
More details on #REF!
#NULL!
error
The #NULL!
error is reporting that the two ranges (C3 and C7) do not intersect.
More:How to fix the #NULL!
To fix this error, just make the column wider.
Note: Excel won’t display negative dates.
If a formula returns a negative date value, Excel will display #####.
More:How to fix ##### errors
#SPILL!
Because D5 contains “apple”, the operation is stopped and the formula returns #SPILL!
Details:How to fix #SPILL errors
Video:Spilling and the spill range
#CALC!
error
The #CALC error takes place if a formula runs into a calculation error with an array.
error
The #BLOCKED!
error in Excel happens when certain resources or functions are restricted.
Because Excel 4.0 macros are disabled in the Trust Centered, Excel returns the #BLOCKED error.
For a more complete explanation seeList sheet names with a formula.
This creates an infinite loop that cannot be resolved.
Circular reference errors do not appear on the worksheet like other errors in Excel.
To find circular references, navigate to Formulas > Error checking > Circular references.
For more details seeHow to fix a circular reference error.
How to find formula errors
There are three basic ways to find formula errors in Excel.
The first way is simple observation.
Because formula errors are displayed directly on the worksheet they are easy to spot in many worksheets.
To select all errors on a given worksheet, you’re able to useGo To Special.
To generate a list of all errors in the entire workbook, you might useFind and Replace.
Both options are explained below.
Use thekeyboard shortcutControl + G, then nudge the “Special” button.
Excel will display the dialog with the options seen below.
Once selected, you might use the Tab key to move through the selection one cell at a time.
If no errors are present, Excel will return the message “No cells were found”.
Note: Go To Special will only find errors on the current worksheet.
To find all errors in a workbook, see the next section below.
Notice the list of errors contains multiple sheets.
Notes: (1) The search string “#*!”
will not find a #N/A error, which does not end with an exclamation point (!).
(2) you might also enter individual errors directly.
For example, you’re free to enter “#N/A”, “#DIV/0!
“, #REF!
to find specific errors.
As you find and fix the core issue(s), things often come together quickly.
go for the links below for more details:
