Hiding errors

Challenge: I want to hide error values.

Error values are pretty ugly. #N/A, #VALUE, #REF etc. When the eye sees them, it triggers an ever so slight wince. They occur when something's awry: when lookups don't yield any results, when cells being referenced no longer exist etc.

In the past, I used to cater for such anomalies in formulae. In the most common example, I used to use an IF statement to get around VLOOKUPS that yielded nothing.

=IF(ISNA(VLOOKUP(A2,Range,2,0)),"",VLOOKUP(A1,Range,2,0))

In human speak, if the lookup brings back "not applicable", leave the cell empty, otherwise bring back the lookup value.

This meant for woefully inefficient formulae and tiresome repetition on the part of their author (me).

Thinking about the problem, I figured it probably makes more sense, both from a presentation and a memory perspective, to cater for this circumstance using a formula-based conditional format, using the formula:

=ISERROR(A1)

As for the format, change the number format to ";;;" (without the quotes). This hides the contents of cells that meet the criterion, meaning that those ugly error messages won't appear.

Conditional formatting of number formats was only introduced in Excel 2007, so this tip is only applicable in more recent versions, I’m afraid.

• Tweet