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.