I am a former maths teacher who was often given any jobs that required the use of Excel. As such, I've taught myself a lot of tricks.
N/A appearing in an Excel spreadsheet
Hiding Error Values
Sometimes when using Microsoft Excel, you find that an error value has appeared in one of your cells. This might be because you are using a formula that has tried to divide by 0 (in which case #DIV/0 will appear) or maybe your formula is linked to a VLOOKUP function that can't find a match for your data (in which case #N/A will appear). These are just two of the several error codes that can appear in Excel.
But why hide them? Why not just delete the formula? Well, in the case of the #N/A error especially, you might have set up your spreadsheet with formulae ready for data that you will be entering in the future. In this case, you want the formulae there ready, but having #N/A written all over the sheet can look messy and unprofessional. In this article I will give a couple of different ways to solve this problem.
Take a look at the table I have set up in the picture above. The table shows eight school students and their end of year target grades (the pink column). The column titled 'Term 1' shows their actual grades for that term. The next column is then set up with a formula involving the VLOOKUP function.
I have used the VLOOKUP function and the table on the right to convert the students' letter grades into numbers so that I can then display the difference between their target and actual grades in the column titled 'Difference'.
This has worked well for most of the students in term 1. We can quickly see the difference between their target and actual grades and who needs help, who is above target etc.
Unfortunately John Surtees was absent when the class did their end of term test, so he does not have an actual grade for term 1. This has then returned #N/A in the 'Difference' column as the VLOOKUP formula cannot find anything to match to. The term 2 and 3 columns are the same for everybody.
This looks messy and I want to hide all of the error messages without having to delete my formula from all those cells.
Let's have a look at how we can do this.
Method 1 - Using formatting to hide the error
By using the IFERROR function, you can convert any errors into a number and then use conditional formatting to hide these.
Using the IFERROR function
Using the =IFERROR function
The =IFERROR function works by returning an item of your choice if your formula returns an error.
In my example in the picture above, I have entered the following formula:
I have the same VLOOKUP formula as before contained within the brackets of the =IFERROR function as so:
this is then followed by a comma and a 100. The 100 is what I have chosen for Excel to return if an error appears. I could have chosen anything and put it after the comma, but you will see why I have chosen a number in a moment.
The final formula looks like this:
=IFERROR(my formula, 100).
Excel will now return the answer to my formula if it works, or a 100 if it doesn't work.
To complete the fix, we now need to add some formatting so that any 100 will be hidden.
Using conditional formatting
1. Highlight all of your cells and click 'Conditional Formatting', then 'New Rule'.
2. Select 'Format only cells that contain', then make sure the drop box below this contains 'equal to' and you enter '100' next to this. (This tells Excel to format any box that contains 100).
3. Click 'Format' and then the 'Number' tab. In the category menu click on 'Custom' and type ';;;' into the type box. This tells Excel to take any box containing the 100 and leave it blank.
4. Click 'Ok'. All of you #N/A cells should now be blank, but will continue to work as normal as soon as you input data that allows your formula to have an answer.
Method 2 - Turn the text white
This method is a little less satisfying, but a lot quicker and easier to do. The #N/A will still be displayed, but as the text will be white, you won't be able to see it.
1. In the home tab, select 'Conditional Formatting' and then click 'New Rule' as we did in method 1.
2. Select 'Format only cells that contain' and then choose 'Errors' from the drop down box.
3. Click 'Format' and then go onto the 'Font' tab. On the drop-down colour menu, change this to 'white' (or whichever colour the background of your cell is) and then click Ok.
Your error messages will have disappeared.
This article is accurate and true to the best of the author’s knowledge. Content is for informational or entertainment purposes only and does not substitute for personal counsel or professional advice in business, financial, legal, or technical matters.
xneelhtakx on August 10, 2018:
i uses if(iserror though. and i find it amazing awesome and useful.