Hide Zero values bar one


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
 
Ad

Advertisements

R

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
 
N

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
 
N

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
 
D

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
 
R

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
 
Ad

Advertisements

N

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
 
R

Rob

Thanks Debra,

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

Rob
 
R

Rob

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

Rob
 
N

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
 
R

Rob

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
 
Ad

Advertisements

D

Debra Dalgleish

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

Advertisements

D

Debra Dalgleish

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top