Conditional Formatting Question (Hiding "#N/A" err

Excel Bunny

New Member
Joined
Sep 30, 2005
Messages
14
Hello All,

This board has been so helpful in the past so I thought I would try my luck once again.

I am working on a table that drags data from other worksheets using VLOOKUP so sometimes fields in this table show up "#N/A" errors (these are instances when the lookup value cannot be found in the source file).

What I want to do is set Conditional Formatting to display all "#N/A" errors as white text, hence rendering them invisible. I assume a formula will be required but I'm not sure which one. Any ideas on this matter would be much appreciated.

Thank you to the regular posters for providing solutions to a variety of Excel dilemmas - I have learnt much just by scanning through past entries. Cheers.
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
Hi Excel Bunny,

You could do it the other way round and just format the whole column as white font. Then set conditional formatting for the whole column where cells are greater than or equal to 0 appear in black font.
 
Upvote 0
Hi Gra,

Thanks for the prompt reply. My only concern is that this table deals predominantly with text values, not numbers. Is there something I can do with "ISNA" in an IF function?

Cheers
 
Upvote 0
Thanks for the reply Barry

I tried your suggestion, replacing A1 with the range I want to apply the conditional formatting to, but it doesn't seem to have worked:

This is what I tried in the Conditional formatting formula section:

=ISNA($D16:$E27)

Any suggestions as to what this formula is missing? Many thanks
 
Upvote 0
The conditional formatting should still recognise text.

But if you'd prefer to insert another column then you could do something like this:

=IF(ISNA(A1),"",A1)

i.e. that will look up the value in A1 - if it is #N/A then it will leave the cell blank, otherwise it will bring back the value in the cell.
 
Upvote 0
You need to apply the conditional formatting to one cell only...like this

If you select your whole range D16:E27 with D16 active cell then set conditional formatting to

formula is

=ISNA(D16)
 
Upvote 0
Alternatively you can replace the A1 with your Vlookup formula so you don't have to have another cell with a formula in.
 
Upvote 0
Excel Bunny

If you are trying to make the #N/A results invisible, what about eliminating them altogether rather than making them white? To do this, adjust your existing VLOOKUP formula as follows:
=IF(ISNA(Your vlookup formula),"",Your vlookup formula)
 
Upvote 0
Peter, Ste_Moore and Barry,

Thanks for taking the time to help me out. I've got it working the way I want to now.

Peter, your solution to get rid of N/A errors altogether is absolute gold and will come in very handy from now on.

<End of Question>
 
Upvote 0

Forum statistics

Threads
1,212,057
Messages
6,105,652
Members
447,974
Latest member
misspancake

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top