Sometimes a secondary axis is a necessary evil. After all, most of the time you can’t plot big numbers and little numbers in the same chart without the little numbers getting lost in the scale.
For example, let’s say we want to plot our annual sales and profit margins in one chart. Here’s the data:
And here’s the default Excel chart. You can barely see the columns for the Profit %, let alone get any insight into their shape or trend:
In steps the Secondary Axis to the rescue (right-click the Profit % columns > Format Data Series > select 'Plot series on Secondary Axis'):
Meh, it’s better of course, but it’s still not that quick to interpret because now we have to figure out which axis plots which data. I’ve labelled the axes, but it’s still not ideal, especially since I have to turn my head to the side to read them.
I could move the axis labels to the top and rotate the text, but that’s a lot of fiddling around and it still won’t be that quick to read.
Better to do away with the axis labels altogether and instead, label the data like so:
Notice that I also got rid of the gridlines. We don’t need them since we’ve labelled the data points. Plus, I lightened the line with a thinner, dashed line.
Another option is to create two separate charts; one for the sales values and one for the profit percentages:
Random tip: Did you know CTRL+D will duplicate a chart? Just select the outer edge of the chart and press CTRL+D to make a copy!
Since our horizontal axes line up we can declutter even further and remove one of them so the charts share a single axis:
Tip: Remove the chart borders and insert a text box around both charts. Set the fill to ‘none’, or send the text box to the back so the charts sit on top. Or leave the chart border off and use white space between your charts to create a grouping of sorts.
Well we’ve come a long way since the default chart served up by Excel.
With a few simple changes, we’re able to present our readers with a chart that’s quick and easy to read. Let’s be honest, anything less would be a waste of their time and yours.
Download the Workbook
The workbook includes the examples used in this post and some notes so you can refer to it later.
In my Excel Dashboard course, I cover many more ideas for visualising your data including which charts suit what type of data. Take a moment to check it out and see where your new skills take you.
If you liked this please click the buttons below to share.