Results 1 to 6 of 6
  1. #1
    4 Star Lounger pccoyle's Avatar
    Join Date
    Apr 2001
    Location
    Auckland, Auckland, New Zealand
    Posts
    535
    Thanks
    3
    Thanked 2 Times in 2 Posts

    Pie Graph Dispaly Labels (all)

    We have the names of furniture suites in 40 columns across the page. Underneath this, from another sheet in the same workbook, is a number relating to the number of repairs for that named item for the month.
    Under this row is a percentage figure to the total, for that item.
    When plotting a pie graph, if I use the entire range, then all the labels for those items with 0% overwrite some of the labels for plotted values.
    I have used advanced filter, but then have to hide columns. This does work, but is tedious.
    Is there a better way to only plot from the source data those cells with data?
    Cheers
    <img src=/S/bingo.gif border=0 alt=bingo width=15 height=22>
    Paul Coyle
    Approach love and cooking with reckless abandon

  2. #2
    Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia
    Posts
    85
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pie Graph Dispaly Labels (all)

    I'm not sure you can tell the pie graph to hide labels for blank/zero amounts.

    However, could you change your worksheet so that the data runs vertically rather than horizontally, ie one row per furniture suite rather than column? Then you could use filtering to hide the zero rows. Apologies if I'm stating the bleeding obvious.

    Jon

  3. #3
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pie Graph Dispaly Labels (all)

    Paul,

    Yoiu might like to use VBA to hide columns. That would give the flexibiliy also to hide rows with very small, but not quite zero, values.

    I've had some limited success in shifting labels which overlap- and a little more in not showing zero values.

    I can try to post some code tomorrow when I'm at work (Australian time) but I'm not sure.
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  4. #4
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Lawrence, Kansas, USA
    Posts
    202
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pie Graph Dispaly Labels (all)

    This probably isn't quite what you want, but if you use a pivot table, you can uncheck whatever cells you don't want to report. It's not automated, but you can also use the pivot table to make the chart.

    Another advantage of the pivot table, you don't have to calculate the percentages separately. You can report the data as sums, averages, percentages, whatever you want.

  5. #5
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pie Graph Dispaly Labels (all)

    This is also probably not what you want but hopefully someone will perfect it. I started on the surmise that #n/a might work for titles as well as data points but it doesn't. That's the chart on the left. The chart on the right was an afterthought but on reflection I prefer it - try changing the data in b7:i7 and see whether either come close to what you need.

    HTH

    Brooke
    Attached Files Attached Files

  6. #6
    4 Star Lounger pccoyle's Avatar
    Join Date
    Apr 2001
    Location
    Auckland, Auckland, New Zealand
    Posts
    535
    Thanks
    3
    Thanked 2 Times in 2 Posts

    Re: Pie Graph Dispaly Labels (all)

    Thank you Jon, Geoff, Cathy and Brooke. Changing the layout of the sheet to vertical, and then applying advanced filter appears to be the best non VBA option. Best of all, it is understood by the user
    Thanks again.
    Paul Coyle
    Approach love and cooking with reckless abandon

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •