EDUCBA

EDUCBA

MENUMENU
  • Explore
    • Lifetime Membership
    • All in One Bundles
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign up
Home Excel Excel Resources Logical Functions in Excel Tutorial IFERROR Excel Function

IFERROR Excel Function

Madhuri Thakur
Article byMadhuri Thakur

Updated August 19, 2023

IFERROR Function in Excel

IFERROR in Excel

IFERROR method captures errors in the formula and returns an alternative result or formula when an error is found. We can use the If Error function, where we have even a small chance of getting an error, and we can replace the error message with a customized message we want to see or replace that with a blank.

IFERROR is an easy and structured way to capture and handle errors without resorting to complicated IF statements. Hopefully, by the end of this article, you will know everything you need to know to start using the IFERROR function to improve your Excel skills.

ADVERTISEMENT
Popular Course in this category
MS Excel Course Bundle - 36 Courses in 1 | 13 Mock Tests

Start Your Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

Common Errors in Excel

Below given are some of the errors we can relate to while working in Excel.

IFERRORs

It certainly doesn’t look very good, and sadly it’s one of the most common problems people face while writing and using complex formulae in Excel. Whenever Excel encounters something unexpected, which doesn’t sit well with the underlying formula, it returns a value in the form of an error like the ones shown above. Hence, it is always a good idea to cover your expressions or formulae to handle those bothersome corner cases which result in errors. Like every other programming platform, Excel provides an excellent way to handle errors through the IFERROR.

IFERROR falls under the umbrella of Excel’s Logical Functions – as the name suggests, this group of functions uses logical values such as TRUE and FALSE as input or output. To properly understand what IFERROR does, it would help to first look at how the IF function works.

The IF function is one of the simplest logical functions, and it does the following:

  1. It tests for a condition.
  2. Based on the outcome of the test above, it performs an action.
  • It also provides an alternative course of action in case the condition is not met.

The IFERROR function also works in a similar fashion. But in this case, it tests whether formula or expression would evaluate correctly or whether it would result in an error and returns something depending on the result of this test.

IFERROR Formula in Excel

The Formula for the IFERROR Function in Excel is as follows:

IFERROR Formula in Excel

So to summarize, the IFERROR Function in Excel would do the following:

  1. Test an Excel formula or an expression.
  2. If the expression or formula evaluates an error, it provides you with the option to return a custom value that could be an expression or a formula.
  • If the expression or formula doesn’t result in an error, then it will return the result of this formula or expression.

IFERROR can handle any Excel error that one might encounter, including #N/A, #VALUE!, #REF!, and #DIV/0! #NUM! #NAME? and #NULL!

To represent this in the form of a flowchart, it would look like this:

IFERROR Flowchart

The basic objective here is to catch the errors, handle them effectively, and thereby elevate the overall user experience – and that is the main goal.

How to Use IFERROR Function in Excel?

IFERROR Function in Excel is very simple and easy to use. Let us understand the working of IFERROR in Excel with Some Examples.

You can download this IFERROR Function Excel Template here – IFERROR Function Excel Template

Example #1 – Handling #N/A Error

Suppose we have two lists of employees in Columns A and B. In Column B, there are some employees which are not there in Column A. We want to find out the names of those employees who are not in Column A.

Example 1

So we will apply the VLOOKUP formula to the right of Column B. The formula for VLOOKUP is shown below.

VLOOKUP formula Example1-1

We shall write the formula for cell C2.

With VLOOKUP formula Example1-2

With VLOOKUP formula Example1-3

And then, left-click, hold, and drag the mouse to drag the formula down the rest of the list.

IFERROR Example1-4

As we can see, there are some names that could not be found in Column A. Hence VLOOKUP generated a #N/A error for those rows.

With VLOOKUP formula Example1-5

If we want #N/A to be replaced by some other value, such as “This Value doesn’t exist in List A”, then we shall use the IFERROR function.

So we will pass the VLOOKUP formula as the first argument in the IFERROR function – in value.

With VLOOKUP formula Example1-6

Next, we will pass “This Value doesn’t exist in List A” as the value_if_error.

IFERROR VLOOKUP Formula

The result is:

IFERROR Example 1-7

After this, we will left-click and hold the mouse to drag the formula to the entire column. This will copy the formula to the rest of the rows and gives the results.

IFERROR Example1-8

Example #2 – Handling #NULL! Error

Suppose we have 3 values in cells G2, G3, and G4. Our objective is to sum them in cell G5.

Example 2

So we start writing the formula using the ‘+’ operator to add the numbers.

IFERROR Example 2-1

Normally we do this like this:

IFERROR Example 2-2

And this yields the following result:

IFERROR Example 2-3

Now suppose, instead of ‘+’, we entered a space or pressed any other incorrect key in this formula.

IFERROR Example 2-4

It will result in an error.

IFERROR Example 2-5

