Excel Actual vs Target Chart

Mynda Treacy

November 6, 2019

Often, you’ll find Actual vs Target charts based on categorical data in the form of a column chart, however they’re slow to read.  Thankfully we can make a big improvement in how quick and easy they are to read with a few simple changes.

Watch the Video

Subscribe YouTube

 

Download Workbook

Enter your email address below to download the sample workbook.

By submitting your email address you agree that we can email you our Excel newsletter.

Two Series Actual vs Target Chart

Let’s take the two series actual vs target chart example below. It’s slow to interpret because it takes time for the reader to compare the height of each column for each category on the horizontal axis:

two series actual vs target chart

Whereas with two changes we can compare the actual to target at a glance, almost without even focusing on the chart columns at all! This effect is often called a thermometer chart.

thermometer chart

It’s so clear that we can still see the actual vs target in our peripheral vision while focusing on the vertical and horizontal axes. It reminds me of those magic eye pictures where in order to see the 3D picture you don’t focus on the detail. If you were a kid in the 90s, you’ll know what I mean!

Three Series Actual vs Target Chart

Sometimes we’ll have three series; actual vs target vs forecast, or as in the example below, stretch target:

three series actual vs target chart

We can use a similar technique, this time displaying the stretch target as a dashed line:

stretch target as a dashed line

Creating Thermometer Charts

Steps for creating thermometer charts:

Step 1: Select one of the series in the chart > CTRL+1 to open the format data series pane

Step 2: Go to the Series Options tab > set the series overlap to 100%

set the series overlap to 100 %

Step 3: Go to the paint bucket icon > set the Fill to ‘No fill’. Give the border a darker colour and increase the width.

set the fill to no fill

If you’re working with a 3 series chart,

Step 4: Change the chart type to a Line with Marker via the Chart Design tab:

change chart type to a line with marker

Step 5: Format the line to ‘No line’

format the line to no line

Step 6: Format the Marker to the dash built in style and size 14, or whatever you think is appropriate for the width of your chart columns:

format the marker

Related Lessons

Charting Variances in Excel – a bit of fun with emojis!

variance chart

5 thoughts on “Excel Actual vs Target Chart”

  1. Hello Mynda
    thank you very mcuh for your effort. your vedios are really beneficial.

    i have a question if you can help me, how can i show the delta between the actual and target

    Reply
  2. Hi Mynda, as always this is so simple yet effective. Thank you. But I do have a question regarding data labels. I would like to have data labels for the Actuals and there are various options for label position but I cant make the Actual data labels appear above the Target column, instead they float above the actual so for some they are within the target column, some overlap and some are above. Any advice please?

    Reply
    • Hi Allwyn,

      Glad you liked it. Label position can be a problem in cases like you describe. The solution is to add a dummy series to your chart that is higher than the target values, then you can assign the “actuals” labels to that series using the ‘Value from cells’ option. Then hide the dummy series by setting its fill colour to none. See step 8 in this post for Value from Cells.

      Mynda

      Reply

Leave a Comment

Current ye@r *