11. Actual Versus Target Charts
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.
Actual vs Target charts
- A common chart we often need to create in Excel is comparing actual figures to targets
- A clustered column chart does a reasonable job at this task
- However, combining a column with a marker is a much better option
How to combine columns and markers
1 Start by creating a clustered column chart
2 Then select the Target data series and right-click Change Series Chart Type
3 Select a Line chart with markers
4 Remove the line and place the marker labels to the right of the markers
One of the most common charts you'll create in business is plotting actual numbers versus the budget or target figures. In this lesson, I'll show you how to do this effectively in Excel. 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 move 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 actual versus the target, go beyond the simple Clustered Column Chart and switch the targets to Diamond Markers as I have done here.