Errors In Excel.
A Line of Hash (#)
Signs Sometimes referred to as “tramlines”, a line of hash signs usually occurs because a column is
not wide enough to display the numbers in the cell or formula. Widening the column will correct this
problem – you can drag the column heading until the value in the cell appears as it should.
#DIV/0!
This message means you are trying to divide a value by zero – this is mathematically impossible. In
the example at the left we are trying to find the average number of persons per household. All is fine
as long as there is a value greater than zero in cell B3 (Houses). As soon as we change this to a zero
an error message appears in the formula cell (B5). To prevent the error you will need to enter a value
greater than zero into cell B3, the divisor cell.
#VALUE!
In this message Excel is advising that something in the formula is not a value and therefore a
calculation can’t be made. A close examination of the example at the left shows cell B3 contains the
word “three”. Therefore the formula in cell B5 is trying to divide 192,664 (in cell B2) with a word,
which doesn’t make sense. To fix the error, a value (a number) will need to be entered in cell B3.
#NAME?
This message appears when text is found in a formula that can’t be matched to either a legitimate
function or range name. In the example to the left, the formula has been entered as =SOME(B3:B7)
– there is no such function as SOME, and presumably the author should have typed =SUM(B3:B7).
#REF!
error shows when a formula refers to a cell that’s not valid. This happens most often when cells that
were referenced by formulas get deleted, or pasted over.
#NUM!
Excel shows this error when a formula or function contains numeric values that aren’t valid. This
often happens when you’ve entered a numeric value using a data type or a number format that’s not
supported in the argument section of the formula. For example, you can’t enter a value like $1,000 in
currency format, because dollar signs are used as absolute reference indicators and commas as
argument separators in formulas. To avoid the #NUM! error, enter values as unformatted numbers,
like 1000, instead.
#NULL!
This error is shown when you use an incorrect range operator in a formula, or when you use an
intersection operator (space character) between range references to specify an intersection of two
1
ranges that don’t intersect. An intersection is a point in a worksheet where data in two or more ranges
[Link] you’ve used an incorrect range operator, make sure you use:
A colon (:) to separate the first cell from the last cell when you refer to a continuous range of
cells in a formula. For example, SUM(A1:A10) refers to the range that includes cells A1
through cell A10.
A comma (,) as the union operator when you refer to two areas that don't intersect. For
example, if the formula sums two ranges, make sure that a comma separates the two ranges
(SUM(A1:A10,C1:C10)).
If you get this error because you’ve used a space character between ranges that don’t intersect, change
the references so that ranges do intersect.
For example, in the formula =CELL(“address”,(A1:A5 C1:C3)), the ranges A1:A5 and C1:C3 don’t
intersect, and the formula returns the #NULL! error. If you change this to =CELL("address",(A1:A5
A3:C3)), the CELL function returns the cell address at which the two ranges intersect—cell A3.
#N/A
Occurs when a value is not available to a function or formula.
