If you’ve worked with Excel for any length of time, you’ve probably run into weird error messages in your cells that begin with a hashtag, like #DIV/0! or #REF!. It can be frustrating if you’re not quite sure how to handle it, but luckily, Excel has a built-in function to make your life easier. Today, we’re going to walk through how to use the IFERROR function in Excel to handle these situations without breaking a sweat.

Errors like these don’t always mean something is wrong with your data. Excel just doesn’t know what to do with the instructions you’ve given. So, let’s explore how the IFERROR function can help you clean up your spreadsheets and keep everything running smoothly.

Check out the video tutorial below for a step-by-step walkthrough:

What is the IFERROR Function in Excel?

The IFERROR function is a super handy tool that allows you to tell Excel what to do if it encounters an error during a calculation. This function is a lifesaver when you’re working with formulas that might result in errors, such as division by zero, missing data, or invalid cell references.

Instead of leaving ugly error codes like #VALUE!, you can use IFERROR to display something that makes more sense—like a zero or a customizable message.

Common Errors You’ll See in Excel

Before we dive into the steps for using the IFERROR function, let’s look at some common error messages you’ve likely seen before:

  1. #DIV/0!: This shows up when Excel tries to divide something by zero. It doesn’t know what to do, so it throws this error.
  2. #VALUE!: This means there’s something wrong with your formula or the data types you are using.
  3. #REF!: Happens when a cell reference is not valid, like if you’ve deleted a cell that a formula refers to.

Using IFERROR to Handle Errors in Your Formulas in Excel

Example Scenario

Let’s say you have a table with three columns: fruit, quantity, and total cost. You want to calculate the unit cost by dividing the total cost by the quantity. Normally, this is straightforward—just use the formula =C2/B2.

But what happens if the quantity is set to zero or is missing altogether? Without the IFERROR function, you’ll get the dreaded #DIV/0! error code.

RELATED TOPIC: How to Use the IF Function in Excel

Follow these steps to clean things up using IFERROR function in Excel:

Step 1: Write Your Formula with IFERROR

Instead of writing a simple division formula, wrap it inside an IFERROR function.

Here’s how it looks:

=IFERROR(C2/B2, 0)

What this does is pretty simple. It tells Excel:

  • First: Try calculating C2/B2.
  • But if it can’t do that (e.g., because of a division by zero), then just put a 0 in the cell instead.

Step 2: Apply the IFERROR function to All Rows in Excel

Once you’ve entered the IFERROR function in the first row in Excel, drag the fill handle (the small square at the bottom corner of the active cell) down to apply the formula to all the rows in your table. Now, wherever you would’ve seen an error, Excel will show a zero instead.

Step 3: Custom Messages with IFERROR

But what if you don’t want Excel to just put a zero? Maybe you want a more noticeable message—like “Needs Corrected”—to tell you that something’s off with your data.

You can easily change the second argument of the IFERROR function in Excel to show a custom message:

=IFERROR(C2/B2, "Needs Corrected")

This way, when an error occurs, Excel will display “Needs Corrected” instead of leaving you to scratch your head at #DIV/0!.

RELATED TOPIC: How to use the SUMIF and SUMIFS Functions in Excel

Step 4: Leaving Cells Blank On Error

There might be cases where you don’t want any message at all. Maybe a blank cell would suit your format better.

Simply use two double quotes ("") in the second part of the function to leave the cell blank when there’s an error:

=IFERROR(C2/B2, "")

This approach is great when you don’t want to clutter your sheet with unnecessary warnings or placeholders. Excel will simply return a blank cell if it hits an error.

More Excel IFERROR function Tricks

You can take the flexibility of the IFERROR function in Excel a step further by combining it with other formulas.

For example, let’s say you’re using the VLOOKUP function to look up data, and sometimes the value won’t be found, resulting in a #N/A error. You could combine VLOOKUP with IFERROR, like this:

=IFERROR(VLOOKUP(A2, YourTable, 2, FALSE), "Not Found")

This way, if there’s a missing lookup value, instead of seeing #N/A, you’ll get a “Not Found” message.

RELATED TOPIC: Mastering Excel: Absolute and Relative Cell References Made Easy

Conclusion

You don’t have to let Excel’s error messages slow you down. The IFERROR function gives you control over what happens when formulas don’t go as planned. Whether you want to replace errors with zeros, custom messages, or even leave the cells blank, IFERROR has your back.

Next time those confusing #DIV/0! symbols pop up, just take a deep breath and remember the power of IFERROR. You’ll wonder how you ever got by without it!

If you found this tutorial helpful, make sure to like the video above and subscribe to stay updated with future tutorials on Excel and beyond.

Thanks for stopping by!

Get Started with Microsoft 365

Be sure to subscribe to my YouTube Channel for more Microsoft Excel and Microsoft 365 Tutorials.

This page may contain affiliate links. These are tools I personally use and love. I may receive a small commission should you make a purchase using one of these links. This helps keep the tutorials and website posts coming and up to date. Thank you for your support! For more information please see my full disclaimer.

RELATED ARTICLESMORE FROM AUTHOR