IFERROR – Replacing error messages in Microsoft Excel with blanks

If you have to divide figures in one column by figures in another column, you may not want the error message #DIV/0 to appear if one of the figures in a second column is a zero. Or, if you are using VLOOKUP, you may not want #N/A to appear if what you are looking up does not appear in your data table.

I will demonstrate first of all with a division, then with a VLOOKUP.

Select the cell for the first of your division calculations.

Type in an equals sign as all formulae and functions start with an equals sign.

Now type IFERROR followed by an opening bracket (; now type in the formula to be checked, then type a comma, then your required result if there would be an error, followed by a closing bracket. Then click the tick to the left of the formula bar or press the Enter key.

So, we might have =IFERROR(b2/c2,””), which will return a blank if the formula result would give an error message; or =IFERROR(b2/c2,”Check data”), which will return the text Check data if the formula result would give an error message.

The formula can then be replicated down the column using the fill handle in the usual way.

In a VLOOKUP, if what is being looked up is not present in the larger dataset you will get the error message #N/A. If we want to set up a VLOOKUP to include the possibility of many rows no matter how many individual items there are, we might reproduce our VLOOKUP function down several rows to begin with, then if there are less individual items to look up than number of rows, it may look slightly messy. In the example shown here, I have set up my worksheet to allow for the possibility about being asked about 10 wine codes, but I might be asked for rather less than this.

In this case the IFERROR function can also be used, as shown below.

Find out more about our Microsoft Excel training London, UK, our Microsoft Excel training in Dorset, Wiltshire and Hampshire or our Microsoft Excel training Sydney, Australia.


About jdonbavand

I am a trainer of Microsoft Office, Microsoft Project and Crystal Reports. I have called my blog "If Only I'd Known That...." because I hear it so many times in training sessions. In fact, if only I had a £100 (or 150 Aussie dollars)for every time someone says "If only I'd known that." ....
This entry was posted in Microsoft Excel, Microsoft Excel 2003, Microsoft Excel 2007, Microsoft Excel 2010, Microsoft Excel 2013, Microsoft Excel 2016 and tagged , . Bookmark the permalink.

One Response to IFERROR – Replacing error messages in Microsoft Excel with blanks

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s