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.
Lee says
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.
Jon Peltier says
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.
Hesley says
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
Jon Peltier says
Hesley –
I wrote about that problem long ago on a retired web page named “Columns on Two Axes”. I retired that page after deciding that charts with two axes are too confusing to read, particularly when you’re trying to figure out which bars go with which axis.
Hesley says
Thanks! It worked perfectly. My Chart as only 2 series.
Shelley says
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!
Juvenal Faria says
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.
Jon Peltier says
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.
DLE says
Please bring back the examples that show how to use tertiary or more axes. Panel charts are not always sufficient. Panel charts take up a lot of vertical real estate to convey the same information. And multiple axis plots are easy to get used to when you and your audience are frequently looking at plots formatted in the same way.
Thanks for you help.
Dogcheese says
Hello … I am in a situation where I also need a tertiary Y Axis. My boss (the president of the company) wants to see a PerCap line, attendance line, and total revenue line per month of the year.
I have gotten the primary and secondary correctly, but he insists on seeing the tertiary axis of total revenue (even though he can multiply the first two numbers to give him the third) on the same graph.
I showed him the panel charts but for him it is not sufficient.
Can someone please help?
Thanks!
Jon Peltier says
President of the company, eh? Not enough reason to perpetrate a bad practice.
If the three items are shown in three panels, they are all plotted on the “same graph”. The important axis, months, is shared, and using separate panels prevents the lines from overlapping in arbitrary ways.
Jim says
Hi – Please, please, please bring back the post that shows how to show clustered columns with a secondary Y axis. I and the rest of a 7-man team are all currently immobilised trying to work out how to construct one of these. We can’t use panel charts because of a document page limit. I’m begging you. Please.
Jon Peltier says
Jim –
Anyone trying to read the graphs will be immobilized trying to extract meaning.
Any topics retired for cause will remain retired.
Jared says
Hi Jon
I found your panel graphs very useful, but when following your instructions word for word I seem to run into issues when converting the secondary axis to be a primary axis (for your step to label the 3 panels using A-axis data as you named it). The whole chart becomes unreadable with the original lines being crunched up against the left and the ‘new’ primary XY plot being crunched up against the right hand side.
I can see it is a scaling issue but I can’t seem to solve the problem, even after adjusting the scales. I am using Excel 2007.
Thanks
Praween Kerketta says
Dear sir,
Please explain the various steps for constructing panel chart with different scales in the different panels. The tutorial “Excel Panel Charts with Different Scales” is not very clear.
I am not able to understand the formula used – =((B2-E$9)/(E$10-E$9)*E$12+SUM($D$12:D$12))/SUM($E$12:$G$12) as the formula does not generate the value as shown in the tutorial.
Thnaks in advance.
Jon Peltier says
Praween –
The formula you pasted into your comment is the exact formula I pasted into a new workbook with the appropriate values in the appropriate cells, and the formula’s results are exactly as shown in this example.
Make sure you have the $ signs in the correct places in the formula. Mixed absolute/relative references can be tricky.
Praween Kerketta says
Thanks for your response.
But I am still confused. Can you please post me the examole in workbook for ready refernece.
Praween Kerketta says
Dear sir,
I tried again and again. Finally I could make out that the formula you had given actually works. Now every thing works fine. But now I am stuck at second part of the tutorial where you have calculated X-left and X- Right data in column I&J.
Please elaborate how have you calculated these values in the tutorial ?
Jon Peltier says
Part of the problem is that line and XY chart type combinations do not always work in Excel 2007+ as they used to when I wrote this tutorial in Excel 97. I do need to rewrite the tutorial.
The simplest way forward is to keep the dummy axis XY series on the secondary axis, format the secondary horizontal axis to have a min of zero, a max of 1, and not line or labels (so it’s hidden), and use zero for X-left and 1 for X-right.