Creating an Actual vs Target Chart in Excel using Floating Markers

Analysis

Colored markers show target values against actual values in columns.

Colored markers show target values against actual values in columns.

Almost everyone in business has heard the maxim, “What you measure is what gets managed.” We know that measuring is important, but measuring is only the start. If you are going to improve what you are measuring you need targets. And before you start changing you need to know how far off the target you are. This Excel trick is an easy way to see the actual value as a column with target value shown as a floating bar, as shown in this figure.

Click here to learn more ways to create budget vs actual charts.

This figure shows a simple Actual and Target column chart. The trouble is that it takes some visual and brain work to compare the Actual and Target columns. We could improve this Actual vs Target chart by creating markers for the Target values.

Create a column chart showing actual and target values.

Create a column chart showing actual and target values.

To create markers we need to first change this dual-column chart into a combination chart with the Target data showing as a line. To do that right-click on a Target column and select Change Series Chart Type. In the Change Chart Type dialog box select Line with Marker for the new Target type. You should now have a chart showing columns for Actual and a line with markers for Target values.

Convert one column into a line chart. The markers will be used to float points

Convert one column into a line chart. The markers will be used to float points

Now, here are the tricks. You need to change the markers into a wide line and change the line color to invisible. To do that, right-click a marker on the line so that the entire line and markers are selected. Choose Format Data Series.

Change Marker Options to Built-In with a line style and size of 10 or 12. This changes the default box marker into a line.

Add line markers to show the target values.

Add line markers to show the target values.

Without closing the dialog box select Line Color and change the line to No Line. Choose Ok.

Add colors to the marker and hide the line

Add colors to the marker and hide the line

While you are in the formatting dialog box you can change the colors of the two data series to fit your organization’s color schemes. Switch between formatting the different data series with the Series Options choice in the Format Data Series dialog box.

Colored markers show target values against actual values in columns.

Colored markers show target values against actual values in columns.

If your main concern is watching the differences and noticing when actuals are below target, then you may want to use a column chart that shows the amount of difference between actual and target.

Click here to learn more ways to build budget vs actual Excel charts.

 

 

 

Share the power...