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

Here's one way. Assuming your items are in col. A and
values in col. B, starting in A1, place this in B1, press
ctrl + shift + enter, and fill down as far as needed:

=INDEX(OFFSET($B$1,,,COUNT(B:B)),SMALL(IF(OFFSET
($B$1,,,COUNT(B:B))<0,ROW(OFFSET($B$1,,,COUNT(B:B )))),ROW
()))

Change your source range col. B, but use a dynamic range.
Define a name and use the formula:

=OFFSET(C1,,,COUNT(C:C))

To learn more about using dynamic ranges in charts, see:

http://www.peltiertech.com/Excel/Cha...micColumnChart
1.html

If your data does not start in row 1, let me know and
I'll adjust the formula.

HTH
Jason
Atlanta, GA

-----Original Message-----
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
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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
...
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" wrote in message
...
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" wrote in message
...
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.