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
Download Workbook
Enter your email address below to download the sample workbook.
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:
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.
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:
We can use a similar technique, this time displaying the 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%
Step 3: Go to the paint bucket icon > set the Fill to ‘No fill’. Give the border a darker colour and increase the width.
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:
Step 5: 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:
Related Lessons
Charting Variances in Excel – a bit of fun with emojis!
Checking Values in Range Objects With VBA
Rashad
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
Mynda Treacy
Hi Rashad,
Glad you find our content helpful 🙂 In terms of showing the difference between Actual and Target, you can do this a number of ways:
1. Add it as a series on the secondary axis
2. Use custom chart labels linked to the variance
3. Only plot the variance in the chart
4. Show the variance in the axis labels
Hope that gives you some ideas.
Mynda
Rashad R alyaheby
Thank you very much Mynda for your help. the answer is very usfel
Allyn
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?
Mynda Treacy
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