Hide Zero values bar one

[Rob]

Hi,

I have set the option to not show Zero values on a worksheet, but there is
one cell that I do want to see as 0.

That cell is not a formula, just a 0. Is there a way to do this, maybe
through a custom format?

Rob

 

[Rob]

PS. It needs to be a number and not text as other formulas use that value of
0. So '0 will not do it for me.

Rob

 

[Nick Hodge]

Rob

I don't wish to sound facetious, but you've elected to hide all zeros! You
have identified the only way in converting it to text.

Could you reset to show zeros and use conditional formatting of white text
when a cell value is zero, and then leave your one without it?

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
www.nickhodge.co.uk

 

[Nick Hodge]

Rob

Testing it, can you put something like


0.00000000000001 in a cell and format as number with no decimals, that way
it would not get picked up by un-checking zero values and providing you make
sure you use ROUND functions for anything based on it, you should be ok to
use it in calculations

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
www.nickhodge.co.uk

 

[Debra Dalgleish]

Select the cell, and choose Format>Cells
On the Number tab, choose Custom
In the format box, type:
#,##0;-#,##0;"0"
Click OK

 

[Rob]

Thanks Nick, I did think to do that, but there are many formulas and
different formattings already on the sheet which would be rather time
consuming to do it that way.
I was hoping there was a way to do this for just the one cell.
What I might do, if there's no solution, is to drag that cell to another
(out of the way) part of the spreadsheet so that all the formulas still
reference that cell and then put a text 0 in that spot.

Rob

 

[Nick Hodge]

Debra

Like that, new one on me, thought that would result in text, but should have
known better as I often use

0"kg"

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
www.nickhodge.co.uk

 

[Rob]

Thanks Debra,

That did the trick!!......and experimenting with that, so does ;;"0"
(although I don't know why).

Rob

 

[Rob]

Thanks again Nick, but Debra provided a fix by formatting as
#,##0;-#,##0;"0".

Rob

 

[Nick Hodge]

Yes

It was something I should have thought of. You subsequent that said ;;"0"
worked just sets the formatting for zero and whilst that's all you need, if
you entered a positive or negative number in the cell it would have no
formatting

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
www.nickhodge.co.uk

 

[Rob]

Aha!

Thanks,

Rob

Yes

It was something I should have thought of. You subsequent that said ;;"0"
worked just sets the formatting for zero and whilst that's all you need,
if you entered a positive or negative number in the cell it would have no
formatting

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
www.nickhodge.co.uk

 

[Debra Dalgleish]

It's interesting though, that the hide zero values option acts on the
formatting, instead of the cell value.

 

[Debra Dalgleish]

You're welcome, and I see that Nick has answered the formatting question.