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.
Reference:
follow us on http://arivilm.blogspot.in
like us on https://www.facebook.com/Arivilm2501/
No comments:
Post a Comment