In my last post, Secondary Axes in Charts, I described an essay by Stephen Few in which he concluded that secondary axes provide no benefit to good infographics. I have come to the same conclusion myself: secondary axes are more likely to confuse and obscure the data, than to clarify relationships in the data.
In the first comment, Lee countered with a description of charts he frequently uses, which have dual axes. His axes are not independent of each other, however, but instead are tied together, showing different measures of cost of fuel per energy content. The units of measure are different for oil (gallons) than for coal (pounds), for example, and industry has developed standard measures of each. With a few constants, however, Lee is able to relate primary and secondary scales on a chart.
Lee’s charts do not have truly different scales. He uses a single scale, with two sets of numbers to describe them. That’s like reporting your weight in lb and kg, or your car’s speed in mph or km/hr. The numbers are different, but the scales are proportional, not independent. I thought, what a good opportunity to show how to construct a chart with this kind of proportional scales, which are the only kind of intentionally visible secondary axes you should employ in your charts.
Standard Secondary Axis Construction
In this example, I’ll plot fictional daily temperatures in Fahrenheit and Celsius. The data is shown in this simple table. The Fahrenheit values were derived from the made-up Celsius values using the well known F = 9/5 C + 32 relationship.
| A | B | C | |
| 1 | Date | Temp (°C) | Temp (°F) |
| 2 | 3/13/2008 | 5 | 41 |
| 3 | 3/14/2008 | -1 | 30.2 |
| 4 | 3/15/2008 | -2 | 28.4 |
| 5 | 3/16/2008 | 3 | 37.4 |
| 6 | 3/17/2008 | 4 | 39.2 |
| 7 | 3/18/2008 | 13 | 55.4 |
| 8 | 3/19/2008 | 10 | 50 |
| 9 | 3/20/2008 | 12 | 53.6 |
| 10 | 3/21/2008 | 4 | 39.2 |
| 11 | 3/22/2008 | 5 | 41 |
| 12 | 3/23/2008 | 7 | 44.6 |
| 13 | 3/24/2008 | 8 | 46.4 |
| 14 | 3/25/2008 | 5 | 41 |
The first step in creating a chart with primary and secondary value axes is to plot the data. In Excel you need to have at least two series in a chart if you want secondary axes, because each axis group requires at least one series. The data will be plotted with the Dates as the X variable and the Temperatures as the Y variable(s). At first the chart has only a primary Y axis.
So far so good. The curves actually relate to the same temperatures, but they are measured on different scales. Let’s move the Fahrenheit data to the secondary axis. Classic Excel (97 and probably earlier to 2003): double click the series, click on the Axis tab, choose the Secondary Axis option. New Excel (2007, and 2010 if they don’t change it again): select the series, press Ctrl+1 (numeral one) to open the formatting dialog, and on the main screen, choose the Secondary Axis option.
The scales still do not coincide. Excel’s built-in axis scaling algorithm has assigned a Y axis minimum of zero to the secondary axis, even though the actual minimum is almost 30 on a scale of 60. No matter: like everything else, if you want something done right, you have to do it yourself. I decided to clean up the Celsius axis by locking in the minimum and maximum at -5 and 15, with a step (major unit) of 5. With the 5/9 relationship between the two scales, the works out to a minimum and maximum of 23 and 59 on the Fahrenheit scale, with a step of 9.
The scales coincide, as evidenced by the alignment of the Celsius and Fahrenheit data. I’ve hidden the Fahrenheit lines os you can see that the Celsius lines connect the Fahrenheit points perfectly. The only problem is that the Fahrenheit scale is a bit unfamiliar; people would probably prefer a scale that started and incremented in multiples of five. in addition, having to plot two sets of data is a bit redundant, because the points coincide exactly.
Custom Axis Scale
Let’s rebuild the chart using just the Celsius data points, and we’ll fake the Fahrenheit scale.
We need a range of values for our fake Fahrenheit axis. From our first experiment we know that -5 to 15 Celsius encompasses 25 to 55 Fahrenheit, os this is the table we will use. The Celsuis values this time were calculated from the Fahrenheit labels. We will add an XY series to the cahrt, using the dates in column E as the X values, the Celsius values in column F as the Y values, and the Fahrenheit values in column G as the “axis” labels.
| E | F | G | |
| 1 | Temp (°C) | Temp (°F) | |
| 2 | 3/27/2008 | -3.88889 | 25 |
| 3 | 3/27/2008 | -1.11111 | 30 |
| 4 | 3/27/2008 | 1.666667 | 35 |
| 5 | 3/27/2008 | 4.444444 | 40 |
| 6 | 3/27/2008 | 7.222222 | 45 |
| 7 | 3/27/2008 | 10 | 50 |
| 8 | 3/27/2008 | 12.77778 | 55 |
To add the points, copy the range E1:F8, select the chart, and use Paste Special (Classic Excel: Edit menu, New Excel: Home tab of the ribbon, far left) to add the data as a new series, X values in the first column, series name in the first row. As desired, the points line up along the right edge of the chart.
Next we need labels on the data points. There are two very good chart labeling utilities, both free, that integrate nicely with Classic Excel. I haven’t tested either in New Excel: I suspect they work but may not interface as nicely with the ribbon. The utilities are Rob Bovey’s Chart Labeler from AppsPro.com, and John Walkenbach’s Chart Tools from J-Walk.com. Download, install, and use one of these utilities to add chart labels from the range G2:G8 to the data points, in the position to the right of the points.
The last step is to make the XY series look like an axis. First, format the series so it has no markers and no lines. Add positive X error bars to this series, with a nominal value (in this case, 0.15 seems good). Format the error bars so there sre no end caps on the bars, and the line color matches the gray of the chart frame.
Note: I have just tried this in Excel 2007, and it doesn’t work. In Classic Excel, there is a small border around the visible plot area of a line or XY chart that has markers in any of its series, which allows a marker to be located on the edge of the chart and not be truncated by the plot area’s border. This allows the error bars to extend away from the chart. New Excel does not have this little border region around the plot area, or maybe the error bars do not appear within the border. I’ve had other issues with Excel 2007 error bars, and maybe one day I’ll rant post about them.
Here is the finished chart in Classic Excel. In New Excel you could change all your ticks in the chart’s axes to Cross instead of Outside, then use the plus-sign markers for the dummy scale axis. Or you could use the small dash marker, but that produces a tick which is too thick.
Lee says
Thanks – I do agree with Stephen Few’s points in his essay. I think you captured my thoughts here as well.
Nice to pick up a tip or two on formatting and labeling the second axis.
I have read your blog for a while and keep finding great tips and tricks. Glad to see that you are more responsive than the servers. Keep up the great insight.
– Lee
Jon Peltier says
I came across this history of the Fahrenheit scale, which describes its imperfect beginnings, and also the imperfect beginnings of the Celsius scale:
The Straight Dope On the Fahrenheit scale, do 0 and 100 have any special significance?
savithri.v says
Dear Sir,
I have a very basic doubt in scaling..
In one of the graphs with secondary axis, I used:-
Primary axis -90 to 90, (major unit 30)
Secondary axis 0% to 30% (major unit 5%)
So 0 in primary axis coincides with 15% in secondary Y axis.
My son picked up some values between 0 & 15%. He asks me when I would have plotted these values in the 1st quadrant on a graph sheet, why is it I’m doing this in the 4th quadrant in Excel. He is not convinced when I ask him to read the values for the %s on the RHS & ignore the LHS.
He feels that the secondary axis has to mirror primary axis & it is not enough to just align values on primary & secondary axis. For him 0 in primary axis should coincide with 0 in secondary axis whatever be the scales on either sides & whatever be the medium used for plotting them. Is he right?
Savithri
Jon Peltier says
First of all, it’s best not to use multiple axes (see Secondary Axes in Charts). If you need multiple scales, make a panel chart to prevent confusion, or normalize values.
But if you can’t avoid it (not just that you’re too laze, but your boss insists or whatever), it’s best if both axes reflect a common baseline. To make the horizontal axis cross both vertical axes at Y=0, you need to adjust the mina nd max of both vertical axes. This is easiest using VBA, as described in Align X Axis to Y=0 on Two Y Axes (VBA).
savithri.v says
I normally keep same Min & Max on both axis, same Major unit on both sides.
I sincerely attempted to do a panel chart in the present case to avoid a dual axis but found it difficult with a negative value. I chose the easier option for separating the value lines from the % lines by choosing the scale mentioned above to make it panel like & ended up being questioned about my knowledge of elementary mathematics
Thanks,
Savithri
Jon Peltier says
Hi Savithri –
If you use the methodology in Panel Charts with Different Scales, you don’t need to worry that on series had negative values. The min values in row 9 of the data range will be negative, and the rest of the formulas will adjust accordingly. Your only duty is to somehow provide a line for the X axis in that panel, using an approach like Adding a Line to a Column or Line Chart, or if you’re industrious, you can add a secondary category axis which crosses the secondary value axis at zero, and has the same scale as the primary category axis but has no axis tick marks or tick labels.
savithri.v says
I got this right, in fact very easily, which makes me slightly apprehensive about the correctness of my graph.
I didn’t understand the X axis part in your explanation above though I got this straight away by following the procedure described by you in ‘Panel Charts with Different Scales’
Your directions were extremely clear & try hard as I could in the past one hour, still didn’t find any reason for goofing it up
I’ve mailed you a sample – I wonder if I’ve missed out something…
savithri
Jasmine Goh says
Dear Sir,
I really love your solution to have panel chart with different scale (https://peltiertech.com/Excel/ChartsHowTo/PanelUnevenScales.html). It allows the user to analyse better. I tried to follow the steps that you have indicated in your web link unfortunately i didn’t get the desired result as published. When the XY series is placed on the primary axis, the data label will not appear. I am using excel 2007, wondering if that’s the cause for it? Is there another solution for user who are using excel 2007? Appreciate your advice on this.
Jon Peltier says
Jasmine –
Do the data points themselves appear? It may be that you are using 0 as the X value, but you need a value of 0.5. If the main series are line chart series, and the Y axis crosses the X axis between categories, that means it crosses at X = 0.5, rather than X = 1.
Jasmine Goh says
Thanks for your speedy reply! I have used the same set of number that you have used & it does end with 0.5. Not sure why i am not getting the same result
balaji says
Hi,
We have plotted two y axis with a common x axis.
the values on both primary axis and secondary axis goes to negative but the problem we are facing is to have a common 0 reference for both primary axis and secondary axis. Which we are not able to plot.
Kindly help as i need to submit my final year project report.
Regards,
balaji
Jon Peltier says
I showed how to Align X Axis to Y=0 on Two Y Axes (VBA) on my web site a while back.
Make sure you aren’t confounding the two series by using two axes: Secondary Axes in Charts often show their data in misleading ways.
Gyuri says
The title “Proportional Scales” is a little bit messleading, because the Celsius and Fahrenheit scale are so called “interval scales”, i.e. they do not have an absolute zero point, therefore ratios between numbers on the scales are not meaningful. Since one cannot divide, one cannot define measures that require a ratio, such as coefficient of variation.
Most measurement in the physical sciences and engineering is done on “ratio scales”. The distinguishing feature of a ratio scale is the possession of an absolute (or objective) zero value. For example, the Kelvin temperature scale has a non-arbitrary zero point of absolute zero, which is denoted 0 K and is equal to -273.15 degrees Celsius. This zero point is non arbitrary as the particles that compose matter at this temperature have zero kinetic energy (except for their zero-point energy). All statistical measures can be used for a variable measured at the ratio level, as all necessary mathematical operations are defined.
See:
http://en.wikipedia.org/wiki/Level_of_measurement
Tom M says
Secondary axis plots that were easier to do with the older version of excel can be very useful when you are plotting things like reactions that have peak times and temperatures that sometimes work in an orderly inverse relationship but when specific variables are induced do not. The best plot for these is a bar/line graph where the two variables are clearly defined. It is also useful when doing Pareto analysis to use the bars for the individual counts and a line that will easily delineate the 80% or 90% cumulative area to clearly concentrate where your focus should be.
Jon Peltier says
Tom –
I am not hesitant to use primary and secondary axes for an audience that has a strong scientific or engineering background, which obviously includes yourself.
Stoyan Karakashev says
I need to know if it is possible to make a secondary X axis. Everyone knows 2 secondary axes are possible – horizontal and vertical. How to make horizontal one?
Jon Peltier says
Stoyan –
It is possible to add a secondary horizontal axis the same way that you add a secondary vertical axis. And a secondary horizontal axis is as prone to comprehension issues as is a secondary vertical axis.
Steve says
Hi Jon
Thanks for a very helpful article.
I thought readers might be interested that there is an unseen catch when adding a secondary y-axis to a chart, at least in Excel 2010. I created a chart following your instrutions showing distortion in four amplifiers. Three data series were linked to the LHS y-axis showing distortion as a percentage. This axis has a log scaling. One data series was linked to the RHS y-axis showing distortion in dB. The two axes were aligned by manually setting their max and min values.
The chart looked fine until I tried to reformat the x-axis (frequency) with log scaling. The three data series linked to the left y-axis rescaled correctly but not the series linked to the right y-axis.
After much searching and heartache, I found that Excel linked the single data series not only to the RHS y-axis, but also to a secondary x-axis. This secondary x-axis was not visible and consequently did not show up in the list of chart elements available for reformatting.
The solution (Excel 2010) is to make the secondary x-axis visible. To do this, click on the chart and go to Chart Tools/Layout/Axes/Secondary Horizontal Axis/Show Default Axis. The secondary x-axis can now be selected and reformatted so that it is identical to the primary axis. It can then be made invisible again from Chart Tools/Layout/Axes/Secondary Horizontal Axis/None.
In a nutshell: if you make a chart with two vertical axes and find that reformatting the horizontal axis does not work for all data series plotted, look for a hidden horizontal axis and make it visible, reformat it as necessary, then make it invisble again.
I hope this prevents someone getting as frustrated as I got. Sometimes one does feel that Excel is very good at applying single-click fancy effects on a chart, effects that I don’t want, yet obscures basic adjustments that should be instantly and intuitively available.