+ Reply to Thread
Results 1 to 8 of 8

Pie Chart - Remove Zero Value Labels

  1. #1
    Registered User
    Join Date
    10-30-2009
    Location
    UK
    MS-Off Ver
    Excel 2003, 2007, 2010
    Posts
    60

    Pie Chart - Remove Zero Value Labels

    I've a pie chart which is populated from a table which can have some zero value series - I don't want to remove these from the legend, but I do want to remove the 0% data labels.

    Is there anyway this can be done other than individually removing 0% labels each time?

    Thanks in advance

  2. #2
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: Pie Chart - Remove Zero Value Labels

    Hello Janc,

    format the labels with custom format

    0%;;;

    The format syntax is

    positive value;negative value;zero value;text

    so if you only specify a format for the positive value and leave the others blank, separated by only the semicolons, zero values will not show in the chart.

    cheers

  3. #3
    Registered User
    Join Date
    10-30-2009
    Location
    UK
    MS-Off Ver
    Excel 2003, 2007, 2010
    Posts
    60

    Re: Pie Chart - Remove Zero Value Labels

    Thanks Teylyn that works perfectly on removing the zero values

    I also have legend keys on the labels which still show even with no value - can these be removed easily?

  4. #4
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: Pie Chart - Remove Zero Value Labels

    You'll have to manually click the legend key and hit the delete button, I'm afraid, unless you want to delve into macros (which I wouldn't be able to provide).

  5. #5
    Registered User
    Join Date
    10-30-2009
    Location
    UK
    MS-Off Ver
    Excel 2003, 2007, 2010
    Posts
    60

    Re: Pie Chart - Remove Zero Value Labels

    That's fine, thank you

  6. #6
    Registered User
    Join Date
    02-11-2013
    Location
    egypt
    MS-Off Ver
    Excel 2010
    Posts
    1

    Re: Pie Chart - Remove Zero Value Labels

    Hii Janc,

    you can try for hiding the data labels "legends" a simple if equation with to be like the following if the data label you want to hide named "car" for example
    =if(cell with value=0,"",if(cell with value>0,"car"))

    hope it helps

  7. #7
    Registered User
    Join Date
    12-30-2010
    Location
    Portland, Oregon USA
    MS-Off Ver
    2007, 2010, 2016
    Posts
    35

    Re: Pie Chart - Remove Zero Value Labels

    teylyn: Thank you for your solution. It still took me a little while to figure out how to apply your fix, therefore I'm going to write it out here for the benefit of others who may struggle with this issue.

    The formulas in the source table can be written in such a way as to mask the zero or error values, but they still show up in the chart.

    Solution (Tested in Excel 2010.):
    1. Right click on one of the chart "data labels" and choose "Format Data Labels."
    2. Choose "Number" from the vertical menu on the left.
    3. In the box of "Category:" items, choose "Custom."
    4. In the "Format Code:" field, type "0%;;;" (without quotes), then click the "Add" button.
    5. Highlight the code you just added, then click the "Close" button.


    Mahmoud_1st: I'm trying to figure out how to apply your solution for hiding empty legend markers. Here's what the data field in my chart says:
    ='Data Summary'!$A$16:$A$21
    How would I apply your fix?

    Kind regards,

    Dave Myers

  8. #8
    Registered User
    Join Date
    08-09-2017
    Location
    Pune
    MS-Off Ver
    2013 64 bit
    Posts
    1

    Re: Pie Chart - Remove Zero Value Labels

    Nice Solution. Worked for me. Thanks a lot.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1