Beauty may be in the eye of the beholder but, when it comes to Excel, most people would definitely agree that having cells with the following error types being displayed looks very ugly:
In this tutorial, I show you one of the easiest ways to handle these type of errors in Excel. I do this by explaining how to use one of Excel’s most underrated and (at the same time) beloved functions:
By the end of this post, you’ll know everything you need to know in order to use the IFERROR function to improve your Excel workbooks. The following outline shows the contents of this Excel tutorial:
Table of Contents
- What Is The Purpose Of Using The IFERROR Function In Excel?
- Why Is IFERROR An Important Excel Function?
- When Can You Use The IFERROR Function In Excel
- Alternatives To Using The IFERROR Function In Excel
- Syntax Of The IFERROR Function In Excel
- How To Use The IFERROR Function In Excel: An Example
- When Not To Use The IFERROR Function in Excel
- Do you use the IFERROR function? If you do, how do you use it?
Are you ready for this?
Great! Then let’s go…
What Is The Purpose Of Using The IFERROR Function In Excel?
IFERROR is one of Excel’s logical functions. This group of functions uses logical values (TRUE or FALSE) as input or output.
One of the most basic logical functions in Excel is the IF function, which (i) tests for a condition, and (ii) returns one value if the condition is met or (iii) another value if the condition is not met. The IFERROR function works in a similar manner:
IFERROR also tests for a condition (whether a formula or expression returns an error) and returns one thing or another depending on whether the logical value returned by the test is true or false. More precisely, the IFERROR function:
- Checks a formula or expression in Excel.
- If the formula or expression returns an error, IFERROR returns a value, formula or expression specified by you.
- If the formula or expression doesn’t return an error, IFERROR returns the result of the formula or expression you’ve specified.
The IFERROR function works with all of the errors that I’ve listed above: #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME? and #NULL!.
Graphically, this process looks roughly as follows:
You may think (correctly), that there are other ways to achieve similar results using other Excel functions such as combining the ISERROR and IF functions. You may think that the IFERROR function is unnecessary and that there is no need to learn or understand “another” Excel function.
Allow me to explain why IFERROR is an extremely useful function and why, if you’re serious about becoming an Excel expert, you should understand it.
Why Is IFERROR An Important Excel Function?
First, let’s take a look at what some very experienced Excel users and experts say about the IFERROR function:
- If you take a look at this Quora thread, several users mention IFERROR as one of the essential Excel formulas that everyone should know.
- Chandoo, one of the most prominent Excel bloggers and a Microsoft MVP, listed IFERROR as one of the top 10 formulas for aspiring analysts. As if that weren’t enough, he had earlier written that if IFERROR was a person, he would “hug her so hard” that his wife “would get into a cat fight with her”.
Now, surely there must be a reason why people are saying this. To understand why, let’s understand some of the situations where you can use the IFERROR function…
When Can You Use The IFERROR Function In Excel
The main reason to use the IFERROR function is to trap errors, handle them and improve the user experience.
Why is this important?
Glad you asked! To answer this, please imagine the following situation:
Let’s assume that you are the General Manager of a large software company called Power Spreadsheets. Every month you receive a report with the some basic figures related to the sales and remuneration of the 50 sales managers the company employs. Once you’ve checked that report, you forward it to your colleagues of the Board of Directors.
I have prepared an Excel workbook with a sample of such report, which you can get for free by clicking here. When you open the file, you see the following:
Do you like to send or receive an Excel workbook that displays errors such as the ones highlighted above?
If you’re anything like me, you don’t enjoy sending or receiving Excel workbooks that display error messages. My guess is that you would’ve probably noticed the big #DIV/0! errors in the screenshot above, even if I hadn’t included arrows pointing to them.
As a side note: if you’ve sent or shared Excel workbooks that display error messages and feel a little embarrassed about it, don’t worry. Been there, done that; particularly as I was starting to use Excel 😳 . I can definitely relate.
Let’s get back to our main topic to guarantee that you don’t have to share Excel workbooks that display errors in the future. Here is where the beauty of IFERROR lies:
The IFERROR function allows you to, basically, replace errors (such as the ones above) with another value, formula or expression that you specify. In the Excel workbook displayed above you can, for example, instruct Excel to make the cells with “#DIV/0!” appear empty or blank (as in the screenshot below) by using IFERROR.
I explain how you can do this and provide several explanations (including the syntax of the IFERROR function) in the sections below.
In the case above, you’re using the IFERROR function to handle a #DIV/0! error. This error is usually caused when (i) dividing by 0 or (ii) dividing by a cell that has no value.
The last column of this table divides the total remuneration of a sales manager by its total sales. Certain sales managers made $0.00 total sales. Therefore, in those cases, the total remuneration is being divided by 0 and the #DIV/0! error is caused.
Using the IFERROR function for purposes of “fixing” a #DIV/0! error (as above) is one of the simplest but most common situations where you can use the IFERROR function to improve your Excel workbooks.
2 other common uses are the following:
- Use IFERROR to check for errors when using the VLOOKUP function or the INDEX and MATCH functions in Excel. The VLOOKUP function generally returns the #N/A! error when it can’t find what its looking for. A similar thing happens with the INDEX and MATCH functions.
Microsoft MVP Debra Dalgleish explains how you can combine the IFERROR and VLOOKUP functions here. In a similar tone, I explain how you can combine the IFNA and VLOOKUP functions to achieve a similar result in this Excel tutorial.
- Use IFERROR to check for errors when using other reference formulas, such as INDEX and OFFSET. In these cases, similar to what happens with the VLOOKUP or INDEX and MATCH functions, errors may be generated if you try to get data that isn’t actually there.
Chandoo provides a rough explanation of how you can do this here. I will explain the topic further in other tutorials.
Now that you know when using the IFERROR function in Excel may come in handy, let’s take a look at what you would have to do if such function didn’t exist…
Alternatives To Using The IFERROR Function In Excel
Let’s assume that you are working on an Excel workbook and want to handle some errors such as the ones I’ve shown in the previous section. However, imagine that you can’t use the IFERROR function because this doesn’t exist in the version of Excel you’re working with.
You actually don’t have to go back so far to find Excel versions where the IFERROR function didn’t exist. IFERROR was introduced as recently as 2007.
So how did Excel users handled errors such as the ones mentioned above previously? How can you handle one of these errors if you’re using a version that is previous to Excel 2007?
By using several functions in a single formula to achieve a substantially similar result as IFERROR. Let’s take a look, in particular, at the strategies that were used to work with the 3 types of errors I explained in the section above:
- If the IFERROR function didn’t exist, a #DIV/0! error could be handled by using the an ISERROR function nested within an IF function. You can find an explanation of this method at TekRevue.
As you can imagine, this is a messier way to handle errors than using the IFERROR function. Let’s go back to the sample Excel worksheet with the data on the 50 sales managers of Power Spreadsheets and take a look at the formula used to calculate the “Remuneration / Sales” of sales manager Jennifer Evans (fifth sales manager on the list, first with $0.00 total sales). When using the IFERROR function, the formula looks as follows:
When using a combination of the IF and ISERROR functions, the formula looks as follows:
Which formula looks cleaner?
You’ll probably agree with me that the formula that uses the IFERROR function is a simpler and more elegant way to handle possible #DIV/0! errors. In addition to the above, the IFERROR alternative is faster and more efficient than using IF along with ISERROR.
- When using reference formulas, the strategy to handle errors involved creating relatively long formulas with several nested functions.
Excel expert Dick Kusleika shows a possible (and common) use of the IF and ISERROR functions to handle errors when using the VLOOKUP function at Daily Dose of Excel. I will cover this topic in future tutorials but, for the moment, let’s simply carry out a simple comparison using the formula structures suggested at ExcelFunctions.net:
If you didn’t have access to the IFERROR function, the basic formula for handling the error is roughly “IF(ISERROR(VLOOKUP(…)),””,VLOOKUP(…))”. When using the IFERROR function, a much simpler formula such as “IFERROR(VLOOKUP(…),””)” returns the same result.
An alternative to using the IF and ISERROR functions is to use the IF and COUNTIF functions, as explained by Chandoo. In this case, the basic formula is roughly “IF(COUNTIF(…)>0,VLOOKUP(…),””)”.
Allow me to ask again: which formula looks more elegant?
The formula that uses IFERROR is definitely simpler and easier to understand. It additionally allows you to avoid inefficient formula structures such as using 2 VLOOKUP functions.
Overall, the IFERROR function is a simpler, easier to understand and more elegant way of trapping and handling errors than the previously existing alternatives.
By the way: if you can’t use the IFERROR function because you’re still using Excel 2003 or before…
It’s time to update your software.… You can do so easily at the Microsoft Store.
I hope that, by now, you’re convinced that you should use the IFERROR function in Excel and see how useful it can be in the appropriate circumstances. Now, let’s take a look at how you actually use the IFERROR function…
Syntax Of The IFERROR Function In Excel
The basic syntax of the IFERROR function in Excel is “IFERROR(value,value_if_error)”. In order to understand each of the arguments within the function, let’s take a look again at the process followed by the IFERROR function:
The 2 arguments of the IFERROR function are the following:
- “value” is the formula or expression that you want Excel to check for an error. This is a required argument.
The following image highlights the places where the value argument comes into play when using the IFERROR function:
- “value_if_error” is the value, formula or expression that you want IFERROR to return if the formula or expression that is being checked returns an error.
The following image shows the value_if_error argument in the context of the process followed by IFERROR:
As explained by Microsoft here, there are 2 additional things to consider when using the IFERROR function in Excel:
- If either of the arguments in IFERROR (value or value_if_error) is an empty cell, the formula treats it as an empty value.
As explained in exceljet.net, when the value argument is empty, it results in no error. In these cases, Excel usually returns 0.
In the case of the value_if_error argument, the result is that the relevant cell generally displays 0.
- If the value argument in an IFERROR function is an array formula, IFERROR returns an array of results for each cell in the range that was specified as the value argument.
I will explore this topic further in future tutorials. If you want to be notified of future tutorials published in Power Spreadsheets, please enter your email address below:
Now that you know the syntax of the IFERROR function, let’s take a look at how it works in practice…
How To Use The IFERROR Function In Excel: An Example
To see an example of the syntax of the IFERROR function in practice, let’s go back to the Excel workbook that contains data on the sales and remuneration of the 50 sales managers of Power Spreadsheets. In particular, let’s look at the following 2 sales managers: Joe Weber and Jennifer Evans.
Let’s focus on the formulas in column H, where the total remuneration of the sales managers is divided by their total sales. More specifically, let’s take a look at the formulas in cells H8 and H9.
- The formula in cell H8, which applies to Joe Weber, is “=IFERROR(F8/E8,””)”. In this case, Excel returns a value of 50.95%.
- The formula in cell H9, which applies to Jennifer Evans, is substantially the same: “=IFERROR(F9/E9,””)”. In this case, Excel leaves the cell blank.
In both cases, Excel is taking the total remuneration of the sales manager (in column F; cells F8 and F9 respectively) and dividing it by the total sales of that same manager (in column E; cells E8 and E9 respectively). But…
Why does Excel return a value in one case and leave the cell blank in the other?
If you need a hint, take a look again at the Excel worksheet with all the data regarding the sales managers where I didn’t apply the IFERROR function. In particular, look at what Excel returned for the value of “Remuneration / Sales” in the case of Jennifer Evans:
You already know that dividing by 0 can cause the #DIV/0! error to appear. This is precisely what is happening above:
Jennifer Evans didn’t make any sales during this period. Her total sales were $0.00.
Since those total sales are the denominator in the formula that appears in column H (Remuneration / Sales), Excel usually returns the #DIV/0! error unless there is a mechanism to trap and handle the error.
The case of Joe Weber is completely different; he made $52,374.00 in total sales.
Therefore, when Excel divides Joe Weber’s total remuneration by his total sales, it is able to complete the calculation and return a value. In this case, that value is 50.95%.
So, what is IFERROR doing in this example?
You already know the syntax of the IFERROR function, as well as the process it follows to return a particular value or string. In the case of the Excel worksheet with the sales and remuneration data of Power Spreadsheet’s 50 sales managers:
- IFERROR checks the formula “Total Remuneration / Total Sales”. This formula is “F8/E8” for Joe Weber and “F9/E9” for Jennifer Evans.
- If the formula doesn’t return an error, Excel displays the result of applying that formula. This is the case for Joe Weber; Excel displays “50.95”.
- If the formula returns an error, Excel displays the value_if_error specified when entering the formula. This is the case for Jennifer Evans. Since the value_if_error is double quotes (“”), Excel leaves the cell blank.
When Not To Use The IFERROR Function in Excel
At the beginning of this tutorial, I provided several reasons for why you should use the IFERROR function. I also told you why the IFERROR function is among the most useful Excel functions.
This doesn’t mean that you should always be using the IFERROR function to trap and handle errors.
When using the IFERROR function you should always bear in mind the following:
As explained by Microsoft here, the IFERROR function is a “catch-all” formula that evaluates several errors: #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME? or #NULL!. Therefore, if you use IFERROR, Excel treats all of these errors equally.
As a consequence of the above, the value displayed in a particular cell is the same (that determined by the value_if_error you’ve inputted in the IFERROR formula) regardless of the type of error causing it.
To understand why this can be a problem, let’s go back to the sample Excel workbook that includes data about the 50 sales managers of Power Spreadsheets.
You already know that the reason why Jennifer Evans’ “Remuneration / Sales” is empty is because her total sales were equal to $0.00. To trap and handle the #DIV/0! error that usually appears, a formula with the IFERROR function has been created to leave the cell blank.
Let’s assume, for a moment, that the person who types in the data into the Excel worksheet made a mistake when recording the details of Jennifer Evans. Instead of typing “0” for number of units sold, he/she typed the letter e.
Without an IFERROR function to trap and handle the error, Excel displays a #VALUE! error. This error is caused because the formula makes reference to cells that contain different data types. In this case, it affects the Total Sales, the Sales – Remuneration and the Remuneration / Sales formulas.
However, if you’ve wrapped all your formulas with IFERROR functions, Excel simply leaves the affected cells blank. This is what I have done, for illustration purposes, in one of the sample Excel workbooks that accompany this Tutorial.
Notice how, in the last column for “Remuneration / Sales”, Excel leaves Jennifer Evans’ cell blank regardless of whether the error is caused by making reference to cells that contain different data types (a #VALUE! error) or because the denominator in a division is 0 (a #DIV/0! error).
I’m aware that, in this particular example, I am exaggerating. In this case, its relatively obvious that there is a problem that needs to be checked out. This is a relatively small table and, if you saw a bunch of white cells, you’d probably notice it easily.
But I want to make sure this point is clear.
I also guess you can imagine that, if you’re working with a huge data set, it may be more complicated to notice these nuances that are so evident in the screenshot above.
What is the main take-away from all of this?
Be mindful of when you use the IFERROR function. Don’t simply start wrapping all of your formulas with IFERROR. Consider other alternatives, such as using the IFNA function.
After all, there is a reason why Excel makes errors so prominent: they’re there to help you identify and fix them. You don’t want to hide them always. As explained by Dick Kusleika, there’s probably no better way to “get incorrect results than by wrapping every formula in IFERROR.”
In Kusleika’s words: “errors are good“. In fact, some very prominent Excel users such as Microsoft MVP Jon Peltier have said (within the comments of the post I link to above) that they “never use IFERROR”.
So, should you actually stop using IFERROR? Or are there some tips you can use to decide whether to use or not the IFERROR function in Excel?
Let’s take a look at the opinions of some Excel experts:
- Chandoo has written that he uses IFERROR to (i) keep output sheets clean and (ii) formulas simple.
- Microsoft MVP Ian Huitson (in the comments within the post I link to above) only uses IFERROR sparingly on output sheets.
- Dick Kusleika suggests 4 basic guidelines regarding error checking in formulas. For purposes of this tutorial, the most relevant is to try to wrap as little as possible (of a formula) with IFERROR.
I could continue quoting Excel authorities, but the advice generally boils down to the following 3 points:
- As a general rule, use the IFERROR function only in output sheets where you want to keep a clean interface.
- Use the IFERROR function judiciously and sparingly.
- Bear in mind that errors are useful.
If Excel displays an error, it’s usually because your Excel workbook has a problem that needs to be taken care of. In those cases, take care of the error instead of hiding it by using the IFERROR function.
This list doesn’t cover all you need to know in connection with error checking in Excel formulas. That’s a much broader topic that I’ll cover in future tutorials. However, the above should give you a good idea of when it’s a good idea to use IFERROR, and when you should try to avoid it.
By now I’m sure you understand what the purpose of using IFERROR is, how it works and what are some of the situations in which you can use it. I hope that you’ll agree with me when I say that the IFERROR function is a great function to use in order to improve your spreadsheets. It can:
- Make your Excel worksheets cleaner, simpler and more beautiful, thereby improving the user experience.
- Simplify certain formulas, thereby increasing the efficiency and speed of your Excel workbooks.
These, among other, reasons help explain why IFERROR has been generally well received since Microsoft introduced it back in 2007. After seeing how some of the alternatives to IFERROR can be more complicated and inefficient, you’ll probably agree with this view.
However, don’t get used to handle every single error in your Excel workbooks with the IFERROR function. Also, avoid wrapping too many functions in your spreadsheets with IFERROR.
The IFERROR function should generally be used sparingly. Perhaps even more importantly, you must understand when is appropriate to use IFERROR and when you should allow Excel to display errors or use other functions.
Just as the IFERROR function can be powerful and useful when used correctly, it can also be very extremely dangerous when used incorrectly. I hope this tutorial has given you some criteria that you can apply to determine when using IFERROR can be appropriate.
I’ve also provided all the information that you’ll need to start using the IFERROR function. Please go ahead and start using the IFERROR function to start improving your spreadsheets now 😀 .
Remember that, if you want the actual Excel workbooks that I’ve used for the example in this tutorial, you can get them for free by clicking here.
Do you use the IFERROR function? If you do, how do you use it?
Do you use the IFERROR function in your Excel workbooks? Or do you prefer avoiding it?
If you don’t use the IFERROR function, what are the main reasons for this? Which alternative strategies do you use in order to achieve your objectives?
I have introduced some ways in which the IFERROR function can be used alongside other functions such as VLOOKUP, INDEX and MATCH. Do you use IFERROR along with other functions or tools that I didn’t mention in this tutorial? Which ones? Do you couple IFERROR with conditional formatting?
I would love to hear your comments regarding the use of the IFERROR function, so please make sure to leave one below.