How to use the ISERROR function in Excel to remove the DIV, REF and NA errors that occur.
- For updated video clips in structured Excel courses with practical example files, have a look at our MS Excel online training courses . You can even try the Free MS Excel tips and tricks course.
- To see if this video matches your skill level (see the suggested skill score below) do our free MS Excel skills assessment.
- If you are based in South Africa look at the live courses we offer in Johannesburg and Cape Town.
- - 03:01
- - Skill Level: 4
- - What's my skill level
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.