Incorporating Analytical Extras into Your Charts

A former student of mine wrote me an email with this chart attached. He points out that most of his charts show data plus some additional analysis that typically isn’t plotted on the chart. In his example, he not only shows sales for each quarter, but also the % growth during the same quarter. No doubt he does this to avoid follow-on questions that will force him to talk to his manager.

 

The problem he’s having is that each of the percentages you see on the chart is actually a text box that he links to a cell. The number in the text box changes, but the placement of the text box becomes problematic when the data in the chart changes. What he needs is to have the text boxes move with the plotted data points.

 

The answer to this particular problem is to incorporate the extra growth % analysis into the chart. Here’s how:

 

Step 1: Add a helper series

Add a helper series using formulas that essentially adds a few points to the actual data you’re charting. The idea here is to have the helper series be slightly greater than the actual data so that the labels for the helper series end up above the series for the actual data. After charting the helper series, you’ll see it on your chart.

 

 

Step 2: Move the helper series to the secondary axis

Moving the helper series to the secondary axis will allow you to give it its own labels. Right-click on the helper series and select ‘Format Data Series’.

In Excel 2003, the Secondary axis option is under the ‘Axis’ tab. In Excel 2007, you’ll find it under ‘Series Options’.

 

 

Step 3: Delete the Secondary Axis labels

Click on the new labels for the Secondary axis and simply hit the Delete key. You won’t want these fake numbers to show up. There are enough fake numbers in your chart. You do need to add your own.

 

 

Step 4: Point your Secondary axis to a different source for labels.

Now that your helper series is on its own axis, you can point it to a different set of category labels. To do this, you’ll have to get to the ‘Source Data’ dialog box.

In Excel 2003, you can right-click on your chart and select ‘Source Data’. In Excel 2007, right-click and select ‘Select Data’.

Once you get to the ‘Source Data’ dialog box, simply point Excel to the data you want used as the Category labels for your helper series. In this case, the category labels will be the growth percentages for each respective quarter.

 

 

Step 5: Show the Category labels for your helper series.

Now that your helper series is pointed to the correct category labels, all you have to do is to show them. Right-click on the helper series and add data labels. Once they’ve been added, format the data labels so that only the Category Name is shown. In our example, this will show the growth percentages.

 

 

Step 6: Make your helper series invisible

At this point, all you have to do is format the help series so that you can’t see anything but the labels.

 

There you have it. My extra analysis is now incorporated into the chart. Because the growth percentages are part of the chart (not just text boxes), the growth percentage labels will keep up with changes in the chart. This will ensure that my extra analysis will always stay with the data.

12 thoughts on “Incorporating Analytical Extras into Your Charts

  1. Liu 's chart blog

    As you see ,your % label are not in a line .,it not perfect.
    I would use a helper series =max(bar data)*1.2, to draw a line in the chart ,and use xy chart labelers to set its label =the % data . then hide the line .

  2. datapig

    Liu: Great tip. You could do that if you wanted the percentage growth labels to go straight across.

    But, my intention here was to have the percentage growth labels move with the columns. I think the labels look better when they stay with the datapoint.

  3. dermotb

    Isn’t it simpler just to add another series stacked on top of the first, with values = the percentages, and set to show value labels? It’s not even necessary to make the second series invisible because the % values are so small relative to the $ series.

  4. Bob Phillips

    It is a picture of George Best (in his playing days prime), IMO the greatest footballer of all time (and I include Pele and Maradonna). He was short, skinny as a rake, but he was so fast, so strong, and had immaculate balance.

    Football was a tough game in his time, kicking thugs were prevalent in most teams, but even though he looked as though the wind could have blown him over, he held his own, indeed more than held his own as he was magic.

    And even though he was short, he was a brilliant header of the ball. All-round, a great player.

    Unfortunately, he succumbed to drink, and his playing career was cut short; he had drink problems for the rest of hsi life, dying in November 2005.

    Sadly missed, but fondly remembere

  5. nixnut

    In my opinion the value labels should not be displayed in and above the bars, but in a table below the graph. That way they are more easily read and compared, don’t distort comparison of the length of the bars and avoid problems with displaying value labels for low values where the value labels don’t fit in the bar because of the low height of the bar.

  6. Colin Banfield

    “In my opinion the value labels should not be displayed in and above the bars, but in a table below the graph. That way they are more easily read and compared, don’t distort comparison of the length of the bars and avoid problems with displaying value labels for low values where the value labels don’t fit in the bar because of the low height of the bar.”

    Indeed. I think that we often forget that the point of a creating a chart is to visualize numbers. A separate chart showing the % growth would be better (as the percentages don’t correspond to the bar lengths in the current chart) .

    Mike’s technique is still quite useful if the percentages over the bars indicated the contribution of each bar to the total.

  7. AlexJ

    To add to Colin’s approach, I have been adding the % of contribution numbers to the X-axis along with the category name. Works well, especially since the range for X-axis labels can contain multiple rows.

  8. GrowTaller

    Hello,nice article. Infos are really exciting and saved me huge amount of time which I spend on something else instead of searching 🙂 Im waiting for more, bye 🙂

  9. Ananda

    Had a similar issue and this article helped a lot. My data source is from a pivot table. So I create a blank chart and selected my fields. This worked perfectly till I moved from 1 graph to 2 representing different information on the same graph lets assume sales and cost. Now when I chose the percentages for sales and that of cost only sales percentage appears on both even though they have different percentage growths. Any help would be appreciated.

Leave a Reply

Your email address will not be published. Required fields are marked *