Pie chart not show Category Names where there is no data

[rutica]

I am using Excel 2003.

I have a Pie chart with the 12 months of the year as my source data. Some months have data, some not yet.

Sample data:
Jan:45%
Feb: 65%
Mar: 56%
Apr:
May:
Jun:
etc.

In Chart Options, Data Label, I checked Category Name and Value.

The months with values are plotted and look fine, but the months (Category Name) with no values are showing on the pie chart on top of each other and it looks messy.

For example, Jan, 45%; Feb, 65%; and Mar, 56% are plotted on the pie chart. But the Category Names of the rest of the 9 months are shown as text boxes on top of each other at the top of the pie. Apr, May, Jun, etc are all in text boxes on top of each other in a unreadable mess.

I know I could make the source data only contain the 3 months with data, but I don't want to have to expand my source data each month.

How can I make the pie chart not show the Category Names where there is no data? Is it possible?

Thanks!

[Andy Pope]

Search forum for dynamic named ranges.
The formula can be set to automatically expand as new months data are added.

Or if you use a custom number format for the data labels. You will need to set the separator to Space rather than default comma.

,0%;;;

[rutica]

thanks for responding so quickly!

I went to Format Data Labels. In the Number tab, I chose Custom and typed: ,0%;;;
Then I went to Chart Options, Data Labels and selected Space.

But there is no change. I still see the month labels on top of each other...

Thanks,

[martindwilson]

see andy popes example here
http://www.andypope.info/charts/piezeros.htm
oh i see he's already answered lol , well he'll know best

[Andy Pope]

Try it the other way around.

Apply data labels, selecting Category and Percentage. Set separator to space.

Now select the data labels and set the custom number format.

Changing the separator resets the number format.

[rutica]

Martin: thanks for the link. I'm not able to download though (I'm at work). Also, I don't have Excel 2007..


Andy: hmm, still not working...

i have to point out that, in the table, I currently have blank values in Apr, May, Jun, etc. They are not 0%, they are blank. So I'm not getting Apr 0%; May 0%; Jun 0%, etc.

What I'm getting is: Apr, May, Jun, etc on top of each other. So it's only the Category Names that is causing me troubles, not the values.

Doesn't changing the data labels affect only the values, not the Category Names?

Thanks,

[Andy Pope]

Sorry that numberformat approach works with value and percentage, not category.

You could make the data label text yourself in another column and use the free chart labeller addin to link data labels to cells.
http://www.appspro.com/Utilities/ChartLabeler.htm


The page Martin mentioned also has a xl2003 download file.

[rutica]

thanks again for writing. I doubt I can install an add-in since it's my work computer. It seems like there is no easy way to do what I want.

I will look into Dynamic Named Ranges, but I think it will be confusing. I actually have 4 Pie Charts every year (one per quarter). The Pie Charts are set up and waiting for users to enter data. (I said I had one pie chart for 12 months just to simplify my question).

I will look at the attachments tonight, on my home computer since I can't download from work.

Thanks!

[Andy Pope]

The addin simply saves you the manual process or writing your own code.
Once the link has been made other users will only have to enter values.

This is how to do the linkage manually
http://www.andypope.info/tips/tip001.htm

[rutica]

i looked at the attachments. very impressive!

thanks for your help