Placing Chart Data Labels

I made this chart in Excel 2003:

I used this data:

Vehicle 1/31/2012 2/29/2012 3/31/2012 4/30/2012 5/31/2012 6/30/2012 7/31/2012 8/31/2012 9/30/2012
Cars 603 615 627 634 646 659 672 679 693
Trucks 405 413 433 442 451 440 430 445 475
Vans 545 556 567 578 590 602 590 585 580
SUVs 465 512 527 570 604 634 672 726 750
Hybrids 510 519 571 628 672 725 775 805 855

Normally I would put the series name at the end of the line (because I like how it looks), but I wanted to include the starting and ending values too. So I decided to put the series name on the second to last data point. If all the labels are at the top or bottom, it looks terrible because they run into other lines. I needed to put the label above or below the line based on how much space was available. That was harder than I thought it would be.

I ended up looping through the collection a lot: once to store the values at position 8, another couple times to sort, and yet another time to apply the labels. I started with some of Peltier’s code and modified for my needs.

The lines diverge toward the end, so using the 8th data point turned out good. If they converged, it wouldn’t really matter whether there was more space on the top or bottom, they would still run into each other. In that case I would change lPNTOFFSET to a more appropriate place.

9 thoughts on “Placing Chart Data Labels

  1. Hey, that’s not too bad

    I think I prefer it the other way, visually, but at about 1/3 of the code and no chance of bumping into each other, the trade-off is probably worth it.

  2. What if the last values of multiple series are the same or too close to each other?
    I like a design often used in WSJ charts where series labels (or labels+values) in the legend are ordered by last values rankage and colored according to the series colors.
    Charley Kyd suggested one of the ways to do it in Excel with custom legend and without VBA.

  3. Dick – why not have an option for the name and value placement ?
    Leonid makes a great point: you can’t assume the last data points are going to show a nice separation.

  4. Hey guys, not very experienced with VBA here. I am getting a Run-Time error ‘424’ on the following line

    Set cht = wshInventory.ChartObjects(1).Chart

    I am using excel 2003.

  5. I replaced that line with

    Set cht = ActiveSheet.ChartObjects(1).Chart

    and it worked, thanks again for this post. very sleek

  6. One last question, is there a property on the .datalabel where I can force the data label to be on one line (as opposed to wrapping into multiple lines).

  7. @macutan,

    Prior to xl2013 data labels have no Width or Height property you can alter, either manually or with code.
    So no you can not force single line text.

    The only alternative is to use textbox shapes.

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.