Hide Zero values bar one

  • Thread starter Thread starter Rob
  • Start date Start date
R

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
 
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
 
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
 
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
 
Select the cell, and choose Format>Cells
On the Number tab, choose Custom
In the format box, type:
#,##0;-#,##0;"0"
Click OK
 
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
 
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
 
Thanks Debra,

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

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
 
Aha!

Thanks,

Rob

Nick Hodge said:
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
Click to expand...
 
It's interesting though, that the hide zero values option acts on the
formatting, instead of the cell value.
 
Back
Top