Hiding Formulas when the value is "0"?

[Guest]

Does anyone know if Excel allows you to hide a formula when the result of the
formula equals "0"? I have a huge spreadsheet that makes it difficult to
briefly scan the report and locate number values greater than "0" because
there are so many formulas that equal "0".

Existing View: 0 0 0 4 0 0 0 0 1 0 0 0 7 0 0 0 0 0

Preferred View: 4 1 7

I don't want to see the zeros as in the "Existing View" above and want the
spreadsheet to appear like "Preferred View". I want to keep the formulas in
the cells that equal "0", therefore I don't want to delete them to make the
cells look blank. Any feedback is appreciated!

 

[Guest]

You could enclose your formula within an IF statement, like this:

=IF(formula=0,"",formula)

So, if the formula results in a 0, a blank will be returned, otherwise the
results of the formula will be returned.

HTH,
Elkar

 

[Chip Pearson]

Try something like

=IF(your_formula=0,"",your_formula)


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)

 

[Guest]

Since you want to keep your formuls as is but just not display the zeros, try:


Tools > Options > View and clear the zero values checkbox.

 

[Dave Peterson]

You could also hide the 0's in that range:

select that range
format|Cells|number tab|Custom category
General;-General;;@
(positive number;negative numbers;0's;text)

But if you select a cell with 0 and look at the formula bar, you'll still see 0.

 

[Dave Peterson]

But that does hide other 0's on that worksheet, too. Maybe some that shouldn't
be hidden??

 

[EXC_Pilot]

I just need 3 cells to sum and if they are not filled then the formula cell
need to be blank. Some how I'm messing up. in cell I8 I want =SUM(F8:H8) but
if the cells a empty I dont want the 0 the formula creates. HELP!!

 

[Gord Dibben]

For your question of empty cells..............

=IF(COUNT(F8:H8)=0,"",SUM(F8:H8))

What would you want to see if all or some are filled but add up to 0?

Maybe........................

=IF(SUM(F8:H8)=0,"",SUM(F8:H8))


Gord Dibben MS Excel MVP

 

[Mykeupismt]

Have you tried Conditional Formatting for the selected cells?

Format
Conditional Formatting

Condition 1 - Cell Value is equal to 0
then select Format/Font/Color/White

 

[Mykeupismt]

Have you tried Conditional Formatting for the selected cells?

Format
Conditional Formatting

Condition 1 - Cell Value is equal to 0
then select Format/Font/Color/White

 

[SUNBUM]

Have you tried Conditional Formatting for the selected cells?

Format
Conditional Formatting

Condition 1 - Cell Value is equal to 0
then select Format/Font/Color/White

 

[SUNBUM]

I would like to have zeros displayed if the actual value is zero, but NOT if
values have not been put into that particular row yet. for example i have a
cell that i want to equal B4 x G4. i then copy and pasted it for the entire
column. i do not have values in for B17 or G17 yet, but it displayes a zero
for this sum already, so my whole page is filled with zeros. Anything I can
do about this?

 

[Jamal]

Dears; I have the same problem with lenghty formulas as
AVERAGEIF($C$5:$C$155,"mar",P6156) and it gives me#DIV/0! error until the
cell value is zero.
I would appreciate if you kindly assist.
best regards
Jamal