Removing Zero Values from Labels

[Diana Child]

How can I remove Zero Values from Data Labels, (They end
up one on top of another and unreadable). I tried going
in to Optionans and removing "Show Zero Values." It works
for the data, but not the chart.

[John Walkenbach]

You can apply a custom number format that doesn't display zeros.

Select your data labels, then choose Format * Data Labels. In the Format
Data Labels dialog, click the Number tab and choose Custom from the Category
list. Then enter this custom number format in the 'Type' box:

General;-General;

This displays positive and negative values using the "General" number
format. Zeros are not displayed. To use a more specific number format,
change the two instances of General to some other codes.

John Walkenbach
For Excel tips, macros, & downloads...
http://j-walk.com/ss

"Diana Child" <mben...@hotmail.com> wrote in message
news:245d01c2446c$67e0fcf0$36ef2ecf@tkmsftngxa12...

[Diana Child]

I didn't articulate myself very well. My issue is that I
have some categories where the value is Zero in my Pie
Chart. The labels from the legend in these categories are
showing up on the chart, even though there is a zero value
in the dataseries. I would e-mail the file to show you,
but there are about 30 sheets in the Workbook, with a
different chart on each sheet. It's huge. I have to
update these twice a day... Fun, fun, fun...

>.
>

[John Walkenbach]

Probably the easiest solution is to use AutoFiltering on your data (Data *
Filter * AutoFilter). Use a custom filter: Data does not equal 0. This will
hide all rows that contain a zero value. By default, charts do not display
hidden data.

You could probably even write a macro that does the filtering for all 30
sheets with a single button click.

John Walkenbach
For Excel tips, macros, & downloads...
http://j-walk.com/ss

"Diana Child" <mben...@hotmail.com> wrote in message

news:251c01c2447b$77ce4c30$36ef2ecf@tkmsftngxa12...

[Jon Peltier]

Or you could use a little quickie to remove zero datalabels. For example,
select the pie chart and run this:

Sub RemoveZeroLabels()
Dim myPoint As Point
With ActiveChart.SeriesCollection(1)
For Each myPoint In .Points
If CSng(myPoint.DataLabel.Text) = 0 Then
myPoint.DataLabel.Delete
End If
Next
End With
End Sub

- Jon
-------
Jon Peltier, Microsoft Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html
_______

In article <uI5RD5HRCHA.2336@tkmsftngp08>, jo...@j-walk.com says...