An easy way to create a dual chart in Excel, trending with a secondary axis

Charts by default display a primary vertical axis when created. This works if there is only one unit of measure in the data. However, should there be different units of measure in your data, a secondary axis will be required, thus allowing you to create a dual chart in your Microsoft® Excel® workbook.

In our practice exercise we are going to display the sales target and actual data on the primary axis as values, and the variance as a percentage on the secondary axis.

 Note: You are welcome to download the workbook to practice.

Applies To: Microsoft® Excel® 2007, 2010 and 2013.

1. Select the entire data range (A2:D14) then press F11 to create a quick chart.

2. Right click on one of the series on the chart, and select format data series.

3. Then under series options, select series variance.

1_ExcelOnSteroids_DualCharts

4. Right click on one of the variance series on the chart.

2_ExcelOnSteroids_DualCharts

5. Select Format Data Series, then select Secondary Axis.

3_ExcelOnSteroids_DualCharts

6. Right click on one of the variance series and select Change Series Chart Type.

7. Select line with markers under chart type for variance.

4_ExcelOnSteroids_DualCharts

8. Select OK, now right click on the Primary axis and select Format Axis.

9. Enter 2500 under major units and select thousands next to Display units.

5_ExcelOnSteroids_DualCharts

10. Add a suitable chart title.

11. Right click one of the horizontal grid lines and select delete.

6_ExcelOnSteroids_DualCharts

The primary axis displays the target and actual data. As you can see, a secondary axis that displays the variance in percentages has been added, thus showing you a trend.