Results 1 to 7 of 7

How to format empty cells to display a zero

This is a discussion on How to format empty cells to display a zero within the Excel Questions forums, part of the Question Forums category; OK, I'm having a brain block regarding cell formatting, and (believe it or not) I'm trying to avoid using VBA ...

  1. #1
    MrExcel MVP Tom Urtis's Avatar
    Join Date
    Feb 2002
    Location
    San Francisco, California USA
    Posts
    11,008

    Default How to format empty cells to display a zero

    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?

  2. #2

    Join Date
    Nov 2002
    Posts
    981

    Default

    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.

  3. #3
    MrExcel MVP
    Join Date
    May 2002
    Posts
    13,993

    Default

    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

  4. #4
    MrExcel MVP Tom Urtis's Avatar
    Join Date
    Feb 2002
    Location
    San Francisco, California USA
    Posts
    11,008

    Default

    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.

  5. #5

    Join Date
    Nov 2002
    Posts
    981

    Default

    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.

  6. #6
    MrExcel MVP Tom Urtis's Avatar
    Join Date
    Feb 2002
    Location
    San Francisco, California USA
    Posts
    11,008

    Default

    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.

  7. #7
    New Member
    Join Date
    Sep 2010
    Posts
    7

    Default Re: How to format empty cells to display a zero

    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?

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com