Tuesday, November 03, 2009

How to hide #N/A errors in Excel 2007

I work in Excel quite a bit, analyzing data and writing formulas. At times, the formula results in an error displayed as #N/A instead of a value. When I'm working with columns and rows filled with data, these annoying errors can make me go cross-eyed. I created a simple example to show you what I mean (click on any image to display a larger view).

How can I hide these errors from displaying? Many of the help articles and forums out there will suggest using Conditional Formatting and then some =ISERROR() code which hides errors in a single cell or, personally, I've find confusing to follow.

I need to know how to hide these errors quickly without needing to break out a formula since I haven't got all day. I've found an easier way; at least it works for me and will show you how to hide these #N/A errors in 10 steps or less -- the user-friendly way! (Note: you don't want to delete the #N/A errors so when the error is resolved, the value will display. I'll touch on this again after step 6.)

Here is how to hide #N/A errors in Excel 2007 in 10 steps or less:
1) Select the column(s) containing the errors
2) Select Conditional Formatting from the Home ribbon
3) Select New Rule... from the submenu

4) Select a Rule Type: Format only cells that contain  (Here is where other articles will tell you to choose "Use a formula to determine which cells to format" which would be fine and dandy for more complicated selection criteria but, dude, we have a simple error here.)
5) Edit the rule description by selecting Errors from the drop down list of values under Format only cells with: and click Format... (Note the other options like Blanks, Specific Text, etc. for future reference)

6) In the Font tab, select White for the background color under the Color drop down menu (assuming your cell or worksheet background is white; otherwise select a matching color) and click OK until you are back to your spreadsheet.

Notice the #N/A errors are still there but the text color formatted to match the background. When you click out of the column the errors seem to disappear (remember that if the error is resolved and returns a value, the conditional formatting rule will not apply so the text will display--this is why you don't want to delete the #N/A errors just to keep them from showing on the spreadsheet)! Viola--I explained how to do this in 6 steps.

But what about the annoying green flags at the corners of the cells containing the errors? Follow me and I'll show you how to hide those too.

7) Click the MS Office icon at the top left of the Excel window.
8) Select Excel Options

9) Select Formulas from the options list on the left.
10) Under Error Checking, select White from the paint bucket icon where it says, Indicate errors using this color and click OK.

Your spreadsheet should now show ... or should I say hide the #N/A and green flag errors. Pretty simple I think, or at least more intuitive than having to remember a formula.

Next time I'll show you how to reference an array, match data and return a value from the same row to display on a new sheet using named ranges, a couple formulas, =INDEX and =MATCH. Trust me, it's easier than it sounds and will save you a lot of time if you are comparing data or cutting and pasting data that matches.


Anonymous said...

OMG! Thank you for helping us not so savy folks out. I'm in data entry hell right now. But at least my work hours aren't too crazy. Miss you Daly!


Stanpuppy said...

Wow...all those brains and she wears malibu strings bikinis. John is a lucky man

Quan said...

You have great songs, is there any way you can show me your play list? and your excel solution helped alot thank you!

Daly said...

Thanks Quan. In the sidebar on the right, you can click the "+ get tracks" button to get the play list. This link should take you there: http://www.mixpod.com/playlist/66156866 I need to add new songs but haven't had a chance to.

Anonymous said...

Nice! You saved my day...!

Daly said...

Glad I can help :)

Anonymous said...

Thank you very much for a straight forward innovative answer, I am a profecient Excel user, but found this the best of all solutions.

It allows you both to plot without zero's and hide the #N/A in your excel table cell.


Daly said...

You are welcome Bass. I'm glad I wrote down how to do it so I can refer back to these steps when I forget!

Anonymous said...

OMG thank you so much for that easy fix! I've been going cross eyed looking at formulas trying to understand out how to fix it!