In Applying Offset Labels to Chart we looked at how a chart’s labels can be offset to avoid cluttering. But what if you wanted to use the chart to narrate a story – and a pretty long one at that? How would you do it? If you turn on the chart axis labels, the entire thing gets messed up. Roots growing out of the chart axis wasn’t exactly the point that we would like to highlight to someone who’s going to be using this chart, is it?
Using the chart’s series labels instead of of the axis ones isn’t too good either.
One of the ways to get a chart to show descriptions for large number of events is to make the chart labels interactive. By placing a forms toolbar in the chart, linking it up with a cell and with a little help from two helper series, we can create a message box that floats on top of the chart. So now as the user clicks on the scrollbar, the message box updates and shows new messages.
Adding Interactive Labels to the Chart
Let’s start with the chart’s data. Essentially the first two series consist of the descriptions and the data points.
Once we have created a basic chart, we add a scrollbar to it. In order to add a scrollbar, click and select the entire chart. Then select a scroll bar from the Forms Toolbar and draw one inside the chart. Edit the scrollbar and link it up to a cell in the chart’s data. When the user uses the scrollbar, the cell gets updated with the corresponding value. In our case, we use cell B1 for this purpose.
Adding the Helper Series
The third and the last columns in the chart’s data are the helper series which we use to create two X-Y data series. Both the series are going to be linked to the cell which we attached to the forms scrollbar in step 1 above. The idea here is to have both helper series show up only one point each at a time – which corresponds to the scrollbar value at that point. The avoid the other points from showing up.
For the first series, we use:
=IF(scrollbar_value= chart_data_point_index ,chart_data_point,#N/A)
Once we have the formulas in place to generate the series, we add them to the chart. They appear as individual points in the chart.
Adding the error bar as the connector
Once we have both the data series added to the chart, click on the second helper series point. Edit the Y-axis error bar to it and provide the fourth column as the +ve error value.
Add the floating message box by turning on the X-axis chart label. In order to ensure that the message box floats at a safe distance away from the series we will need to fix the scale maximum to a static value (right click y-axis and choose “Scale” tab in Excel 2003 and earlier. In Excel 2007, right click on Y-axis and select “Format Axis”, select “Axis Options” and adjust maximum). Too low a value for the maximum and the message box gets cut off and too high a value gets the message box interfering with the data series. (Adjust the formula in the 2nd helper series if needed). Add the fill and font color to the message box. Format the connector as needed and that’s well … just about it.
(A word of caution though – Doing this is Excel 2007 can be a bit frustrating and the result may not be prefect If you able to get it right, let me know.)
You can download a sample worksheet with an example of chart with interactive labels here or click on the button below: