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

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
>
>
>

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
>
>
>

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
>
>

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:[email protected]...
> 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
>>
>>
>
>

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.

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" <[email protected]> wrote in message
news:e%[email protected]...
> 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.

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" <[email protected]> wrote in message
news:#[email protected]...
> 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" <[email protected]> wrote in message
> news:e%[email protected]...
> > 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.
>
>

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:%[email protected]...
> 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" <[email protected]> wrote in message
> news:#[email protected]...
>> 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" <[email protected]> wrote in message
>> news:e%[email protected]...
>> > 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.
>>
>>
>
>

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.
>