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".
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!
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
"Dallas MBA" wrote:
> 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!
>
--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)
"Dallas MBA" <Dallas MBA@discussions.microsoft.com> wrote in message
news:C784526F-E7DD-4378-B747-0BF2063156D6@microsoft.com...
> 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!
>
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.
--
Gary's Student
gsnu200709
"Dallas MBA" wrote:
> 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!
>
But that does hide other 0's on that worksheet, too. Maybe some that shouldn't
be hidden??
Gary''s Student wrote:
>
> 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.
> --
> Gary's Student
> gsnu200709
>
> "Dallas MBA" wrote:
>
> > 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!
> >
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.
Dallas MBA wrote:
>
> 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!
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!!
"Dallas MBA" wrote:
> 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!
>
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
On Sat, 13 Sep 2008 15:03:01 -0700, EXC_Pilot
<EXC_Pilot@discussions.microsoft.com> wrote:
>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!!
>
>"Dallas MBA" wrote:
>
>> 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!
>>
> 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
>
>
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?
>
>
In CF, use "Formula Is": =COUNT(B4,G4)<2 as your white condition.
Alternatively change your cell formula from =B4*G4 to
=IF(COUNT(B4,G4)=2,B4*G4,"")
--
David Biddulph
SUNBUM wrote:
> 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?
Question
Reply
Top
Answer
Notify me of replies