Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Piscataway, New Jersey, USA
    Posts
    171
    Thanks
    0
    Thanked 0 Times in 0 Posts

    hack a doughnut chart (Excel 2003)

    Hi everybody:

    My boss has created a doughnut chart with 3 data series that I am updating automatically from my Access database (along with a whole lot of other stuff). Everything works fine. However, the data labels Excel generates only display inside the chart, which makes them difficult to read. I can move them outside manually by clicking and dragging, but then when the chart updates they are not always in the correct position in relationship to their data points.

    Unfortunately, the Excel GUI does not give me options for placing the data labels anywhere except its default (inside the doughnut). Anyone know how to hack this programmatically?

    Thank you, in advance, for your help.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: hack a doughnut chart (Excel 2003)

    You can set the Left and Top properties of the DataLabel object in VBA, but I think it would be a daunting task to calculate appropriate positions for each data label of each series - I suspect that's why Excel doesn't offer an "outside" option, and neither does Rob Bovey's excellent XY Data Labeler add-in.

    Why are the data labels hard to read - do your series have many data points? In that case, a doughnut chart may not be the best way to display the data.
    Or are the background colors too dark? You can change these, interactively or in VBA.

  3. #3
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Piscataway, New Jersey, USA
    Posts
    171
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: hack a doughnut chart (Excel 2003)

    Hi Hans - thanks for your quick response.

    The doughnut shape, colors, etc. were chosen by my boss, so I can't change them. The data labels are hard to read inside the doughnut for three reasons:

    1. There are 3 data series and about 30 data points each, so the chart is very busy even without data labels.
    2. The data label text (category names) is somewhat wordy and wouldn't fit inside the chart.
    3. Boss also wants the data values displayed inside each data point, so there is no room for anything else inside the chart.

    If it were up to me, I'd make it a stacked bar chart, but bosso likes fancy. He uses it to impress potential clients.

    Any ideas how to explode the data labels to the outside? Maybe create a fourth (fake, invisible) ring for them?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: hack a doughnut chart (Excel 2003)

    Whatever your boss may think, it doesn't seem a good idea to me.

    (You could duplicate the three series, add them to the chart, set them to no fill and no border, then remove the data labels for the original series, but the result isn't attractive, moreover when I tried it, the labels lost the category names after saving, closing and reopening the workbook.)

  5. #5
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Piscataway, New Jersey, USA
    Posts
    171
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: hack a doughnut chart (Excel 2003)

    Hi Hans:

    Thanks for your efforts.

    I fixed my chart by adding 2 more series and adding the category labels to the outside one, leaving enough space for the text to overlie the inner one. I set the two new series patterns and borders to "none."

    I added two columns to the data source, one for each additional series, and set the font to white so they wouldn't show on the worksheet. I linked the values in the additional columns to those in one of the other series so they would be spaced proportionately around the doughnut.

    The doughnut came out a little skinny, so I set the hole percentage to 25% and now it looks fine.

    I saved and reopened the file with no problem.

    The result is not perfect, but at least it will not mislead the viewer.

Posting Permissions

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