Show zero values in specific area

[IanC]

I know it is possible to show zero values in a sheet by ticking "zero
values" in options, but I have a situation where zero values should only be
shown in certain areas of the sheet.

I've tried setting formatting to text and this displays zero values entered,
but it plays havoc with the data validation (Decimal greater than or equal
to 0).

Is there any way I can retain operation of the data validation, but still
display zero values in a specific range?

 

[Bernard Liengme]

The only way I can think of is to use the option "show zero values"; then in
the area where zeros are not to be shown forma the cell with something like
#;#,"" to hide zeros
best wishes

 

[Duke Carey]

Use custom number formatting, to which there are 4 components, each separated
by a semi-colon

[positive];[negative];[zero];text

You get there by selecting the cells you want to format and pressing Ctrl-1

To show only positive and negative, use something like

#,##0;-#,##0;

To show positive, negative, and zeros, use something like

#,##0;-#,##0;0 or #,##0;-#,##0 [notice there's no trailing semi-colon]

 

[Paul C]

Another possibility is to leave all zeros shown and use Accounting format for
all of the areas where you don't want the zero digit displayed. With this
format zero displays as -. this lets you know something is being calculated
there, but does not fill up a sheet with 0 digits.

You can also create you own custom format and use - for zero, but the
Accounting format is standard and easy.

 

[IanC]

Thanks to Bernard, Duke and Paul. I didn't realise there could be so many
options for something this simple! Thanks for your suggestions.

Paul - I've discounted your method as it shows - even if nothing has been
entered.
Duke & Bernard - I've used a variation of your methods but have a question.
In some cases the entries may be integers or 1dp so I've gone for #.#;-#.#;0
The problem with this is that when a decimal point is still shown when an
integer is entered (eg 1. instead of 1). Whilst it's not a big problem,
aesthetically I'd prefer to lose the point.

Any ideas?