problem conditional formatting?

B

_Bigred

I have a sheet that has approx 7,000 rows.

Until I get all the data into it many columns will contain: #DIV/0! or
#VALUE!

I would like to do some conditional formatting to hide this error values,
does anyone know how I can do it?

TIA,
_Bigred
 
M

Max

Select entire sheet (with A1 active), and apply the CF as
Condition 1: =ISERROR(A1)
Format the font color white (to mask)
 
T

Trevor Shuttleworth

One way:

Conditional Formatting | Formula is: =ISERROR(cell)

=ISERROR(L21) (for example)

Format the font to be white (or the same as the background colour in the
cell)

Regards

Trevor
 
M

Max

Condition 1: =ISERROR(A1)
Just to clarify that the above line refers to
the "Formula Is" option for condition 1 ..
 
J

JLatham

You've got two answers giving solution exactly as you asked for it: via
conditional formatting. I'll offer a different solution: back in your
formula itself!

Lets say you have a formula like this:
=A1/A2
and it's giving you a #DIV/0! error because nothing is in A2, you could
change that to
=IF(ISERROR(A1/A2),"",A1/A2)
and that would suppress display of the any error notations also.

See Excel help on ISNA(), ISERR() and ISERROR() - each works slightly
different, with ISERROR() detecting any type of error.

If you're unfamiliar with the IF() function, has 3 parts:
condition
what to do if condition is true
what to do if condition is false
each of the 3 parts is separated by a comma.
 
B

_Bigred

Thanks Max, worked like a charm!

_Bigred





Max said:
Select entire sheet (with A1 active), and apply the CF as
Condition 1: =ISERROR(A1)
Format the font color white (to mask)
Click to expand...
 
Top