+ Reply to Thread
Results 1 to 10 of 10

Zero Values in a Pie Chart

  1. #1
    DMc2005
    Guest

    Zero Values in a Pie Chart

    Hi

    I currently have some data as shown below:

    Item 1 0
    Item 2 3
    Item 3 6
    Item 4 5
    Item 5 0
    Item 6 9

    How can I exclude the zero items automatically, the data shown below is
    going to change regulary and i do not want to have to keep change the source
    data manually.

    D



  2. #2
    Toppers
    Guest

    RE: Zero Values in a Pie Chart

    You can use AutoFilter to hide the rows with 0s as Chart does not display
    data that's hidden.

    HTH

    "DMc2005" wrote:

    > Hi
    >
    > I currently have some data as shown below:
    >
    > Item 1 0
    > Item 2 3
    > Item 3 6
    > Item 4 5
    > Item 5 0
    > Item 6 9
    >
    > How can I exclude the zero items automatically, the data shown below is
    > going to change regulary and i do not want to have to keep change the source
    > data manually.
    >
    > D
    >
    >
    >


  3. #3
    John Mansfield
    Guest

    RE: Zero Values in a Pie Chart

    Andy Pope shows how to do this via te use of dynamic range names here . . .

    http://www.andypope.info/charts/piezeros.htm

    ----
    Regards,
    John Mansfield
    http://www.pdbook.com


    "DMc2005" wrote:

    > Hi
    >
    > I currently have some data as shown below:
    >
    > Item 1 0
    > Item 2 3
    > Item 3 6
    > Item 4 5
    > Item 5 0
    > Item 6 9
    >
    > How can I exclude the zero items automatically, the data shown below is
    > going to change regulary and i do not want to have to keep change the source
    > data manually.
    >
    > D
    >
    >
    >


  4. #4
    Peter T
    Guest

    Re: Zero Values in a Pie Chart

    In a pie chart no slice is shown for a zero value, so unlike other chart
    types you don't really need to cater for it. Or, do you mean not display
    Legend Entries with zero values. That could be done with a macro, though it
    would need to be rerun each time values change.

    Regards,
    Peter T


    > I currently have some data as shown below:
    >
    > Item 1 0
    > Item 2 3
    > Item 3 6
    > Item 4 5
    > Item 5 0
    > Item 6 9
    >
    > How can I exclude the zero items automatically, the data shown below is
    > going to change regulary and i do not want to have to keep change the

    source
    > data manually.
    >
    > D
    >
    >




  5. #5
    Earl Kiosterud
    Guest

    Re: Zero Values in a Pie Chart

    To add to Peter's response, you can make a separate column for the pie
    labels (presuming you're using labels, not the legend, for the slices) that
    yields an empty string for those values that are zero. This way, you get no
    labels for zero-value items:

    Label Chart label Value
    Tom =IF(C2<>0, A2, "") 1
    Sally =IF(C3<>0, A3, "") 0
    etc.

    Use the Chart Label column for the chart. You can hide that column.
    --
    Earl Kiosterud
    mvpearl omitthisword at verizon period net
    -------------------------------------------

    "Peter T" <peter_t@discussions> wrote in message
    news:OWuiC0FNFHA.3340@TK2MSFTNGP14.phx.gbl...
    > In a pie chart no slice is shown for a zero value, so unlike other chart
    > types you don't really need to cater for it. Or, do you mean not display
    > Legend Entries with zero values. That could be done with a macro, though
    > it
    > would need to be rerun each time values change.
    >
    > Regards,
    > Peter T
    >
    >
    >> I currently have some data as shown below:
    >>
    >> Item 1 0
    >> Item 2 3
    >> Item 3 6
    >> Item 4 5
    >> Item 5 0
    >> Item 6 9
    >>
    >> How can I exclude the zero items automatically, the data shown below is
    >> going to change regulary and i do not want to have to keep change the

    > source
    >> data manually.
    >>
    >> D
    >>
    >>

    >
    >




  6. #6
    Jon Peltier
    Guest

    Re: Zero Values in a Pie Chart

    Alternatively, you can use a custom number format that excludes zero
    displays, like one of these:

    0;;;
    0%;;;

    - Jon
    -------
    Jon Peltier, Microsoft Excel MVP
    Peltier Technical Services
    Tutorials and Custom Solutions
    http://PeltierTech.com/
    _______

    Earl Kiosterud wrote:

    > To add to Peter's response, you can make a separate column for the pie
    > labels (presuming you're using labels, not the legend, for the slices) that
    > yields an empty string for those values that are zero. This way, you get no
    > labels for zero-value items:
    >
    > Label Chart label Value
    > Tom =IF(C2<>0, A2, "") 1
    > Sally =IF(C3<>0, A3, "") 0
    > etc.
    >
    > Use the Chart Label column for the chart. You can hide that column.


  7. #7
    Earl Kiosterud
    Guest

    Re: Zero Values in a Pie Chart

    Jon,

    I don't think I follow. The idea is to make the label disappear when the
    value is zero. The label and the value are in separate cells.

    --
    Earl Kiosterud
    mvpearl omitthisword at verizon period net
    -------------------------------------------

    "Jon Peltier" <jonREMOVExlmvp@peltierCAPStech.com> wrote in message
    news:e%23cWQbaNFHA.1172@TK2MSFTNGP12.phx.gbl...
    > Alternatively, you can use a custom number format that excludes zero
    > displays, like one of these:
    >
    > 0;;;
    > 0%;;;
    >
    > - Jon
    > -------
    > Jon Peltier, Microsoft Excel MVP
    > Peltier Technical Services
    > Tutorials and Custom Solutions
    > http://PeltierTech.com/
    > _______
    >
    > Earl Kiosterud wrote:
    >
    >> To add to Peter's response, you can make a separate column for the pie
    >> labels (presuming you're using labels, not the legend, for the slices)
    >> that yields an empty string for those values that are zero. This way,
    >> you get no labels for zero-value items:
    >>
    >> Label Chart label Value
    >> Tom =IF(C2<>0, A2, "") 1
    >> Sally =IF(C3<>0, A3, "") 0
    >> etc.
    >>
    >> Use the Chart Label column for the chart. You can hide that column.




  8. #8
    Peter T
    Guest

    Re: Zero Values in a Pie Chart

    Earl,

    Jon's neat idea will display no value in the label, and the label
    "apparently" disappear providing labels are formatted with Fill and Border
    "None".

    In Format labels..., try custom number format like this:

    General;General;

    "General" can be changed as required but nothing after the second ";", the
    zero value format.

    This also assume that Labels are set to "show value" rather than "show
    label" in Chart Options > Data Labels.

    We never did learn what the OP didn't want to show, so we don't know if his
    Labels show values or labels, if indeed his problem concerns labels !

    Regards,
    Peter T

    PS your idea is very clever !

    "Earl Kiosterud" <nothanks@nospam.com> wrote in message
    news:#EieWlgNFHA.3356@TK2MSFTNGP12.phx.gbl...
    > Jon,
    >
    > I don't think I follow. The idea is to make the label disappear when the
    > value is zero. The label and the value are in separate cells.
    >
    > --
    > Earl Kiosterud
    > mvpearl omitthisword at verizon period net
    > -------------------------------------------
    >
    > "Jon Peltier" <jonREMOVExlmvp@peltierCAPStech.com> wrote in message
    > news:e%23cWQbaNFHA.1172@TK2MSFTNGP12.phx.gbl...
    > > Alternatively, you can use a custom number format that excludes zero
    > > displays, like one of these:
    > >
    > > 0;;;
    > > 0%;;;
    > >
    > > - Jon
    > > -------
    > > Jon Peltier, Microsoft Excel MVP
    > > Peltier Technical Services
    > > Tutorials and Custom Solutions
    > > http://PeltierTech.com/
    > > _______
    > >
    > > Earl Kiosterud wrote:
    > >
    > >> To add to Peter's response, you can make a separate column for the pie
    > >> labels (presuming you're using labels, not the legend, for the slices)
    > >> that yields an empty string for those values that are zero. This way,
    > >> you get no labels for zero-value items:
    > >>
    > >> Label Chart label Value
    > >> Tom =IF(C2<>0, A2, "") 1
    > >> Sally =IF(C3<>0, A3, "") 0
    > >> etc.
    > >>
    > >> Use the Chart Label column for the chart. You can hide that column.

    >
    >




  9. #9
    Earl Kiosterud
    Guest

    Re: Zero Values in a Pie Chart

    Peter,

    Yeah. I presumed the OP wanted category names for labels, not values. I
    think Jon's solution was for showing values.

    --
    Earl Kiosterud
    mvpearl omitthisword at verizon period net
    -------------------------------------------

    "Peter T" <peter_t@discussions> wrote in message
    news:%23Td7X7gNFHA.2604@TK2MSFTNGP10.phx.gbl...
    > Earl,
    >
    > Jon's neat idea will display no value in the label, and the label
    > "apparently" disappear providing labels are formatted with Fill and Border
    > "None".
    >
    > In Format labels..., try custom number format like this:
    >
    > General;General;
    >
    > "General" can be changed as required but nothing after the second ";", the
    > zero value format.
    >
    > This also assume that Labels are set to "show value" rather than "show
    > label" in Chart Options > Data Labels.
    >
    > We never did learn what the OP didn't want to show, so we don't know if
    > his
    > Labels show values or labels, if indeed his problem concerns labels !
    >
    > Regards,
    > Peter T
    >
    > PS your idea is very clever !
    >
    > "Earl Kiosterud" <nothanks@nospam.com> wrote in message
    > news:#EieWlgNFHA.3356@TK2MSFTNGP12.phx.gbl...
    >> Jon,
    >>
    >> I don't think I follow. The idea is to make the label disappear when the
    >> value is zero. The label and the value are in separate cells.
    >>
    >> --
    >> Earl Kiosterud
    >> mvpearl omitthisword at verizon period net
    >> -------------------------------------------
    >>
    >> "Jon Peltier" <jonREMOVExlmvp@peltierCAPStech.com> wrote in message
    >> news:e%23cWQbaNFHA.1172@TK2MSFTNGP12.phx.gbl...
    >> > Alternatively, you can use a custom number format that excludes zero
    >> > displays, like one of these:
    >> >
    >> > 0;;;
    >> > 0%;;;
    >> >
    >> > - Jon
    >> > -------
    >> > Jon Peltier, Microsoft Excel MVP
    >> > Peltier Technical Services
    >> > Tutorials and Custom Solutions
    >> > http://PeltierTech.com/
    >> > _______
    >> >
    >> > Earl Kiosterud wrote:
    >> >
    >> >> To add to Peter's response, you can make a separate column for the pie
    >> >> labels (presuming you're using labels, not the legend, for the slices)
    >> >> that yields an empty string for those values that are zero. This way,
    >> >> you get no labels for zero-value items:
    >> >>
    >> >> Label Chart label Value
    >> >> Tom =IF(C2<>0, A2, "") 1
    >> >> Sally =IF(C3<>0, A3, "") 0
    >> >> etc.
    >> >>
    >> >> Use the Chart Label column for the chart. You can hide that column.

    >>
    >>

    >
    >




  10. #10
    Jon Peltier
    Guest

    Re: Zero Values in a Pie Chart

    Earl -

    That's right, I was thinking of values, and I replied to your post without noticing
    that the formulas pointed to the labels.

    - Jon

    Earl Kiosterud wrote:

    > Peter,
    >
    > Yeah. I presumed the OP wanted category names for labels, not values. I
    > think Jon's solution was for showing values.
    >



+ 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