View Single Post
  #5   Report Post  
Posted to microsoft.public.excel.charting
[email protected] ashley73@gmail.com is offline
external usenet poster
 
Posts: 3
Default Named-range source-data for pie charts on copied worksheets

I may have figured out a somewhat inelegant yet arguably effective
solution. The piezeros workaround is brilliant, albeit way above my
level of comprehension. Unfortunately it appears that it doesn't work
for copied sheets. One alternative workaround, which so far looks
satisfactory, is to do away with the piezeros complexity, and use the
original table data as source data. Values and percentages can be
hidden using custom formats, leaving just the labels. Assuming the
zero value records are at the bottom of the source table (my data will
always be in order of decreasing value), they get piled up at the top
of the chart. They can be hidden by the strategic placing of a small
rectangle, no border, white fill, placed at 12 o'clock. As I said,
inelegant and basic, yet it looks like it does the job.

The only problem could be that it may partially obscure the last non-
zero label, but it seems ok unless the value is ~1% (unlikely in my
situation). When there is a small value, say 2%, the zero labels get
pushed to the side a tad. Another small rectangle can be strategically
placed to cover that area also. Obviously this solution has no effect
on the legend (whereas piezeros does), so you would have to choose
between showing all labels in the legend or not showing the legend at
all.

Granted that my solution is based on a number of assumptions, but it
works for me, and may be helpful to someone else.