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:
- Insert a column chart and set first series as target and second series as actual. Also select months as horizontal category.
- When all is in place, click on actual series on the chart and access “Format Data Series” screen from right click menu.
- Set this data series as “Secondary Axis”. And close this window.
- Right click on the newly placed secondary vertical axis( on the right) and access “Format Axis” screen.
- Set min and max values as the same values as primary (left) vertical axis. Exit this screen.
- Left click on secondary vertical axis and delete it.
- Access “Format Data Series” screen for target series and decrease gap width so target columns become a little larger than actual columns.
- 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.