Hide #n/a errors

[chasethewind]

I have searched everywhere, tried everything (except the one that works) and I still cannot figure how to change a #N/A cell to white, or any color with conditional formating. I tried ISNA, but I cannot get it to work. Any help would be great. Thanks.

 

[Lilliabeth]

You can do that with conditional formatting, using =isna(A1), assuming A1 is the cell in the upper left corner of the range you are formatting.

You could instead edit the formulas that are returning errors so the errors are suppressed without formatting. For example: =IF(ISNA(Your formula here),"",Your formula here)

--Lilliabeth

 

[chasethewind]

Originally I did have the "", but when I chart my values it places a zero value in the cells that was formated to "". I can get one cell to format conditionally with a NA but I cannot get it to search through the entire sheet and change all N/A to a color.

 

[xlbo]

Leave the #N/A in place as it won't show on the chart

then use conditional formatting as Lilliabeth has suggested

Select all cells you want to conditionally format (starting from top left of range)

Assuming that the top left cell in your range is B2, change the Value Is option to Formula Is and use

=ISNA(B2)

set colour to white and you should be good to go

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[combo]

If is for report (printing) purpose, you can (in page setup, sheet tab) switch off printing errors.

combo

 

[chasethewind]

Thank You. I did try selecting all the cells, but instead of "B2" I had "B2:I14", something way different. It works perfectly.