ISERROR in Excel

How to use the ISERROR function in Excel to remove the DIV, REF and NA errors that occur

ISERROR in Excel

The ISERROR function allows you to automatically identify where a cell has generated an error, which means you can build in an alternate action. In this segment you’ll learn how to find, activate, and use the ISERROR function to make your models a lot cleaner. In this example we have a simple income statement and what we’d like to do is just work out the percentage change between the years.

  • In this cell we can build a simple formula, we’ll say equals, 2005, divided by, 2004, minus one, (=2005/2004-1)
  • and what well get is a number showing the growth between 2004 and 2005
  • however when we copy this down,
  • we’ll discover that we have an error message here because 2004 is a 0,
  • and what we’d like to do is remove this error message and maybe just replace it with a 0 or a dash. The ISERROR function allows you to automatically check if a cell has generated an error message.
  • So we can click in a cell
  • activate the Function Wizard
  • and find the ISERROR function by going down,
  • you say ok, And what you’ll see is that this will check where the value is an error and it can be any one of these errors. And what you do is
  • you just highlight the cell that you want checked,
  • you say ok
  • and it’ll give you a true or false answer
  • and we can copy that down,
  • so there we are told that this is an error cell. We can now build this into an If function,
  • so again we highlight the cell,
  • activate the Function Wizard
  • and go down to If, The logical test here is that if this cell is true or false, do something.
  • so we’ll click on this cell
  • and well say if this cell equals true, then if it is a true we actually want it to show a dash,
  • if it is false as in this case, we want it to show the actual number,
  • we’re can say ok
  • and now when we copy it down
  • you’ll see that instead of getting the error message, we actually get a nice clean cell showing that there is no answer.

You can use this feature when you know that your model is going to generate some errors and correctly so, but you don’t want them to affect the look of your model.