11. Actual Versus Target Charts

Overview

A common chart we need in business is plotting actual numbers vs targets. Here, we'll learn how to create this chart in an easy-to-read format.

To explore more Kubicle data literacy subjects, please refer to our full library.

Summary

  1. Chart Overview (00:04)

    Plotting actual figures against a target or budget is a common business task. We can do this with a simple clustered column chart. However, it’s possible to make the target look better by converting it from a column into a diamond marker with a label showing the target value.

  2. Creating the Chart (00:49)

    We start with a clustered column chart showing actual and targeted revenue. To create the diamond markers, we first convert the target series to a line. We right click one of the target columns, select Change Series Chart Type, and change the chart type for the target series to a line chart with markers.

    We then edit the line by right clicking it and selecting Format Data Series. Under the line options, we set the line type to No Line, and under the marker options we set the marker type to a diamond. This converts our target data series to a series of diamond markets.

    Finally, we move the labels for the markers so that the labels don’t cover the markers. We right click a label, and select Format Data Labels. We then change the label position from Center to Right. This completes our chart which aligns the targets vertically with the column of actual data.

Transcript

One of the most common charts you'll create in business is plotting actual numbers versus the budget or target figures. In this lesson, we'll create a column chart comparing actual revenue and target revenue. In the following example, I have a row of actual data and a row of target data that corresponds to annual revenue for 2008 to 2013.

I have this data plotted as a clustered column chart, and this actually does a reasonably good job of comparing the two. However, if we change the format of the target numbers to something less intrusive than a column, I think it could look much better. So instead of having a column for the target, I'm going to just have a diamond marker with a label showing the target value. So how do we create this? Well, the diamond markers are created as part of a line chart. So let's first select the target data series, right click, and change series chart type.

And I'll change to a line with markers.

I'll then press OK.

And this converts my column to a line. Now I need to make some edits to this line. So I'll first select it, right click, and format data series.

Under the Line Color, I'll say no line to remove the connections between the markers.

Under the Marker options, I'll change the shape from a square to a diamond, which I think looks a little better.

And under the marker fill, I'll make the fill a brighter red, which makes it a little easier to read.

Then I'll click Close.

And as you can see, this allows us to compare our targets to our revenue in a much more visually pleasing way.

Currently, however, the labels for the markers are covering each individual marker. So we'll need to move them. To move the labels, select them, right click, and then go to Format Data Labels.

And under Label Position, I'll simply moved these labels all to the right.

Then I'll simply click Close, and as you can see, our targets are now much easier to read.

For me, when we have the targets aligned vertically with the column itself, it makes our chart much easier to read than the clustered column option that we had earlier. So the next time you need to plot actuals versus a target, go beyond the simple clustered column chart and switch the targets to diamond markers as I have done here.

Charts and Dashboards
Charts in Depth

Contents

My Notes

You can take notes as you view lessons.

Sign in or start a free trial to avail of this feature.

Free Trial

Download our training resources while you learn.

Sign in or start a free trial to avail of this feature.

Free Trial