Secondary Axes in Charts
by Jon Peltier
Tuesday, March 25th, 2008
Peltier Technical Services, Inc., Copyright © 2012.
Licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported License.
Stephen Few of Perceptual Edge writes about primary and secondary axis scales in the March 2008 Visual Business Intelligence Newsletter.
In this month’s essay, entitled Dual-Scaled Axes in Graphs-Are They Ever the Best Solution?, Stephen asks
Is it ever appropriate to include two quantitative scales on a single axis (Y or X) of a graph? Do dual-scaled axes suffer from problems that we should always avoid? If so, is there a better way to display data when it’s useful to compare quantitative variables that have different units of measure (for example, sales revenue in dollars and the number of units sold)? This month’s article attempts to answer these questions.
Stephen follows a rigorous and logical train of thought as he analyzes the use of dual axes in a chart. He examines and disqualifies one use for secondary axes after another, until he finishes with
I certainly cannot conclude, once and for all, that graphs with dual-scaled axes are never useful; only that I cannot think of a situation that warrants them in light of other, better solutions. I invite you to propose viable exceptions, which I will welcome with open arms.
Over the past couple of years I’ve occasionally mulled over this issue myself, in a much less rigorous process than Stephen has followed, and I’ve come to a similar conclusion. Secondary axes are more confusing than enlightening, and the measures people take to clarify them add clutter and chart junk to their charts. While I have written tutorials about secondary axes (and even tertiary axes), more recently I’ve avoided them, and written about better approaches. Rather than making a single chart with secondary axes, it is more suitable to use a panel chart with different scales in the different panels (Panel Charts with Different Scales).
If you have a common time scale, pin the curves to a reference point, to highlight the relative changes of the data since that time (Charting Dynamic Normalized Ranges).
I must admit that I find Excel’s secondary axes very useful. Looking at it more closely, though, I realize that having secondary axes does not necessarily mean there are different scales. I often use secondary axes in order to combine otherwise incompatible chart types into a single chart, as when using an XY chart series to locate labels or lines in a bar or column chart.
An Excel Gantt chart with milestone markers is impossible without using secondary axes.
But in all of these cases, the secondary axis merely adds a compatible scale to the chart for a different chart type.
Related Posts:
- Secondary Axes that Work – Proportional Scales
- Add Percentages on the Secondary Axis
- Overlapped Bar Chart – Thinner Bars in Front
- Broken Y Axis in an Excel Chart
- Charting Seasonal Sales Data in Excel using Cycle Plots
- Logarithmic Axes in Excel Charts
Posted: Tuesday, March 25th, 2008 under Chart Axes.
Comments: 9
Comments
Comment from Lee
Time: Tuesday, March 25, 2008, 7:41 pm
I am an analyst in the power business, and I am regularly producing charts comparing prices across multiple fuels – natural gas, fuel oil, coal. Each fuel has its own price units – $/mmbtu, $/gal, $/ton – that my audience expects to see. I use the secondary axis to appropriately scale a second fuel price relative to another on the same chart. Since the fuels are substitute commodities, the relationships and correlations through long periods of time hold true, and can be demonstrated well on a single chart.
Because of the simple conversion factors ($/mmbtu = 7.21*$/gal = 1/25*$/ton), I could build these charts with one axis, covert the data and then label the points with the original prices. But it seems so much easier to have the secondary axis defined, the labels are the data, and my work is straight-forward.
I do agree that other data might be less appropriate for this method, but similar type items with scalar relationships seem to make sense. I have tried to use the Normalized method, but that back fires with more confusion.
What gets me is when the scaling of two axes is not tied together when there is a simple defined relationship between the points.
Comment from Jon Peltier
Time: Tuesday, March 25, 2008, 10:14 pm
Hi Lee -
You bring up a very good point. Having worked for many years as an engineer in the US, I’ve had to do my share of work in mixed units. Pounds and kilograms, meters and feet, Fahrenheit and Celsius, you name it. However, this is a different situation than Stephen laid out in his essay. He was critical of multiple series plotted on axes with unrelated setss of units ($ sales vs. units sold, for example). You are normalizing your data to cost per energy content, so you have a single value scale, with multiple ways to represent that scale depending on the type of fuel.
That is like a chart that shows daily temperatures, with Celsius on one axxiss and Fahrenheit on the other. The reader can look at the data points, and look left or right to determine the temperature in whatever units are most suitable. In fact, while waiting for this page to load so I could approve your comment (stupid hosting company), I set up some charts illustrating just this issue, and showing how to set up a “nice” converted axis. Stay tuned, it may be up by tomorrow at this time, if I don’t fall asleep waiting for the server.
Server update: They have finally admitted that this new “platform” they’ve migrated me onto is woefully underequipped to handle lots of MySQL requests. By the weekend they plan to have five additional MySQL servers on line, and things should again be hunky dory. I can’t believe their prediction of server requirements was so far off. I worked on a project a couple years ago where we predicted just this kind of server loading (I had to lern queuing theory and all that), and we got to where our predictions were within a factor of 2. Well, in fairness, I guess my hosting company was within a factor of ZERO.
Pingback from Secondary axes in Excel 2007 « Newton Excel Bach, not (just) an Excel Blog
Time: Friday, February 19, 2010, 10:01 pm
[...] is another matter, but for some ideas on what you can do with secondary axes, have a look at the Jon Peltier Blog (see both the linked post and the “related posts” listed at the bottom for some useful [...]
Comment from Hesley
Time: Thursday, January 27, 2011, 2:40 pm
Your tips are great.
I’m facing a problem that I haven’t seen addressed anywhere.
I have two series of data with clustered column chart. When I add the secondary axis, the columns that where side by side became aligned with the higher valueshiding the lower ones.
Could you help?
Thanks,
Hesley
Comment from Jon Peltier
Time: Thursday, January 27, 2011, 8:47 pm
Hesley -
I wrote about that problem here:
http://peltiertech.com/Excel/Charts/ColumnsOnTwoAxes.html
Keep in mind that charts with two axes are confusing to read, particularly when you’re trying to figure out which bars go to which axis.
Comment from Hesley
Time: Tuesday, February 1, 2011, 12:01 pm
Thanks! It worked perfectly. My Chart as only 2 series.
Comment from Shelley
Time: Tuesday, January 24, 2012, 10:41 am
I am finding this blog increasingly helpful as I explore it! Thanks so much!
Unfortunately, I am in a field where I need to include a tertiary y axis on occasion for data documentation. I read in a previous comment somewhere that it is not impossible. I’m fine with the secondary axis addition and scaling and everything, but can’t figure out how to include a third y axis on one x axis.
Thanks so much!
Comment from Juvenal Faria
Time: Wednesday, May 9, 2012, 8:48 pm
I need to know how to place a third vertical axis on an excel charts using excel 2010.
My graph already has two vertical axis. Every time I tried to place a third vertical axis it still uses the second vertical axis. I followed the instrusctions below and did not get it to work? Can anyone help
1 Open the chart you want to work with by clicking the Office button, “Open,” and selecting the file.
2 Click the chart you want to add the additional axes to. This brings up the “Chart Tools” tab in the top right corner of Excel.
3 Click the “Format” tab. Select the arrow under “Chart Area” (in the “Current Selection” group on the left side of the toolbar). This brings down a selection box. Choose the series you want to add an additional axis to.
4 Click “Format Selection.” Format Selection is directly below the “Chart Area” tab.
5 From the “Series Options” tab (this should be the first one displayed), click “Secondary Axis” and then click the “Close” button.
6 Click the “Layout” tab and choose “Axes” from within the Axes group. Click on “Secondary Vertical Axis” and choose a display option.
7 Repeat Steps 2 to 6 to add another axis.
Comment from Jon Peltier
Time: Thursday, May 10, 2012, 7:01 am
Juvenal -
In the sixth paragraph of this article I state that “Secondary axes are more confusing than enlightening”. I encourage people not to use them because of this.
Tertiary axes (secondary plus one, that is, the third axis in a given direction) are even more confusing, so I would definitely advise against them. They are not even a native Excel chart feature, so you would have to go to great lengths to fake a tertiary axis. I’m not going to show how to do this and let you waste your time making confusing and cluttered charts.
Write a comment
I welcome comments from my readers. If you have an opinion on this post, if you have a question or if there is anything to add, I want to hear from you. Whether you agree or disagree, please join the discussion.
If you want to include an image in your comment, post it on your own site or on one of the many free image sharing sites, and include a link in your comment. I'll download your image and insert the necessary html to display the image inline.
Read the PTS Blog Comment Policy.