**See Also: DEBUG / EVALUATE FORMULA ERRORS**

As soon as you have discovered how to use formulas in Excel, you will likely
need to lean how to correct or hide formula errors. The first thing you should
know is what each error type means. Once you understand what each error
value means, correcting the formula becomes a LOT easier. Also note that a Formula can return an error IF a range it references contains an error cell.

To mask errors and return an alternate value in its place, it is best to
return zero rather than empty text (""). This is because zeros are generally
more downstream formula friendly than text.

**HIDE ZEROS**

To hide zeros on the Workbook level go to Tools>Options>View - Zero Values.

**Custom Formats**

Excel sees a cells format as having four Sections. These are, from left to right:

*Positives;Negatives;Zeros;Text*.

To hide zeros cell-by-cell use a Custom Number Format like 0.00;-0.00; where
0.00 is desired the format for non zeros. Note the use of -0.00 for negatives.

**#NULL!**

Often occurs when you specify a intersecting range which in fact does NOT intersect. The space is the Intersect Operator and should be used correctly
like;

=A1:F1 B1:B10

OR with
named ranges

=Range1 Range2

In both cases Excel will return the cell value that intersects A1:F1 and B1:B10. In this case, B2.

However, if we used =A1:F1 B2:B10 Excel would display the #NULL! error as it is
NOT possible for a row 1 range to intersect a column range that starts at

row 2.

**#DIV/0!**

Simply means you cannot divide zero into a number. For example

=A1/A2

would result #DIV/0! IF A2 contains nothing or zero. To correct this one
could use one of 2 methods.

=IF(A2=0,0,=A1/A2)

OR

=IF(ERROR.TYPE(A1/A2)=2,0,A1/A2)

Note the use of the ERROR.TYPE Function. It is important to identify the
error type so you are NOT masking another error type you SHOULD know about.

That is, we could use;

=IF(ISERROR(A1/A2),0,A1/A2)

OR

=IF(ISERR(A1/A2),0,A1/A2)

BUT, it is NOT good practice as you will end up masking most error values

when you SHOULD be masking only the #DIV/0! error.

**Error.Type Function **

For specifying error types. #NULL! = 1 #DIV/0!
= 2
#VALUE! = 3 #REF! = 4 #NAME? = 5 #NUM! = 6 #N/A = 7

**#VALUE!**

Possibly the most frequent error type. Occurs
when the wrong type of
argument or operand (operand: Items on either side of an operator in a

formula. In Excel, operands can be values, cell references, names, labels,
and functions.) is used. For example, you may have;

=A1*A2

and IF either cell had text and NOT numbers, the #VALUE! error would be
displayed. This is why one should NOT change the default horizontal
alignment of data cells. That is, text is always left aligned while numbers
are right aligned by default. If you allow this and then widen a Column, you
can tell at a glance what Excel is seeing as text and numbers.

**#REF!**

This means a non-valid reference in your
formula. Often occurs as the result
of deleting rows, columns, cells or Worksheets. This is why deleting

deleting rows, columns, cells or Worksheets is bad practice. Also check
named ranges if used.

You DO NOT want to mask this error as you SHOULD be aware of it.

**#NAME?**

This error means a Function used is not being
recognized by Excel. Check for

typos and always type Excel Functions in lower case. This way, when you

enter the formula Excel will automatically convert it to upper case, if it

is recognized.

Another common reason is if you are using a
custom function without the code being present in he same Workbook. Or, you are using a function that requires a specific
Excel add-in being installed.
E.g the Analysis Toolpak

On the Tools menu, click Add-Ins. In the Add-Ins available list, select the
Analysis ToolPak box, and then click OK.

If necessary, follow the instructions in the setup program.

As with the #REF! error, you don't want to mask this error.

**#NUM!**

This error occurs if you supply a non valid number to a function argument.
E.g, using a negative number when a positive is needed. Or, using a $, %

symbol with the number.

This error can be masked so long as you are aware of the reason why. Again,
use the Error.Type function as shown in #DIV/0!

**#N/A**

The most common reason for this error is any of the
Lookup functions. It
means Excel cannot find a match for the value it's being told to find. There

are many ways to correct or mask this error out there, BUT most are wrong in
their approach and force a LOT of unneeded over-heads.

Consider placing the Lookup functions on the same Worksheet as the Table (if not
already), then create a simply reference (e.g. =IV1) to the cell(s) to

get the result into the needed Worksheet. Doing this also opens up another
opportunity in that we could now use;

IF(ISNA(IV1),"",IV1)

See Stop #N/A Error

Another reason is when Array formulas are used AND the referenced ranges are not of equal size in each array.

**See Also: DEBUG / EVALUATE FORMULA ERRORS**

**Excel
Dashboard Reports & Excel Dashboard Charts 50% Off
Become an ExcelUser Affiliate & Earn Money**

**Special! Free
Choice** of
Complete Excel Training Course **OR **
Excel Add-ins Collection
on all purchases totaling over $64.00. ALL
purchases totaling
over $150.00 **gets you BOTH! Purchases MUST be made via
this site**. Send payment proof to [email protected] 31 days after purchase
date.

**Instant Download and Money Back Guarantee on Most Software**

**
Excel Trader Package
**Technical Analysis in Excel With $139.00 of **FREE software!**

Microsoft ® and Microsoft Excel ® are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft

**Some of our more popular products are below...**