Categorized | excel tips

Hide Error Value and Error Indicators in Excel

To hide error value and error indicators in Excel you can use any of the following means:
Hide error values using conditional formatting
Hide errors indicators by turning off background error
Hide error values by using IF(ISERROR()) function
Hide error using a custom VBA function

Let’s take a closer look at each type of error handling method.

Hide error values using conditional formatting

Click on the cell that contains an error value. From the menu, select ‘Format’ -> ‘Conditional Formatting’.

selecting-the-conditional-format-tab-for-hiding-errorsIn the box that appears, choose “Formula Is” from the condition drop-down. In the formula box right next to it, enter the formula as =IFERROR(cell_address) where ‘cell_address’ is the cell that you choose. You can also choose the cell by using the mouse. However when you use this method, Excel will convert the cell address in absolute reference ($A$1) rather than A1. This is fine till the time that particular cell is the only one that you want for format. However if you want to extend this format to other cells, you will have to convert the absolute reference ($A$1) to relative reference (A1). To convert the absolute reference to relative reference, while the cursor is in the box that contains the cell address, press F2. Now move the cursor around and delete the extra $ in the cell address. Now click the button labeled ‘Format’. Choose the format in which you want to the cell to appear as when it has an error value. (In our case we have chosen Red but you can choose White to merge the cells with the background and hide the errors from appearing.)

Once you are done, use for format painter option to copy the format from that particular cell to all the other cells in the Excel Sheet.


Hide errors indicators by turning off background error

error-indicating-symbolOne of the biggest irritants that Excel has is the automatic background error checker. It usually appears in the form of a small triangle in the top-left corner of the cell that evaluates to zero.

To turn off this error indicator in Excel, click on ‘Tools’ -> ‘Options’ in the menu and then select ‘Error Checking’ tab. Turn-off the check box which is labeled ‘Enable background error checking’ as shown below.


This will turn off all those pesky little colored triangles from appearing against the cells that currently evaluate to zero.

Hide error values by using IF(ISERROR()) function

Both the above options dealt with hiding errors. However oftentimes it is advisable to eliminate the errors altogether especially when the values in those cells are going to be used by other cells and formulas down the line.

The ISERROR() function can help you identify if a particular expression will evaluate to and error. Now when used with the IF function, (IF(ISERROR())), the become a very versatile combination for handling errors. Like the IF() function, it also has three parts – IF(ISERROR(condition), do_this_when_true, do_this_when_false) where:

condition = is the expression that you want to check (if it will evaluate to zero) and
do_this_when_true = is the value that you want to display when the expression evaluates to true.
do_this_when_false = is the value that you want to display when the expression evaluates to false.

So if a value in a cell has an error value as a result of a particular expression, you can edit that cell and instead write, say =IF(ISERROR(expression), “-“, expression). What this simply does is to ensure that if the expression evaluates to an error, then show a dash “-” otherwise show the normal expression. There are numerous other possibilities that you can explore with this combination.

Hide error using VBA function

A small user defined function can help you identify all the cells with an error and take corrective action. In this example given below, we simply replace the cells that cause contain an error value with a dash (-).

You may want to edit the function to ensure the appropriate type of error handling.

Excel Formula, Excel Chart, Excel Macro, Excel VBA, Pivot Table Excel, Excel Dashboard

What Do You Think ?

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Comments and Trackbacks

  1. TSIOUSTAS wrote:




  1. VLOOKUP Formula Excel - How to use Excel VLOOKUP Function | da TaB is On wrote:

    […] can know more about hiding errors in a worksheet […]

  2. CELL Formula in Excel - How to Use CELL Formula and Examples | Excel & VBA - da Tab Is On wrote:

    […] can know more about hiding errors in a worksheet […]


Keep up with the latest stories - Delivered right to your inbox


English flagItalian flagKorean flagChinese (Simplified) flagPortuguese flagGerman flagFrench flagSpanish flagJapanese flagArabic flagRussian flagDutch flagHindi flagFilipino flagIndonesian flagThai flagTurkish flagPersian flag
treeemap software for excel