Broken Y Axis in an Excel Chart
by Jon Peltier
Friday, November 18th, 2011
Peltier Technical Services, Inc., Copyright © 2012.
Licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported License.
If you’re looking for a tutorial on breaking an axis scale, you won’t find it here. Instead you’ll read why breaking an axis is a bad idea, and you’ll get a tutorial in Panel Charts, which are a more effective (and easier) means to show your data.
The Problem
People frequently ask how to show vastly different values in a single chart. Usually they ask because a few very large values (for instance, Paris in June or Madrid in May in the chart below) overwhelm the other, relatively much smaller, values.
Logarithmic Scale
One suggestion is to use a logarithmic scale. For scientific data presented to scientific audiences, this is often an excellent suggestion. For the general public, and for general data, this may not be so useful. Especially in a bar chart, where the length of bars is important to comprehension, not some mathematical abstraction of length.
Broken Axis
Another suggestion is to “break” the axis, so that part of the axis shows the small values, then another part of the axis shows the large values, with a section of the axis scale removed. Sounds good, but you’ve lost any correlation between the large and small values. Also our eyes are likely to see the two broken bars in the chart below as only about twice the value of the tallest of the unbroken values (despite our conscious brains “knowing” that the axis has been cut).
Another problem with this approach is that it’s cumbersome to create and nearly impossible to maintain charts like this.
Panel Chart
A better suggestion than either a log scale or a broken axis is to plot the data in a panel chart. This chart has two panels, one with an axis that shows all the data, the other with an axis that focuses on the small values. I generally advise strongly against using any kind of gradient in a chart, because the gradients are pretty much meaningless. In this chart, the gradient at the tops of the (truncated) large values are not meaningless, but are intended to show the large values extending high up into the clouds.
Making the Panel Chart (It’s Easy!)
If you want to play along at home, the data is located in BrokenYData.csv.
The panel chart is really not too complicated. It consists of one set of data plotted on the primary axis and another set on the secondary axis. The primary axis data shows all the data using the full Y axis scale.
The secondary axis is scaled to show the smaller values. Note that the two very large values (>30M) have been truncated at a suitably small value (7.5M).
When seen as part of a panel chart, the primary axis (left of chart) has been scaled so that its data occupies the top half of the chart.
The secondary axis (right) has been scaled to keep its data within the bottom half of the chart.
It only takes a little experience, imagination, and knowledge of algebra to choose suitable scales. Fortunately readers of my blog are gifted in these characteristics.
The first step in creating this panel chart is to plot all six series (primary and secondary versions of the original three series).
One by one, format the last three series to move them to the secondary axis. Then rescale the two axes as shown above, to allocate the primary and secondary to separate panels.
Format the secondary series so they have the same fill colors as the corresponding primary series.
Clean up the axis labels using custom number formats: [>=0]0,,"M";;; for the primary axis and [<8000000]0,,"M";;; for the secondary axis. Remove the redundant legend entries: click once on the legend, then again on the duplicate label, and press Delete.
As I mentioned, use of gradient fills is usually useless or even harmful formatting. However, I’ve formatted the truncated bars in the bottom panel of this chart to (try to) indicate that they extend higher than their neighbors, into the clouds. Here are the settings I used. First I selected just the one data point (bar) by single clicking on it twice. The first click selects the whole series, the second selects just the one point. I chose a gradient direction that puts the light end of the bar at the top. I moved the middle gradient stop to 80%, so the gradient is confined to the very top of the bar. I set the bottom and middle gradient colors to the fill color of the series, and I set the top gradient color to white.
Here is the finished panel chart, repeated here for your viewing pleasure.
Related Posts:
- US Employment Slump Chart – How To
- Marimekko Replacement – 2 by 2 Panel
- Chart Busters – Compare Employee Sales
- How to Build a 2×2 Panel Chart
- How to Build a Simple Panel Chart
- Challenge – Show Market Share Changes
Posted: Friday, November 18th, 2011 under Chart Axes.
Comments: 39
Comments
Comment from Naomi B. Robbins
Time: Friday, November 18, 2011, 11:46 am
I read the title, saw the top figure and thought to myself, “Finally I’ve found an area where I disagree with Jon.” Then I read, “You won’t find it here.” Another great post!
Comment from Patrick M
Time: Friday, November 18, 2011, 12:11 pm
Jon,
Brilliant, as usual. I have fended off requests before for a broken y axis, but I never thought about panel charts as an alternative. (As for logarithmic scales for most audiences: don’t even go there!)
Patrick
Comment from Jon Peltier
Time: Friday, November 18, 2011, 3:02 pm
Naomi -
I once had a tutorial that showed how to break an axis, but it was problematic. Not only was it a source of data distortion, it was also a technique that people had an inordinate amount of trouble with. Finally I decided it was time to officially retire that page.
Comment from Bob
Time: Friday, November 18, 2011, 6:33 pm
Hi Jon,
I recently tried using your older “broken axis” method for a report. It was complicated and the results were not satisfactory.
My only observation with this method is the tick marks. I would want to make sure the steps on the right and left axis line up properly.
Cheers,
Bob
Comment from Rob
Time: Saturday, November 19, 2011, 7:20 am
Excellent stuff. Really useful. Thanks!
Comment from Wal
Time: Sunday, November 20, 2011, 2:02 am
It would be nice and help to include the data setup for plotting this as ell. Tricky to work out without some clues. Is it possible to send this?? Otherwise very helpful.
Comment from Jon Peltier
Time: Sunday, November 20, 2011, 9:12 am
Hi Wal -
The data is located in BrokenYData.csv.
Comment from Stefan
Time: Tuesday, November 22, 2011, 4:13 am
Hi Jon,
maybe we can use Andy Pope´s solution as an add?
Stefan
Comment from Jon Peltier
Time: Tuesday, November 22, 2011, 7:20 am
Stefan -
It is possible to create the broken axis effect in a chart, that’s not the point. Many of these effects seem like a great shortcut, a clever way to squeeze diverse data into a smaller space. The problem is that it is vary hard to overcome the erroneous judgement of values presented in cognitively unintuitive ways.
Broken axes, tertiary (and secondary) axes, dial gauges, faux 3D charts, and all manner of gratuitous graphical effects are all things that are possible, “even” in Excel charts. But just because something is possible does not mean it should be done.
Comment from Bat
Time: Thursday, November 24, 2011, 4:14 am
Hey could you please explain how to brake the y axis
Comment from Jon Peltier
Time: Thursday, November 24, 2011, 10:29 am
Bat -
Sorry, but I’m not going to teach people how to use a technique which is ineffective or counterproductive. Instead, try the panel chart approach described here.
Comment from James Mansell
Time: Friday, November 25, 2011, 6:45 am
Thanks for very helpful article, just working through I can’t work out what scale setting should be on the secondary axis? Can you let us know?
Thanks
James
Comment from Jon Peltier
Time: Friday, November 25, 2011, 8:54 am
James -
Good point. If my two scales are 0 to Y1 and 0 to Y2, I’ll set up the primary scale to -Y1 to +Y1, so the data falls in the top half of the chart, and the secondary scale from 0 to 2 Y2, so the data falls in the bottom half. I make both X axes the same, and make both cross at the automatic setting. The primary axis then bisects the chart and the secondary is at the bottom of the chart. I usually hide the labels and often the tick marks for the primary axis.
Comment from Joy
Time: Wednesday, November 30, 2011, 12:16 pm
NOOO…. @Jon, please bring back the broken axis page! It was so useful!
Comment from Jon Peltier
Time: Wednesday, November 30, 2011, 5:13 pm
Joy -
The technique is too complicated for most people, and the charts it makes are difficult to interpret correctly.
Comment from Ninad
Time: Thursday, December 1, 2011, 2:22 am
Hi Jon,
Can you please share the XL file for ease for novices like me.
Regards,
Comment from Joe
Time: Thursday, December 1, 2011, 8:22 pm
Hello Jon,
Can you please email me the old instructions for breaking the Y axis? I have an old slide that I made that way, and once a year I need to update it. My boss doesn’t like the panel chart version.
Thanks,
Joe
Comment from JJ
Time: Tuesday, December 6, 2011, 6:20 pm
Your method is no clearer, but nice try.
Comment from sixseven
Time: Thursday, December 8, 2011, 1:34 pm
Jon,
How do you truncate the data in the second series to <= to 7.5M? A simple method would be to copy the data to a different place in the spreadsheet and manually change the value, but I get the impression you did this through formatting?
Please help.
-sixseven
Comment from sixseven
Time: Thursday, December 8, 2011, 5:10 pm
Hi John,
Thanks for writing back. I just used this technique on an executive spend report, and they liked it.
So back to my initial question. Your instructions say:
“The secondary axis is scaled to show the smaller values. Note that the two very large values (>30M) have been truncated at a suitably small value (7.5M).”
How did you truncate the large values to 7.5M?
Thanks!
-67
Comment from Jon Peltier
Time: Thursday, December 8, 2011, 8:12 pm
“Truncate: To shorten by or as if by cutting off.” (thefreedictionary.com)
I plotted a smaller number, 7.5M instead of their complete value. This is done by copying the values into another range, changing the large numbers to 7.5, and plotting this range.
Comment from sixseven
Time: Friday, December 9, 2011, 1:38 pm
Hi Jon,
I’ll give you the benefit of that doubt that you didn’t intend for your message to be snarky. So, thanks for your reply and help!
I used another range too in my report. As you know, there can be many ways to accomplish the same task is excel. I was just wondering if you had a different approach when truncating the value. (formula, formating, etc…)
Thanks again!
-67
Comment from Jon Peltier
Time: Friday, December 9, 2011, 2:16 pm
I just manually changed the values. If I were building a reusable approach, I’d probably write some formulas or code to do this.
Comment from AJ
Time: Wednesday, December 14, 2011, 11:08 am
Hi Jon,
Your explanation looks very good and I totally agree with you for a bar chart application that you shouldn’t split the axis as it distorts the image. However for the problem I currently have, that isn’t a problem and I still need to split the axis.
I have two measurements that were taken over two system configurations and hence at two different times; between 10am and 11am for the first and between 6pm and 7pm for the second. Due to the type of measurement it is, I need to quote the times correctly (And so can’t just move one forward by modifying the time data). The time inbetween the two measurements has no relevance and the second measurement could have been taken directly after the first but it wasn’t. During the measurement itself, time is relevent. Therefore if I was to split the graph it would not be distorted by the way you suggest for bar charts. I don’t think the alternative you suggest is relevent for a time domain plot.
Do you know any other alternatives to splitting the axis in this case? Can you provide advice or links to your previous page showing how to split the axis?
Cheers,
AJ
Comment from Jon Peltier
Time: Wednesday, December 14, 2011, 11:51 am
AJ -
How many time points in all? Are they evenly spaced within the hour periods? Could you represent the time as elapsed time?
Can you plot one primary and one secondary, with time across top and bottom axes? You could either overlap the data (10am to 11am and 6pm to 7pm), or offset so each is in its part of the chart (10am to 12noon and 5pm to 7pm).
Comment from Bat
Time: Wednesday, December 21, 2011, 9:45 pm
Please teach us how to break Y axis according to the first picture.
Comment from Jon Peltier
Time: Thursday, December 22, 2011, 6:58 am
Quite a few requests for a tutorial on broken axes in charts. But, no, that technique is a bad one, and I will not do anything to perpetuate it.
Comment from AJ
Time: Wednesday, January 4, 2012, 4:31 am
Hi Jon,
Thanks for your advice. I had thought of that although was trying to avoid it. It does look reasonable although I still feel that for my application the split axis would be better. The reason I say this is because with the primary and secondary axis, you have to work out which axis is relevant to each plot in the graph rather than just look at it and it is obvious (as in the split axis method).
By the posts you’ve been receiving about the split axis and you’re replies, may I suggest you change the title of the page? I think you’re getting so many posts requesting the tutorial on split axis’ because that is what the page is called and that is what those coming here are wanting to find out (for better or for worse than other methods).
Happy New Year!
AJ
Comment from Rob
Time: Thursday, January 5, 2012, 2:18 pm
I need to do a broken y-axis chart with a line chart, as I am comparing returns over time in various market indexes. Is there an easy way to do this?
Comment from Jon Peltier
Time: Thursday, January 5, 2012, 2:25 pm
Rob -
There’s no easy way to split an axis in Excel, and I can think of no situation when it is reasonable to do so. That’s why I have permanently retired the old tutorial.
Comment from Rob
Time: Thursday, January 5, 2012, 2:30 pm
Thanks for the quick reply, I agree paneling is a better implementation for Bar Charts. I am using a line chart that compares a few stocks against each other, over time and a line chart allows us to easily see the change and the change relative to the other stocks, however seeing as some stock prices are a lot lower than other (some in low hundreds, others in high thousands) there is a huge amount of white space in the chart. I need them to maintain the scale against each other so log axis wont work, an paneling seems to make more sense for comparing magnitudes rather than percentage changes.
Thoughts?
Comment from Jon Peltier
Time: Thursday, January 5, 2012, 3:12 pm
Rob -
Does it make sense to normalize the data, so every series is 100 at a given time? Then you could be comparing the percentage change.
Comment from Rob
Time: Thursday, January 5, 2012, 3:15 pm
That is something that was discussed, thank you for your help, I think I have found a solution by moving the smallest data set to the secondary axis. If I keep the differential from min to max axis values the same for both I can position them near each other without losing the ability to see them relative one another. If I hide the smaller values on the primary axis and larger values on the secondary axis it makes it easy to see them closer together.
Thank you for all your help!
Comment from Lou Dean
Time: Wednesday, January 11, 2012, 7:08 am
*sobs*
Please can we have the instructions back?
I know that you don’t want to perpetuate a bad chart but I loved the split axis chart and it always worked for me.
I haven’t had to use it for a while and the panel chart just doesn’t cut the mustard!
*wanders off to find more tissues*
Comment from Partha Banerjee
Time: Wednesday, January 11, 2012, 5:34 pm
Hi Jon,
My daughter wants to use your concept for one of her project demo at Fulton County Tech Fair, GA. She is at her middle school. While I have seen your license agreement and understand that she can, still I will appreciate if you send your consent in email response.
Thanks in advance.
Comment from Marcin K
Time: Tuesday, January 17, 2012, 4:54 am
Hi Jon,
My problem is common with Robs, I`ve got a line chart and i have to break Y axis, unfortunately it is impossible to use other way than break the axis , cause this is one of the points of my exam :(
Could U provide any tutorial for line chart ?
I`ll be greatful … nobody knows how to do it :( and the column chart tutorial is not valid for line type :(
Comment from Naomi B. Robbins
Time: Tuesday, January 17, 2012, 7:30 am
Marcin,
One possibility is to use a full scale break. By a full scale break I mean two separate panels next to each other: one with the scale before the break and one with the scale after the break. This is more forceful than the two little parallel lines often used that are difficult to notice. I’ll add a figure as an example when time permits.
Comment from Johannes D
Time: Tuesday, January 31, 2012, 11:04 am
And another satisfied reader. I came for the broken y-axis and left as a convert panel diagram user. It’s still some handwork for the gradient, but much easier to maintain than an ugly axis hack.
Comment from Jon Peltier
Time: Tuesday, January 31, 2012, 1:11 pm
Johannes -
I’m glad some people get it. (You’d be surprised at the clamor to reinstate the broken axis protocol, which is not going to happen.)
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.