For All Your Training Needs                                                                      [ Home ] [ Up ] [ Site Map ] [ Search ]

Microsoft Excel
Hide Error Indicator in Cells

Imagine the following scenario ... you are creating a Microsoft Excel worksheet with formulas that will calculate percentages for each month of your fiscal year.  The formulas for months in the future are returning an error indicator of #DIV/0.  This makes sense because you have no data yet for future months and any number divided by zero will return the error indicator.  Your worksheet will look cluttered with #DIV/0 errors until you have all the data figures for the entire year.

There is a way to hide the error indicator yet keep the formula in place.  In this lesson, we will use an IF function to fix the error indicator problem.  Your worksheet will look more attractive and less confusing to read.

Edit Formulas with Error Indicator

• Single click in any cell that contains an error indicator

• In the Formula Bar, click your mouse at the beginning of the selected cell's formula

  • This will place you in edit mode for the formula

  
  Formula that Returns an Error Indicator

Use the IF Function to Hide the Error Indicator

• The IF Function contains three parts

  • A logical test

  • The value to display if the test is true

  • The value to display if the test is false

• In this example we will create an IF function that

  • Tests if the formula returns an error indicator

  • If an error indicator does result, then we want the cell to display a zero

  • If an error indicator does not result, then we want the cell to display the formula's answer

IF Function Formula

• In the Formula Bar, type the following =IF(ISERROR(C8/B8),0,C8/B8)

  • Typing in upper or lower case does not matter

  • Commas are used to separate the three parts of the IF function

  • Logical Test

    • With the logical test, we are checking if the original formula of C8/B8 will return an error

  • Value to Display if Logical Test result is True

    • If there is an error, input a zero in the cell

  • Value to Display if Logical Test result is False

    • If there is no error, input the results of the original formula of C8/B8

Formula with Error Indicator Hidden

Send mail to Complete Training Management with questions or comments about this web site.
Copyright © 2002 Complete Training Management
Last modified: December 03, 2008