How to format empty cells to display a zero

Tom Urtis

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,300
OK, I'm having a brain block regarding cell formatting, and (believe it or not) I'm trying to avoid using VBA if possible to accomplish the kind of formatting I need. I want to have empty cells display a zero, and I'm probably forgetting something simple about how to do it.

Background:

I have a range of empty cells in A1:M20. "Empty cells" means no formulas, values, or formatting of any kind...cells that are in untouched condition just as when you start a new workbook.

I am creating a budget workbook model, where cells in A1:M20 might have whole numbers manually entered into them. No text or decimal places are involved.

Not every cell will end up having a number manually entered into it. When it does, the range shall be formatted for comma separators, such as you'd get with the #,##0;-#,##0 custom format. No problem so far.

The question I have is, when no number otherwise exists in a cell (either because the cell has never been touched, or its previous number was deleted), what would be the custom format to display a zero ("0") in that cell? The client wants to see every cell contain either whole numbers (formatted for comma separators), or a zero.

I could put code in the worksheet module to display a zero in cells with a zero-length string, but is there a native custom format to achieve this?
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Cell formatting displays the contents of the cell in the desrired format - I don't think you can use cell formatting to display something when there is nothing there.
 
Upvote 0
Hi Tom,

is there a native custom format to achieve this


Not that I can think of (which may or may not mean anything). As formats apply to data values, I guess you're going to have problems coercing native formatting functions to recognise the absence of data as a data value!

Any reason for the non-VA preference?

paddy
 
Upvote 0
Thanks guys; you are probably correct...I know there are native custom formats to put a dash in a cell with nothing in it, but I thought maybe I'm missing something obvious, with a zero instead, which doesn't work when trying to modify _(* #,##0_);_(* (#,##0);_(* "-"_);_(@_).

To answer your question Paddy - - I'm trying to explore VBA alternatives just for my own knowledge, because I wonder if sometimes I use VBA too quickly for solutions. Maybe this is a case where VBA really is necessary. No big deal to me if so, but it seems there are native non-VBA solutions for lots of problems, so I thought I'd take a shot and see if this can be done without code.
 
Upvote 0
Tom

The format you posted _(* #,##0_);_(* (#,##0);_(* "-"_);_(@_) will not display a dash in a cell that is empty.

It custom formats zero values (the third part of the format) so that a dash will be displayed when the cell's value is zero.
 
Upvote 0
Yes I know, that was just an attempt to convey the comparison example that if a cell has no formula, what format could display a zero, sort of like how _(* #,##0_);_(* (#,##0);_(* "-"_);_(@_) displays a dash with a zero-returned formula.
 
Upvote 0
Hi,
I was wondering how to get a dash displayed when the cell is empty using a format similar to the one in this thread?

That is, using cell custom format,
if cell value is 0 I want -
if cell value is empty I want -

How can I do that please?
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,025
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top