Comparison Chart

Here is a combination chart with a trick. It is very compact and it shows comparison between two series very clearly. It is especially useful in comparing results to a varying target values. That’s why, I called it comparison chart.

For this tutorial, we have a data set that consists of 2 series. Target and Actual. We are going to compare actual sales figures to target sales figures. Here is how our data looks like:

Here is the steps for making the chart:

1. Insert a column chart and set first series as target and second series as actual. Also select months as horizontal category.
2. When all is in place, click on actual series on the chart and access “Format Data Series” screen from right click menu.
3. Set this data series as “Secondary Axis”. And close this window.
4. Right click on the newly placed secondary vertical axis( on the right) and access “Format Axis” screen.
5. Set min and max values as the same values as primary (left) vertical axis. Exit this screen.
6. Left click on secondary vertical axis and delete it.
7. Access “Format Data Series” screen for target series and decrease gap width so target columns become a little larger than actual columns.
8. Arrange formatting (column colors, gridlines, etc) and it is finished.

You can download the example workbook here: Comparison Chart Example.

If you need a chart for comparing performance against a static target, you may find Conditional Colors In Column Chart post useful.