Results 1 to 4 of 4
  1. #1
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Calgary, Alberta, Canada
    Posts
    818
    Thanks
    6
    Thanked 1 Time in 1 Post

    Question Conditional Format so that zeros do not show?

    I have a small spreadsheet that I want to format so that values that are zero do not show, but are, instead, replaced by a blank cell. This means that only the numbers greater than zero show. There are no, nor will there ever be, numbers less than zero, so this situation is not a problem. Any insight that anywone would care to provide will be greatly appreciated. Thanks.

    Ron M

    P.S. I am using Excel 2010

  2. #2
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    From the File tab, select Word Options, then Advanced, then about 3/4 of the way down that window, uncheck show a zero.
    See below.

    Clip.jpg

  3. #3
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Ron, you may want to apply this to the whole workbook, so click the dropdown as currently showing Sheet1 in the previous image from kweaver.

    For zeros, sometimes, I want to have both: I want some cells to NOT show a zero, but others (like sum totals) I do want to see a zero. To do this you need to apply a Custom format to the cells where you want to hide the zeros.

    A number format can have up to four sections, separated by semicolons. These sections define the format for positive numbers, negative numbers, zero values, and text, in that order.
    <POSITIVE>;<NEGATIVE>;<ZERO>;<TEXT>

    In Excel 2010, to create a Custom number format select the Home ribbon tab; in the middle of the Home ribbon you’ll find the Number section. Click the Dialog Box Launcher (in bottom-right) next to Number. In the left-hand Category panel, click Custom. In the right-hand Type panel, you will see a scrollable list of existing custom number formats. A few of these only have definitions for <POSITIVE>;<NEGATIVE>

    You can edit an existing format rather than specifying each section.

    For example, select #,##0.00;[Red]-#,##0.00 and change to
    #,##0.00;[Red]-#,##0.00;
    ..i.e just add a semi-colon to the end. This will hide zero values. Copy this format to other cells as required etc.

    zeddy

  4. #4
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Calgary, Alberta, Canada
    Posts
    818
    Thanks
    6
    Thanked 1 Time in 1 Post
    kweaver, thanks for the insight, but the approach you have outlined works for every spreadsheet in my workbook, whereas I only want it to work for one of them - sorry if I forgot to mention that. zeddy, your approach works the way I wanted it to work, so I thanks you for that.

    Regards,

    Ron M

Tags for this Thread

Posting Permissions

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