So to rectify this error, we can use the IFERROR function by passing the summation formula in the first argument – value.

IFERROR Example 2-6

Next, we will pass the correct formula in the value_if_error argument.

IFERROR Example 2-7

This is correct the above error and gives the right result.

IFERROR Example 2-8

It will return the result:

IFERROR Example 2-9

Example #3 – #DIV/O!

Let us imagine a scenario to understand this better. Suppose you are a Director of a large gift store chain with outlets nationwide. You receive a monthly report showing some basic figures related to overall sales achieved in all different states, split among 5 sales managers the company employs. Your job is to check this report and then forward it to your colleagues on the Board so you can together analyze the data and formulate a plan to counter dips or capture further opportunities.

If on opening one such report, you see something like this:

Example 3

Does it look good to send this report as it is to the rest of the Board?

I wouldn’t. The #DIV/0! sticks out like a sore thumb, and for high stake meetings, having these visual sores in your Excel report is a downright embarrassment. So how does one clean this? This is where the IFERROR Function comes to your rescue. The IFERROR function allows you to replace all such errors as shown above with a suitable value, expression, or function of your choice. In the scenario described above, you can leave the cell black using IFERROR Formula.

IFERROR Example 3-1

The IFERROR Function returns the result as:

IFERROR Example 3-2

In the scenario described above, we use the IFERROR function to handle a #DIV/0! Error. This error is caused when either:

  1. dividing by 0 or
  2. dividing by a cell that has no value.

The last column of this table, ‘Contribution Margin Ratio’, divides the Contribution Margin of a sales manager by its Total Sales. In the case above, Sales Manager Anurag made no sales for that month. Thus, in this case, the Contribution Margin is divided by 0 and the #DIV/0! An error is caused. As shown in the example above, we can use the IFERROR Function to ‘fix’ a #DIV/0! Error. This is one of an Excel user’s most widely encountered situations. Another common situation where most users encounter an error is using the VLOOKUP function, which returns #N/A when it can’t find the value it is searching for. Thus, it is a good idea to use the IFERROR Function in such cases to improve the general look of the report.

Now, it is true that there are other alternatives to this, but IFERROR is perhaps the simplest and the most versatile among them. Let’s discuss why IFERROR is the simplest among all the other alternatives that are available in Excel. If, for some reason, IFERROR is not available for use (and it’s not that unlikely because IFERROR was introduced as late as 2007!).

In that case, we would have to use other formulae to achieve the same result, which is more complicated and quite cumbersome to write.

IFERROR Example 3-3

The Result is:

IFERROR Example 3-4

So which formula looks cleaner and easier to write?

IFERROR not only looks more elegant but also provides a very simple way to handle these situations.

Things to Remember

  • Use IFERROR with other functions to cover cases where the functions could return an error. It’s always best to account for these exceptions.
  • IFERROR evaluates a function that accepts as an argument and returns an alternative result in error.
  • IFERROR replaces the value for all types of errors, i.e. you will get the same custom value for all types of errors.
  • If you want to display an alternative value only for #N/A, then consider using the IFNA function instead of IFERROR.
  • IFERROR is not available with old versions of Excel (prior to 2007).

Recommended Articles

This has been a guide to IFERROR in Excel. Here we discuss the IFERROR Formula in Excel and how to use IFERROR Function in Excel, along with Excel examples and downloadable Excel templates. You may also look at these useful functions in Excel –

  1. Errors in Excel
  2. ISERROR Excel Function
  3. VBA IsError
  4. VBA IFError
ADVERTISEMENT
MICROSOFT POWER BI Course Bundle - 8 Courses in 1
34+ Hours of HD Videos
8 Courses
Verifiable Certificate of Completion
Lifetime Access
4.5
ADVERTISEMENT
MS Excel & VBA for Data Science Course Bundle - 24 Courses in 1 | 10 Mock Tests
87+ Hours of HD Videos
24 Courses
10 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
ADVERTISEMENT
All-in-One Financial Analyst Masters Training Program Bundle - 550+ Courses | 300+ Mock Tests | 2000+ Hours | Lifetime |
2000+ Hours of HD Videos
43 Learning Paths
550+ Courses
Verifiable Certificate of Completion
Lifetime Access
4.9
ADVERTISEMENT
All-in-One Data Science Bundle - 400+ Courses | 550+ Mock Tests | 2000+ Hours | Lifetime |
2000+ Hour of HD Videos
80 Learning Paths
400+ Courses
Verifiable Certificate of Completion
Lifetime Access
4.7
Primary Sidebar
Footer
About Us
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Blog as Guest
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

Download IFERROR Function Excel Template

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

EDUCBA

Download IFERROR Function Excel Template

EDUCBA

डाउनलोड IFERROR Function Excel Template

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

🚀 Extended Cyber Monday Price Drop! All in One Universal Bundle (3700+ Courses) @ 🎁 90% OFF - Ends in ENROLL NOW