Replacing zero with an empty cell!?
Solved
Tony
-
OGNono -
OGNono -
Hello,
I made a table with simple sums.
I would like to ensure (in Excel 2007) that a cell returning a zero becomes empty!
Instead of actually showing zero...
It's just a tiny issue that is only for aesthetic purposes!
Thank you in advance
I made a table with simple sums.
I would like to ensure (in Excel 2007) that a cell returning a zero becomes empty!
Instead of actually showing zero...
It's just a tiny issue that is only for aesthetic purposes!
Thank you in advance
Configuration: Windows Vista Firefox 3.0.3
7 réponses
You need to click on the Office Button, in the top left corner, then select Excel Options (button at the bottom), then go to Advanced Options and scroll down to find "Show options for this worksheet," and finally uncheck the option "Show a zero in cells that have a null value."
There is another method, valid for all versions of Excel:
Select the sheet (or the area, or the column, or the row ...) and format it as Cell/Number/Custom/ and enter # ##0.00;-# ##0.00; without adding anything after the semicolon.
This format tells Excel to display positive numbers like this: 1 234.56 and negative numbers like this: -1 234.56 without displaying anything for the zero value.
--
Retirement is nice! Especially in the West Indies... :-)
☻ Raymond ♂
There is another method, valid for all versions of Excel:
Select the sheet (or the area, or the column, or the row ...) and format it as Cell/Number/Custom/ and enter # ##0.00;-# ##0.00; without adding anything after the semicolon.
This format tells Excel to display positive numbers like this: 1 234.56 and negative numbers like this: -1 234.56 without displaying anything for the zero value.
--
Retirement is nice! Especially in the West Indies... :-)
☻ Raymond ♂
I don't know where you got that from but thank you :)
I strongly recommend using the "Number" format in the "Custom" category so that your numerical data is displayed like a sentence.
By writing in the small "Type" window the expression
"Today is "jjjj jj mmmm aaaa" and the weather is nice"
and, after validating, entering the data 18/8 in the formatted cell (B3 for example), you will see
"Today is Thursday, August 18, 2011, and the weather is nice"
and if in cell B4 you enter the formula =B3+6, you will see
"Today is Wednesday, August 22, 2011, and the weather is nice"
which proves to you that they are indeed numerical data and not text...
Best regards.
thank you raymond