Charts and Dashboards: Dynamic Chart Labels

Welcome back to this week’s Charts and Dashboards blog series. This week, we show you how to create dynamic chart labels.

I have created a straightforward line chart in Excel and want to show both the data values and label the chart so that the label attaches itself to the position of the final data point rather than in a legend somewhere.

In this example, the aim is to attach the label for the chart series to the series itself, e.g.

As you can see from the above figure, the line chart’s label appears on the right hand side next to the final data point in the series. If the values were to change, the label would move too:

You may note that the final period is earlier and that the values for the final point differ – yet the labels hang on steadfastly.

So how do we do this?

First of all, make sure your chart data is collated in a Table.

The benefit is that if charts are linked to a Table range and the range is contracted or extended, the dependent chart will update automatically without having to use the ‘Select Data…’ functionality.

Creating a line chart from this Table is trivial. Simply highlight the Table and click on the Quick Analysis tool in the bottom right hand corner (CTRL + Q):

Following the resultant prompts leads to a line chart in all of about two seconds. Right-clicking on one of the data series then allows you to add data labels:

By default, Excel adds the values. Right-clicking on these values and selecting ‘Format Data Labels…’ from the pop-up context menu triggers the ‘Format Data Labels’ task pane and allows you to choose what should be displayed (e.g. value, series name, category name), where it should be displayed (e.g. left of the data point, above it, below it) and how it should be displayed (format to use):

Selecting ‘Series Name’ in ‘Label Options’ almost provides what is required:

The problem here is that we only require the series name against the final data point. If we need this to be flexible, manual adjustment is insufficient: we need to cheat.

‘Cheating’ requires us to add two more columns to the underlying data Table:

Assuming the first three columns have been labelled Date, Sales Input and Costs Input as in the example illustration above, we add two more columns Sales and Costs (N.B. in a Table, the same column heading may not be used more than once). The formulae in the two columns would be as follows:

and

The @ symbol for Tables in Excel 2010 onwards signifies that the formula is referring to the data point for that field in that row, e.g. the formula highlighted in cell I35 in the image is essentially

which is effectively

This is necessary as Table formulae do not like calculations linking to other rows.

These formulae cause the corresponding Sales and Costs values only to appear in the final row of the table, with #N/A elsewhere.  Normally, I would strongly recommend against having prima facie errors in an Excel file, but here they are useful – this is the syntax required for these data points to be ignored by the chart engine.

You are almost done.  All that is left is to highlight the revised Table and recreate the line chart from earlier.  After selecting the Data Labels for the Sales Input and Costs Input series (just as previously), you simply add Data Labels for the two new “series” Sales and Costs even though they are singleton points:

Et voila! You have created the chart required:

That’s it for this week. Check back next week for more Charts and Dashboards tips.