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.