Excel charts secondary axis: how to improve your chart
The Secondary Axis is an effective way of improving your Excel charts.
The Secondary Axis is very useful when you need to represent on the same chart two sets of data which have very different scales.
For instance, you might need to display ‘Revenue’ data and ‘Revenue per Unit’ on the same charts starting from the following table.
Secondary Axis in Excel – Starting Point
Take notice of how the figures for ‘Revenue’ are all in the thousands of dollars while the figures for ‘Revenue per Unit’ are all in hundreds.
A resulting 2D Column chart would look like this…
Secondary Axis in Excel – Problem with different sets of values
The ‘Revenue per Unit’ information, displayed in red, does not add value to the chart as its scale is very small compared to the larger set of ‘Revenue’ values.
We can remedy this by selecting the series on the chart, right clicking ‘Format Data Series’ and then select Secondary Axis within the ‘Plot Series’ option.
Plot Series on Secondary Axis in Excel
By doing so, Excel will create a secondary axis on the chart on which it will display the selected series. The axis will be automatically formatted according to the scale of the selected series so that the axis occupies the same portion of the chart as the primary axis.
The chart might need some more adjusting, depending on the type of chart selected, as in the case of this particular example.
Secondary axis in Excel – Chart needs extra work
For instance, by clicking on the ‘Revenue per Unit’ series and Changing Chart Type you can obtain a chart like this.
Secondary Axis in Excel – Meaningful chart
The one thing you might want to do is to add Titles to both the Primary and Secondary Axis to help the reader in understanding what the two different axis represent.
Secondary Axis on Excel Chart – Add Titles
The final result looks like the following chart on which you can do more work depending on your needs.
Secondary Axis in Excel Chart – Final Result
