Combining Charts In Excel
One of the most common questions I get asked in relation to charts is how to combine two charts on one. The chart below shows actual sales numbers and target sales numbers. This is a common comparison of targets or expected numbers against actuals. The values can all be seen, the target numbers are blue columns and the actual numbers are the red columns.
However, to make it easier to follow, I’m often asked how to leave the actual numbers as columns and show the targets another way, for example a line chart as below. For the sake of presentations and quick displays this seems to be a preferred layout for a lot of people.
More recent versions of Excel include the options to create combo charts, but for older verions combining charts in Excel is easy to do, with some simple steps. The process will move the target data onto a secondary axis. To ensure that the end result is correct, it is important that the primary and secondary axis units are the same. I’ve also included a slowed-down video demonstrating this.
- Insert a normal 2d-column chart with the target and actual data
- Right-click on one of the target columns in the chart
- Select “Format Data Point”
- Change “Plot Series On” to “Secondary Axis”
- Click OK
- Right-click again on one of the target columns and this time “Change Series Chart Type”
- Select the Line chart
- On the left-hand side is your primary axis which relates to the actual data. Right-click on the axis and select “Format Axis”
- Set “Minimum”, “Maximum” and “Major Unit” to “Fixed” and set the value you want. Note these values and press the close button
- On the right-hand side is you secondary Y axis (for the target data). Right-click on the axis and select “Format Axis”
- Set “Minimum”, “Maximum” and “Major Unit” to “Fixed” and set the values to match what you noted for the primary axis and press the close button.
- After this, it is just formatting the charts to suit your needs.
If you have problems playing the video, it is also available here on youtube
