Categorized | excel chart

Chart Label Trick – Using Interactive Labels on Chart

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.


forms-scrollbarOnce 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:


Excel Formula, Excel Chart, Excel Macro, Excel VBA, Pivot Table Excel, Excel Dashboard

What Do You Think ?

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Comments and Trackbacks

  1. Bruce wrote:

    This is pretty cool. But, I need to create a Stacked bar chart , like this. Is it possible? I played around with it and couldn’t get the interactive labels to stay visible.

  2. Ajay wrote:

    Bruce – A stacked bar chart is quite possible. The only thing you’ve got to do is to a) change the scale maximum to a higher value and b) adjust the second helper series to show a higher offset value (increase it from the present 100 to 500 or something that suits your need). You can play around it till you get it right.

    I should’ve mentioned the scale part in the post itself – something which I’ve added now.


  1. Dashboard Best Practice – Google Analytics Intelligence Report | Pointy Haired Dilbert: Charting & Excel Tips - wrote:

    […] Be smart with data labels: While data labels can help understand the charts, often dashboards have too many charts and thus data labels make it look cluttered. A simple solution is to use data labels conditionally. Ajay at has another good example at databison on interactive data labels. […]

  2. Timeline Chart in Excel with Interactive Event Descriptions | Excel & VBA - da Tab Is On wrote:

    […] our previous post on adding interactive labels to chart, we saw how to fit a large number of event descriptions in a single chart. Today we look at a […]


Keep up with the latest stories - Delivered right to your inbox


English flagItalian flagKorean flagChinese (Simplified) flagPortuguese flagGerman flagFrench flagSpanish flagJapanese flagArabic flagRussian flagDutch flagHindi flagFilipino flagIndonesian flagThai flagTurkish flagPersian flag
treeemap software for excel