|
Hide Zero values bar one
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 |
Hide Zero values bar one
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" wrote in message ... 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 |
Hide Zero values bar one
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 DTHIS www.nickhodge.co.uk "Rob" wrote in message ... 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" wrote in message ... 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 |
Hide Zero values bar one
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 DTHIS www.nickhodge.co.uk "Nick Hodge" wrote in message ... 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 DTHIS www.nickhodge.co.uk "Rob" wrote in message ... 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" wrote in message ... 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 |
Hide Zero values bar one
Select the cell, and choose FormatCells
On the Number tab, choose Custom In the format box, type: #,##0;-#,##0;"0" Click OK Rob wrote: 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" wrote in message ... 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 -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
Hide Zero values bar one
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" wrote in message ... 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 DTHIS www.nickhodge.co.uk "Rob" wrote in message ... 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" wrote in message ... 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 |
Hide Zero values bar one
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 DTHIS www.nickhodge.co.uk "Debra Dalgleish" wrote in message ... Select the cell, and choose FormatCells On the Number tab, choose Custom In the format box, type: #,##0;-#,##0;"0" Click OK Rob wrote: 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" wrote in message ... 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 -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
Hide Zero values bar one
Thanks Debra,
That did the trick!!......and experimenting with that, so does ;;"0" (although I don't know why). Rob "Debra Dalgleish" wrote in message ... Select the cell, and choose FormatCells On the Number tab, choose Custom In the format box, type: #,##0;-#,##0;"0" Click OK Rob wrote: 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" wrote in message ... 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 -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
Hide Zero values bar one
Thanks again Nick, but Debra provided a fix by formatting as
#,##0;-#,##0;"0". Rob "Nick Hodge" wrote in message ... 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 DTHIS www.nickhodge.co.uk "Nick Hodge" wrote in message ... 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 DTHIS www.nickhodge.co.uk "Rob" wrote in message ... 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" wrote in message ... 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 |
Hide Zero values bar one
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 DTHIS www.nickhodge.co.uk "Rob" wrote in message ... Thanks again Nick, but Debra provided a fix by formatting as #,##0;-#,##0;"0". Rob "Nick Hodge" wrote in message ... 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 DTHIS www.nickhodge.co.uk "Nick Hodge" wrote in message ... 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 DTHIS www.nickhodge.co.uk "Rob" wrote in message ... 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" wrote in message ... 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 |
Hide Zero values bar one
Aha!
Thanks, Rob "Nick Hodge" wrote in message ... 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 DTHIS www.nickhodge.co.uk "Rob" wrote in message ... Thanks again Nick, but Debra provided a fix by formatting as #,##0;-#,##0;"0". Rob "Nick Hodge" wrote in message ... 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 DTHIS www.nickhodge.co.uk "Nick Hodge" wrote in message ... 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 DTHIS www.nickhodge.co.uk "Rob" wrote in message ... 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" wrote in message ... 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 |
Hide Zero values bar one
It's interesting though, that the hide zero values option acts on the
formatting, instead of the cell value. Nick Hodge wrote: Debra Like that, new one on me, thought that would result in text, but should have known better as I often use 0"kg" -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
Hide Zero values bar one
You're welcome, and I see that Nick has answered the formatting question.
Rob wrote: Thanks Debra, That did the trick!!......and experimenting with that, so does ;;"0" (although I don't know why). Rob "Debra Dalgleish" wrote in message ... Select the cell, and choose FormatCells On the Number tab, choose Custom In the format box, type: #,##0;-#,##0;"0" Click OK Rob wrote: 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" wrote in message ... 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 -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
| All times are GMT +1. The time now is 02:00 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com