Posted: March 12, 2014

PROBLEM: how do I hide errors in a worksheet?  For example the #DIV/0 error or the #N/A error. Not only do they look not so great on the worksheet, they can cause other functions and formulas that include the cell holding the error to bring back an error as well (sigh).

SOLUTION: Start using the IFERROR function to hide the errors. Not only will it hide the errors in your workbook, your other calculations will no longer show errors.

How do I hide errors in a worksheet?

I often get asked “how do I hide errors in a worksheet”? For example if a value is divided by 0 or an empty cell the #DIV/0 error is displayed.  Another example is when a lookup function cannot find a match it will return the #N/A error.

These are fairly normal errors in have their uses and sometimes we need to leave them in our calculations as indicators.

I cover how you can do this and still calculate totals on ranges in my post How do I sum a range of cells that include #N/A or #DIV/0! errors? However leaving these errors in the worksheet can cause problems when you use the cell containing the error in another calculation or in a Pivot Table and sometimes they just need to be hidden.

To hide errors use the very clever IFERROR function.

If you have been using the ISNA, ISERROR or ISERR functions inside an IF function you will appreciate this function.  No more having to copy and paste long formulae multiple times. Just write it once and let Excel do the rest!

In the example below D4 is returning an error because B4 and C4 are empty cells.

Using the IFERROR function the error can be hidden.  Just place the IFERROR function around the calculation.  Insert a comma at the end of the calculation and then the data you would like returned into the cell if an error is calculated.  In the example below “” will return an empty cell.
 

Once B4 and C4 have content an error will no longer be created and the calculation result will be displayed instead.
 

Note: if your file is to be opened using an old version of Excel (2003 or earlier) this function will not work and will instead show an error. I would suggest checking out the ISNA, ISERROR or ISERR functions.

Was this blog helpful? Let us know in the Comments below.

If you enjoyed this post check out the related posts below.

Join our free insiders Group!

Learn how to SAVE TIME and WORK SMARTER, without the 'techie' speak!

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

Join our free insiders club! 

Receive 100+ Steps to Becoming Proficient in Excel

>