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
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:
Charting Variances in Excel – a bit of fun with emojis!