Secondary Axis Charts in Excel
If you know what a secondary axis chart is, then you know what a challenge it can be to show disparate values on the same chart. This used to be somewhat cumbersome in older versions of Excel, but with Office 2007 and Office 2010 it’s a piece of cake!
For those unfamiliar with the term, a secondary axis is useful when you have to display whole numbers and related percentages, like cost, price and profit percentage. The cost and price data would be whole numbers, perhaps in the thousands, while the profit number is a percentage, a number less than 1. Those percentages would completely disappear on a scale intended to show dollars in the thousands.
So, how do we do that?
1. Create a chart from your data. Use he shortcut key, F11. That will put a column chart on another worksheet. (If necessary, click Switch Row/Column button to arrange the chart so that the “invisible” Profit % is in each set of the Cost and Price numbers. )
2. Click on the Layout Chart Tools Contextual tab and in Current Selection group, click on the drop down arrow in the Chart Elements field and select Profit %.
3. Right beneath the Chart Elements field, click the Format Selection group.
4. In the Plot Series On section of the Format Series Dialog box, choose Secondary Axis.
5. Right click on the newly visible Profit % series and choose Change Series Chart Type.
6. Choose a chart type from the Line category.
That’s it!
So, how do we do that?
1. Create a chart from your data. Use he shortcut key, F11. That will put a column chart on another worksheet. (If necessary, click Switch Row/Column button to arrange the chart so that the “invisible” Profit % is in each set of the Cost and Price numbers. )
2. Click on the Layout Chart Tools Contextual tab and in Current Selection group, click on the drop down arrow in the Chart Elements field and select Profit %.
3. Right beneath the Chart Elements field, click the Format Selection group.
4. In the Plot Series On section of the Format Series Dialog box, choose Secondary Axis.
5. Right click on the newly visible Profit % series and choose Change Series Chart Type.
6. Choose a chart type from the Line category.
That’s it!
