How to force individual cells to display zero when worksheet option is ...

[threephi]

Hello,

I've done a bit of googling and can't find the answer so I figured I would ask the experts

I have a worksheet with options > advanced > display options for worksheet > show a zero in cells that have zero value disabled, so my worksheet isn't filled with zeros all over the place.

However, there are a very small number of calculation cells where I would like to see a zero if that is the value, instead of having the cell stay blank.

Everything I have found online describes how to display zeros if you have them hidden through cell formatting, which I do not. I am reluctant to change my cell formatting for the entire worksheet just to un-change them for a few cells--the worksheet contains a variety of custom cell formats, is too large, and works too well as is to change cell formatting in this way.

I've done a little quick experimenting with cell formats to see if there was a way to force zeros that way, but I could not find a solution.

Anyone have any thoughts how to accomplish this without changing the worksheet display setting?

Many thanks in advance.

[jme1013]

threephi,

could you attach an example of what you're working with and show your desired results? That's much easier than us trying to recreate the wheel.

Bottom right hand corner, Go Advance >(scroll down) Manage Attachments > select the file you want to attach

[threephi]

Hi jme1013,

Thanks for the reply. The issue can very easily and quickly be investigated in a new blank workbook:

Open a new blank workbook
File menu > options > advanced > scroll down to "Display options for this worksheet" > uncheck Show a zero in cells that have a zero value (as shown in this screen cap)
excel zeros.png

put 0 in any cell

My question is: Is there a way to force individual cells to display zero values without changing the global display option?

[AliGW]

No, not to my knowledge. However, you can force zeroes not to show.

You would be better off changing the global setting back and then controlling the visibility of zeros via your formulae and cell formatting.

[loginjmor]

Hi -

One option you might try is to use Conditional Formatting. Create a new rule for the entire spreadsheet that says if a cell value equals zero, display a blank. Just click the upper left corner of the spreadsheet grid area to select all cells, then create the new rule. Where it asks for your format, just go to Custom Format, pick the numeric format that is already there that displays zeroes and "-", and edit it to "". Finally, select the cells you want to display zeroes and create a new rule for those cells. The rule would be for cells that equal zero, then just pick any numeric format you want.

Hope that helps.

[shg]

You can custom-format any cell to not show zero, e.g.

General;-General;

0.0_);(0.0);

[threephi]

Hi -

One option you might try is to use Conditional Formatting. Create a new rule for the entire spreadsheet that says if a cell value equals zero, display a blank. Just click the upper left corner of the spreadsheet grid area to select all cells, then create the new rule. Where it asks for your format, just go to Custom Format, pick the numeric format that is already there that displays zeroes and "-", and edit it to "". Finally, select the cells you want to display zeroes and create a new rule for those cells. The rule would be for cells that equal zero, then just pick any numeric format you want.

Hope that helps.

Thanks loginjmor, this seems like the best available option, although a bit kludgy.

[loginjmor]

It is a bit kludgy. Sorry.

[threephi]

No, not to my knowledge. However, you can force zeroes not to show.

You would be better off changing the global setting back and then controlling the visibility of zeros via your formulae and cell formatting.

Thanks also AliGW