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.
Here is the data for the chart. Columns E, F, and G have the same data as columns B, C, and D, except the two very large values (>30 million) have been replaced by cut-off values of 7,500,000 (shaded cells).
The first step is to plot all of the data in one chart. By default, all series are plotted on the primary axis.
The second step is to move the three extra series to the secondary axis. They block the primary axis data…
… but if I format the secondary axis series with outlines and no fills, you can see the primary axis data.
Back to solid fill colors. I have rescaled the vertical axes. The primary (left) axis now has a minimum of -40 million and a maximum of +40 million; the secondary (right) axis now has a minimum of 0 and a maximum of 16 million.
Add the secondary horizontal axis. Excel by default puts it at the top of the chart, and the bars hang from the axis down to the values they represent. Pretty strange, but we’ll fix that in a moment.
Format the secondary vertical axis (right of chart), and change the Crosses At setting to Automatic. This makes the added axis cross at zero, at the bottom of the chart.
(The primary horizontal axis also crosses at zero, but that’s in the middle of the chart, since the primary vertical axis scale goes from negative to positive.)
Now we need to apply custom number formats to the vertical axes.
The primary (left) axis gets a format of 0,,"M"; (zero, comma, comma, and capital M within double quotes). Each comma knocks a set of three zeros off the displayed value, making for example 1,000,000 appear as 1. The M will be shown after the number of millions. The semicolon indicates that this format is for positive values, and nothing after the semicolon indicates that negative values are not to be shown. Since no special format is indicated for zero (which would be after a second semicolon), it is shown with the same format as a positive number.
The secondary (right) axis gets the trickier format of [<8000000]0,,"M"; (less than eight million enclosed in square brackets, zero, comma, comma, and capital M within double quotes). The first format in the string is normally for positive numbers, but square brackets indicate a non-default condition for the first string. This means that any values less than 8 million will appear as the number of millions folloewd by capital M. The semicolon with nothing following means that any other numbers will not be displayed.
Now I’ve cleaned up a bit. I’ve used a medium gray line for the plot area border, and for both horizontal axis lines. I’ve also set the labels of the primary horizontal axis (center of the chart) to No Labels, because they are redundant and clutter up the chart. The primary and secondary axis scales conveniently have the right spacing so that the primary horizontal gridlines work for the secondary axis as well.
Now I’ve applied the same fill colors to the secondary axis columns as are used for the primary axis columns.
Finally I’ve formatted the two large values separately. To format just one point in a series, click once to select the series, then click again to select the particular point (column) to format.
I used a gradient that had white fill at 0%, and column’s regular fill color at 15% and at 100%. This gradient makes the bars extend upward, and fade as they reached into the clouds.
Finally I deleted the duplicate legend entries. To delete an unwanted legend entry, click once to select the legend, then click again to select the particular legend entry, then press the Delete key.
This is the finished panel chart. The top panel shows that the two outlying values are drastically larger than the others, while the bottom panel allows comparison between the smaller values.
The Final Word
I know everybody’s case is special, and everybody knows better than I do about why using improper techniques is correct in their particular situation. Your boss needs it this way, or it’s a specialized scientific chart, or you don’t see how anybody could be confused, or it’s really really important. However, I am under no obligation to share something that I do not want to share. I do not even have the old tutorial, so I cannot send it to you, nor will I recreate a new version of the tutorial.
Naomi B. Robbins says
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!
Patrick M says
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
Jon Peltier says
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.
Bob says
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
Rob says
Excellent stuff. Really useful. Thanks!
Wal says
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.
Jon Peltier says
Hi Wal –
The data is located in BrokenYData.csv.
Stefan says
Hi Jon,
maybe we can use Andy Pope´s solution as an add?
Stefan
Jon Peltier says
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.
Bat says
Hey could you please explain how to brake the y axis
Jon Peltier says
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.
James Mansell says
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
Jon Peltier says
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.
Joy says
NOOO…. @Jon, please bring back the broken axis page! It was so useful!
Jon Peltier says
Joy –
The technique is too complicated for most people, and the charts it makes are difficult to interpret correctly.
Ninad says
Hi Jon,
Can you please share the XL file for ease for novices like me.
Regards,
Joe says
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
JJ says
Your method is no clearer, but nice try.
sixseven says
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
sixseven says
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
Jon Peltier says
“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.
sixseven says
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
Jon Peltier says
I just manually changed the values. If I were building a reusable approach, I’d probably write some formulas or code to do this.
AJ says
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
Jon Peltier says
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).
Bat says
Please teach us how to break Y axis according to the first picture.
Jon Peltier says
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.
AJ says
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
Rob says
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?
Jon Peltier says
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.
Rob says
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?
Jon Peltier says
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.
Rob says
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!
Lou Dean says
*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*
Partha Banerjee says
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.
Marcin K says
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 :(
Naomi B. Robbins says
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.
Johannes D says
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.
Jon Peltier says
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.)
Adrian says
Dear Jon,
it is somewhat tedious to remove a tutorial that has been referenced by a bunch of forums and sites in the internet. This is a kind of not confortable situation since I lost my time to look for a similar tutorial.
To be very honest, it does not matter whether you think its not an appropriate way to communicate data using broken axis, it is the responsibility of the author of a paper, report, book, …
I think the removal of your tutorial gives me reason to return to the scientific plotting tool that is very expensive but provides the broken axis tool as built in option.
Cheers,
Adrian
Patrick says
Adrian,
With respect, nonsense. This was Jon’s content. He owns it. If he no longer wishes to make it available, that is his choice alone. Unless you are a paying customer or employer, neither he nor any other blogger has any responsibility toward you with regard to making content available.
Patrick
Adrian says
Patrick,
I fully agree with you. It is the blogger’s choice making available or not content he authored.
I just wanted to say that it is tedious if you are directed by several forums and Internet sites to a tutorial, which is not available anymore, full stop.
Best wishes,
Adrian
Jon Peltier says
Adrian –
“it is somewhat tedious to remove a tutorial…”
It was not tedious at all to remove the tutorial in question. It was more tedious to set up a redirect, so visitors at least learn that there is no longer such a page and also read the rationale for its removal.
I can’t believe it was more tedious to find similar tutorials than to land on this page. Most pages that cited my old tutorial also cited others, usually several others, and I’m sure the search engines listed the others as well.
Was it tedious to read the rationale against using this technique? Or was it more tedious to be faced with the thought that the technique you wanted to use is frowned upon by visualization experts?
“it does not matter whether you think its not an appropriate way to communicate data”
On the contrary, it matters a great deal (at least to me) that what I write about are in fact what I consider appropriate approaches to communicate data. It also matters that I explain why certain techniques are or are not appropriate. If, after reading this explanation and considering alternative means to communicate data, a reader decides to use a technique that I consider inappropriate, that is what “does not matter” to me.
Joana says
Hi,
Thank you so much for this tutorial, I managed to get my data into a panel graph and it looks good, except for one think which I don’t manage to do: eliminating the useless parts of the primary and secondary axes. I tried pasting the code you gave ( [>=0]0,,”M”;;; for the primary axis and [<8000000]0,,"M";;; for the secondary axis ) but it doesn't work. Could this have to do with using a different Excel version, in this case Excel for Mac? It could also be that I'm just pasting it wrong, or that the code you gave is suited to your values but not mine, could you explain better what each part of the code means so I can adapt it to my situation?
Thanks a lot!
Joana
Jon Peltier says
Joana –
I wrote a tutorial about Excel’s number formats some time ago: Number Formats in Excel.
A brief note:
Number formats can have up to four sections, separated with semicolons. Generally the four segments refer to formats for positive;negative;zero;text. But the first two of these can have simple conditions included in square brackets.
[>=0] means use this format if the value is zero or greater
[<8000000] means use this format if the number is less than 8000000
My numbers were in millions, and rather than show, for example, all digits of 1000000, I used 0,,"M". This deducts three zeros for each comma, making 1000000 look like 1. The "M" appends M, so finally, 1000000 looks like 1M.
The exact formats you will use depends on your own ranges of numbers.
Neith says
Thank you for taking the time to share your experience. The problem I am struggling with is that I need to make line graphs with a small range of high values (from 5,000 to 7,000). I use error bars to help my audience determine whether increases or differences are significant, but it’s very difficult to see the data if I am comparing two lines because they are much closer to eachother than they are to 0. Is it appropriate to “zoom in” on the range that my data is in, rather than showing the whole axis from 0 to 7000? A difference of a 500 units is a big difference, but if I show the whole scale from 0 to 7000 it’s difficult to see those differences. How can I make my graphs easy to read, but still accurate?
Jon Peltier says
Neith –
While a bar chart has the requirement (well, it often isn’t followed, to the detriment of the reader) that the value axis scale has to include zero, a line chart is not bound to zero. You could scale your axis from 5000 to 7000.
Of course, while this doesn’t distort the values themselves, it exaggerates the variability within this range.
Neith says
Thank you!
Pierre says
Hi,
Thanks for the helpfull tips. I am however stil looking for a way to break the Y-axis.
The reason is that Im plotting 2 sets of data on a date graph. The one set always vary between 2 and 5 on a specific day and the second between 30 and 60. Im using 2 x/y scatter line series on the same graph.
The requirement is to show these data points on the same graph but if i do it without some kind of break the smaller set becomes very cramped and unreadable.
Because the actual values are not related and Im more interested in the gradients it doesnt matter that it might visually be misleading in the sense that it appears to be values of similar magnitude.
If there is a better way please advise.
Thank you
Pierre
PS I have tried a log grapgh with some success but it isnt achieving what I want just yet.
Naomi B. Robbins says
Pierre,
Try a complete scale break. It will look like the panel charts in this post; however, you don’t need to use the gradient colors. The panel chart that Jon created serves as a model for what you should do. Your dates go where his cities are. The lower panel covers the 2 to 5 range and the upper panel covers the 30 to 60. Use your line graph where Jon has bars.
Pete says
Please change the title of this article, you ginormous tosser. You are luring people here under false pretenses. I am fully aware of the issues surrounding breaking an axis and I don’t care about your opinions on it or your inelegant alternatives.
Yours truly
Pete
Heather says
Jon, I appreciate your rationale for not breaking the y axis. However, I want to break the x axis: I have some plant growth data, log toxicant concentration on the x axis, with the control at x = 1 and the following points (log scale) at 3.2, 3.4, 3.6, 3.8, 4.0. The control data is included by convention, but the main interest is a comparison of 4 different forms of toxicant at the 5 concentrations mentioned above. By having that long empty space between the control (x = 1) and the first toxicant response (x = 3.2) the 5 concentrations are all squashed together and the differences are lost. Do you have any suggestions for how that could be handled in excel? Thanks in advance.
Heather
Jon Peltier says
Heather –
Could you scale the axes so X = concentration/control and Y = response/control response? Then you wouldn’t need to plot the control data point. Of course, it depends on the magnitudes of the responses as well, but sometimes things done “by convention” are not always best practices.
Judy says
I have a question. At school we are making posters of data charts to display in the classroom. I’ve increased the scaling to 200% so the chart will print on four pages. The problem is that some of the vertical axis values do not print. (i.e. On one of the pages the values all print and on another page of the enlarged poster, only a few of the values print, skipping some of the values on the axis. I’ve tried as much as I know to correct the problem. Have not been successful. Is there something I can do to correct this? Thank you for your time.
Marcela says
Thanks for the tutorial on panel charts. It helped me learn something new today (: Hoping the supervisor thinks it is appropriate for the readers of our report.
Javed says
Hi Jon,
I am having a problem to draw broken graph. I want to show the smaller values clearly the values for Y axis are 0.4 to 300. This link shows a broken line graph but didn’t say anything about how to draw the broken line graph in excel.
Liezl says
Dear Jon
I’m doing pharmacokinetic studies. Which means that I need to draw a concentration-time graph for the drug concentrations in the blood.
My time values range from 0 to 168 hours which really makes it difficult I want to draw the time as a broken x-axis, the panel method will not work for this type of chart unless I can somehow connect the by a line as we also work with the area under the curve to determine the bioavailability any ideas? Pleaaase help!
It would be greatly appreciated
Kind regards,
Liezl
Jon Peltier says
Liezl –
If the panel method interferes with calculations of area under the curve, wouldn’t the broken axis method as well?
Shiuan says
Dear Jon,
Thank you for such a good and smart method to display the chart. However, I have met a problem when I was generating the panel chart. On the step of formatting the axis, I was not able to delete part of the axis labels/entries as shown in the instruction above. I was not able to select only one scale label and if I press delete, the entire primary or secondary y axis just gone. Could you help? Thank you!
Jon Peltier says
Shiuan –
You cannot delete individual axis labels. You need to use custom number formatting to hide the labels based on conditions within square brackets in the formats.
Shiuan says
I got it! Thank you for helping!
Mara says
Hi Jon, I have a similar issue to Neith (feb. 15th). I have a stacked bar chart with one very large category and two much smaller ones (the first is generally 0 to about 97 percent and the other two make up the remaining 3 percent). I have scaled the Y axis to go from 93 – 100 percent to highlight the relationship of the two smaller categories, but I am worried that readers won’t notice the scale. Do you have a suggestion for making this more obvious?
thanks,
Mara
Jon Peltier says
Mara –
No matter how obvious the axis is, even if the reader knows it is there, the fact that the axis does not include zero means that you are hiding part of the larger bar, and the reader will not recognize the relative sizes of the bars. You should read Bar Chart Value Axis Scale Must Include Zero.
What about a clustered bar chart? You will have a very long bar and a couple very short bars. Or how about a clustered bar chart with just the two shorter bars? The longer one would be 100% minus the total of these two, so maybe you could omit it, but at least you can compare the smaller values.
Matt says
Jon,
Frankly, I don’t really care about your opinion as to why breaking the y axis is a good idea or not. I see graphs of this nature in my scientific journals all the time. Are you chief editor of Nature?
Now I need to make one, I find your site in a google search, your first graph on this page is a freaking paragon of the type of graph I’m trying to make, and you don’t post how you made it.
Very frustrating.
Jon Peltier says
Matt (and others) –
It’s my blog, so I can post about things I want to post about. I do not want to post about how to break an axis, because I think it interferes with the message in a chart. This is not just my opinion, either.
Also, just because things are presented in widely distributed periodicals does not make them good practices, only common practices. Or do you think USA Today sets the standard for presentation graphics?
Finally, I wish I had the control over Google’s search results that you seem to think I have. Google has their algorithm, and a whole industry exists trying to reverse-engineer the algorithm, but I don’t have time for this effort.
Alex says
Hi Jon,
I used your previous method (axis break) in all my previous graphs and I have one more to finish. It worked really well for me and I like to use the same method so I am consistant. If not I have to re-do everything from the beginig. Could you please please post it again so people who like that method can use it again.
Please!!!!!!!!!At least for few days.
Alex
Alex says
Hi Jon,
This is Alex again. After reading all your comments I know that you won’t publish the previous method again. I am trying with your new method but I am really not getting it. Speciialy when you say
“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.”
Could you please explain it further? I could do the first step.
Thanks!
RJ says
Jon, thank you for your method. I’m having trouble figuring out how to “erase” half of the upper and half of the lower values on the primary and secondary vertical axes. You mention something about cleaning up the axis labels but I’m not really sure I understand that part. Could you clarify how to do this?
For example, how do you make the negative values on one axis and the useless 10-16M values on the right axis disappear?
Thank you!
RJ says
Jon, I see you responded to other users about the same problem. Thank you!
Patrick O says
John,
I have a big problem and since you’ve been so helpful I was hoping you could give me a hand! On Microsoft Excel 2007, I have added a 2nd y-axis. I want a few data points to share the data for the x-axis but display different y-axis data. When I add a second y-axis these few data points get thrown into a spot where they don’t display the x-axis data any longer! I have checked and messed around with it and all the data is correct. Can you give me any suggestions please
Jon Peltier says
Patrick –
Do the primary and secondary series all use the same X values (category labels)? If not, you may see this kind of funny business. Is there a secondary X axis? What are the chart types of the series?
Frank says
Hi Jon,
Thanks a lot for your post! it took me a while bu finally i could work it out and it looks good!
greetings from Switzerland,
Frank
Bob says
So instead of asking the audience to understand a gap in a single axis (one level of abstraction), you’re now asking them to 1) read double the amount of data, 2) understand the relationship between the top and bottom plots, and 3) understand the culture-sensitive abstraction of ‘disappearing into the clouds.’
And what do you plan on doing when the Y-axis is negative? Fade to blue gradient to look like the bars are sinking into the ocean?
Jon Peltier says
Bob –
I’m sorry you didn’t find the broken axis tutorial here that you expected. The single abstraction of the broken axis is very damaging to comprehension. The first two other points you’ve mentioned can easily be cleared up with an explanatory sentence in the figure caption, and the disappearing into the clouds thing, well, the faded tops of the tall bars look different than the flat-topped, constant-colored bars, so most observant people would know something is different, regardless of their familiarity with weather phenomena.
Bob says
Thanks for assuming that I was here for a tutorial on a broken axis plot. I actually found this page for completely unrelated reasons and was simply bemused by the comments.
For the record, I showed your two plots to the research group I’m in and the one next door (N=9; 2 undergaduates, 1 bachelors researcher, 1 MD/PhD student, 1 Masters student, 1 Masters researcher, and 3 PhDs each of very different generations). Only the bachelor’s researcher liked the panel plot and every other one (unprompted and without knowing the responses from the others) pointed out that it was very confusing to have to continuously jump up and down between the two panels, and that the fading gradient only made it look like the printer had screwed up, not that those two bars were somehow marked as different. So yes, they were very observant and immediately saw the gradient, and decided that it was just a lousy graphics job from the author. At that point, I then explained that it meant the bars ‘faded’ into the top panel, and they all (except for the one who liked the panel plot better) then stated that that was an even lousier explanation.
So again, 1) you now have to explain twice the data to the audience, and in a live talk that extra time is better spent presenting actual interesting results rather than going over the same thing twice, 2) to point out any trends, you have to jump between multiple panels and force the viewer’s eye to forget what they were previously seeing when they jump to the other abstraction level, and 3) spend time explaining that the chosen color scheme represents a transition between the same data presented twice.
Jon Peltier says
Bob –
Didn’t mean to get snarky there. This post seems to have attracted lots of displeasure.
Maybe the bar chart wasn’t appropriate for the panel chart. I generally would have used a line chart or dot plot. It’s messy with multiple series…
In any case, if you want to look at trends, you may need another chart type, and just deal with the range of values. Your researchers could probably handle logarithmic axes, which scare many audiences. The panel chart can remove outliers, enabling you to expand the rest of the data, and see trends in the remaining points.
The dot plot panel below shows the same data as the bar chart above. It’s nonsensical to talk about trends with categorical labels (the cities), but if these were numerical, you could see the trend in the left panel clearly with the outlier removed. The right panel shows all the data, a little too compressed to make out a slope in the small values. Maybe this better suits your sensibilities.
Le says
Hi Jon,
Great tutorial. The only thing I’m stuck on is:
“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).”
Could you explain how you truncated the value? Was this in the data?
Jon Peltier says
Le –
I actually used two sets of data. I had the full data for the upper panel, and for the lower panel I had data that showed all values but only up to the truncation value.
graham baker says
Thanks for this method, very useful. I did however struggle to get the labels on the x-axis down to the bottom. If I selected the min value of the primary axis for the intercept then the bars in the top primary chart seem to start at the min value of the primary and overwrite the secondary plot. Could you explain how you got he labels to the bottom of the primary axis – thanks Graham
Carolyn says
There are definitely situations where a broken y-axis is useful, informative, and not distorting the data or interpretation. I find it interesting that you seem to think you know all situations, Jon, such that you with hold technical fixes on conceptual grounds – without knowing all the concepts.
Jon Peltier says
Carolyn –
I don’t know all situations, but I have yet to see a situation in which a broken axis is guaranteed not to be misleading or misunderstood.
I am choosing not to write up a protocol mostly on conceptual grounds, since the approach is not a good practice and I don’t want to spread bad practices. I am also not writing it up because the protocol is very complicated and my old tutorial resulted in too many emails from readers who had problems following it.
Sirreal says
Panel charts are o.k….in some occasions. But I need broken y axis for my presentation, no room for panel chart. what now?
Jon Peltier says
Best practices are only for when they’re convenient?
Bob says
Jon,
Stand your ground. I like this solution with the panels very much.
Bob
lokesh garg says
can anybody tell me how to draw broken y axis chart???????
Kevin says
I came here seeking help on how to create a broken axis. But was instead given a lecture on why what I needed to do was wrong. As a 20 year veteran in the sciences, I would humbly suggest that just because you think it’s wrong doesn’t make it so. Guess I’ll just consider the last 15 minutes a portion of my life wasted.
John says
Pretty frustrated that you’ve removed the tutorial on broken axis. I’m a grown up. I’d rather make my own decision about whether or not its an appropriate way to present data.
Harrison Rose says
Jon,
Your tutorials are awesome, and they have been very useful during my internship at a corporation which does not provide its employees with MATLAB, which is what I am used to using.
As much as I understand your frustration with broken-axis charts, and their propensity to misrepresent data, I would argue that there are certainly some circumstances in which they are necessary. For example when trying to duplicate this:
http://www.graphpad.com/faq/viewfaq.cfm?faq=165
in Excel (which I is what I am trying to do at the moment), or really any time in which column or bar charts are inappropriate.
I believe that it is never the role of an educator to withold information. It is not hard to imagine what an unfortunate turn it would be if all knowledgeable persons behaved in this manner. Instead, I believe that it is important to educate people on WHY one procedure GENERALLY should not be utilized, rather than assuming that because you cannot fathom a case in which broken axes are appropriate, such a case does not exist. Leave the choice up to the people (or at the very least, please email me a copy of your old tutorial, if it still exists somewhere)!
Thanks,
H
Dom says
Excellent solution Jon!
A much better way to get the information on the first look than any form of broken axis.
And that’s all that counts.
Terry says
Hi Jon,
I have two sets of data to graph for every day of the month. The data points range from 1-4000. A majority of the points are from 1-100. The x-axis contains the day of the month and the y-axis contains the two data points. Both of the points are plotted on the same day because they are collected from the same sample and we need to see how they correspond. Some of these graphs may have 2 to 4 samples on a given day, others may only have one sample every other day. On the days with multiple samples, each sample must be plotted individually.
I have been using the logarithmic scale and I agree, it is not the best method to display the wide range of data. To keep the graph readable and I was willing to try the panel graph, but I quickly discovered I would have to format for every single day of the year! It would be a challenge if I only had one of these graphs to maintain, but I have close to 40!
Any recommendations would be appreciated.
Thank You,
Terry
Jon Peltier says
Terry –
“I would have to format for every single day of the year”
I don’t understand what you mean. What would you have to format?
R says
way to have a misleading title
Daniel says
Hi Jon,
Is there a way to adapt this panel approach for the X axis?
I have an interesting situation. I am plotting expected new product revenue vs. time. Unfortunately, there quite a few new products that lack launch dates. For the products that have launch dates, I want the X axis to be year 2012 through 2025. For the products that do not have launch dates, I want the X axis to be 1 through 5.
Part of the point of this graph is to show executives how much revenue they cannot forecast beacuse the project management teams have failed to come up with a planned launch date.
FYI, I love your site and thanks for all the help you have provided over the years.
Jon Peltier says
Daniel –
You can make the panel chart in either orientation.
Here’s a complicated example with side-by-side panels:
https://peltiertech.com/Excel/ChartsHowTo/PanelChart1.html
This one’s less complex:
https://peltiertech.com/how-to-build-a-simple-panel-chart/
Here are a couple 2×2 panel charts:
https://peltiertech.com/how-to-build-a-2×2-panel-chart/
https://peltiertech.com/marimekko-replacement-2-by-2-panel/
Anonymous says
Yea great and now I need a zoomed in section of a scatter plot… so that won’t work. All the data is in a very small range and very far from y=0. Wouldn’t it be great if excel just provided all the options?
Jon Peltier says
If you need to zoom in on a section of an XY chart, couldn’t you change the min and max of the X and Y axes to expand the section of the chart that contains the values?
Raquel says
Hi Jon,
Thank you so much for posting this tutorial as well as the reasoning behind why a split axis is not the best way to go. I pitched your reasoning to my supervisor and obtained approval to show the panels instead.
I’ve been trying to replicate your split panel chart, but so far I am unable to figure out why in my version the city names shows in the middle of the chart instead of at the bottom. Can you help with this please?
EA says
The reason everyone keeps getting directed to Jon’s site here is because Jon Peltier himself here hit about every Excel forum and blog he could find between 2003 and 2008 posting the link to his website on how to break the axis in an Excel graph. He’s basically been the equivalent of a little kid bragging about his new basketball and asking us to play a game for 5 years, then getting upset when the game doesn’t go his way. He then tosses us all a football so we can finish our basketball game, gives us a lecture on why we’re all wrong, then takes his ball and goes home to pout.
I even found the origin of his breaking the axis tutorial.
http://www.mrexcel.com/forum/showthread.php?57231-Chart-Y-axis-break-in-scale
(Hmm…the data and labels provided in post #4 sure look familiar…)
I’m in complete agreement with Harrison Rose above, as I am also working with a line graph, and think it is inappropriate for “an educator” to withhold information that he appears to have been so eager to share with everyone in the past.
Jon Peltier says
Actually, I hit Mr Excel and one other site, the old Microsoft Excel Newsgroup. A zillion other forums, lacking original content, scooped the feeds from the newsgroup, so anything I may have posted to the newsgroup appeared on dozens of other pages.
I didn’t “get upset” when the game didn’t go my way. I changed my mind about the effectiveness of the technique in question, admitted my error, and explained my rationale. I removed the technique, introduced a replacement, and explained how to use it. It’s my prerogative, in fact, my obligation as author and editor-in-chief of this blog to keep it current and accurate.
I’m sure you can find an alternative source for the technique you need so badly. The page you searched for with the origin of my data has a link to another web site, if not to its particular example.
Thai says
Hi – Great post! [and sorry for digging this up] Would anyone mind explaining to me what the [>=0]0,,”M”;;; and [0 would not appear, but still not fully understand it for my next chance of using it. Cheers,
Jon Peltier says
You’ve gotten stuck on a custom number format. Basically, the number format has four elements, separated by semicolons. In the first element:
[>=0]
means do this for any number greater than or equal to zero, and
0,,”M”
means display the number with no decimals as millions (two commas means remove two sets of three digits, one for thousands, one for millions), then put an M after the number.
Lacey says
If your comparing an apple value (say, ranging from 1 to 10) and an orange value (ranging from 100-200) over time, breaking the x axis is a very useful tool. The large orange values make the changes in apple values indistinguishable.
Don’t be so arrogant. Just because you haven’t found a good use for it doesn’t mean that it is always, always, always inappropriate. Get over yourself.
Jon Peltier says
Lacey –
Are the relative values of the apples and oranges important? If so, breaking the axis prevents you from visually comparing these values. If not, break the axis all you want, or make a panel chart similar to that shown here, with one panel for apples and one for oranges.
Are the changes in values important? If so, try subtracting or dividing by the initial value of each series. This brings the data onto the same scale of change or percentage change.
Is it informative to use a scatter plot where apples and oranges are plotted on the X and Y axes?
It’s not so much that there are no good uses for charts with a broken axis. It’s that there are better ways to show the data, depending on what is important to show.
Patrick says
Lacey,
>>Get over yourself.
Have you forgotten that this is actually Jon’s web site? If he chooses to pull material from it, that is his prerogative. Indeed, it takes some courage to come out and say, “you know what, I was wrong, and here’s a better way.” You don’t have to like it, but how exactly did you get to be the judge of whether or not Jon’s decision was inappropriate?
Patrick
Nate says
Hi Jon,
I appreciate the panel chart and like it a lot better. I had come back to this post for a slightly different question: can you refresh my memory how you made the squiggly lines and filled the gap in between them with white space on the old broken axis chart. I was trying to split a picture in powerpoint with that technique and could only recall seeing it here, so I came back to the post to see that you’ve updated it.
Thanks,
Nate
balings says
Jon,
Is there any way to use a data table with a panel chart? That is, without the duplicate series in it.
It can be done by hand I presume, but I’m trying to automate generating panel chart in VBA.
Thanks!
Jon Peltier says
The data table in a chart is not particularly flexible. It shows all of the data in a chart, and only the data in the chart, and its formatting options are rudimentary.
Phil says
Very cool. I was able to follow the steps well enough to get what I needed, although I never did figure out how to get a horizontal line at zero for each axis (Excel 2007).
Brent says
I can follow along on using csv file but I cannot seem to the get the Horizontal (Category) Axis to move from the O line on the upper panel chart.
Thanks for your help in advance.
Anonymous says
Hilarious all around…
Anonymous says
Definitely was hoping to learn how to break the y-axis. A panel chart will not be appropriate because like others, I’m working with a line graph tracking driver age by category across time. Two age groups are in the 30% range while the rest are in the 0-10. If I want to look at trends, having the same scale for all removes all real ability to illustrate the trends in all but two groups. Also, I want everything on one graph. So in short, I just wasted five minutes reading a post that was not helpful.
Dan says
I almost passed my Bloody Mary through my nose @ 35,000 ft!
I’m on a long flight and working on “stuff”, Bloody Mary in hand (more has already passed my gullet). I started with how to “do a broken y-axis” search and you’ve convinced me (as in “convincingly convinced me”) to go with a panel chart instead; but prior to starting I thought I’d read the comments. Most were pretty much as expected until I got to “Bat” (Nov 24, 2011).
I’m hoping he was going for the wind-up, after several paragraphs of convincing logic against “break” charts, he asked for a “brake” chart. A diction-challenged engineer? Or a very skillful comedian? I am a ChE, but also work in “manglement” — unfortunately, as much as I would like to believe it was intended humor, I conclude yet again that more engineers need to spend time in the colleges of arts & letters.
p.s. — I don’t have a formal background in psychology, but after decades of trying to get “the public” to understand data, in one reading you have convinced me to use panel charts for representing dis-proportionate data. Well done.
Triumph says
Hello Jon,
This was really helpful, though it took me a while!..I got the scoop!..however I’ll like to know how effective this is with large data sets in which pivot charts are used, as you cannot easily truncate values on pivot.
Cheers,
Triumph.
Jon Peltier says
Check out Create Regular Charts From Pivot Tables. You may need to use an intermediate range, linked to the pivot data, to set up the data.
Echo says
Hi Jon,
I’m working on a chart built with four series of data. I think the broken axis is better for this situation since these four series have same unit, however one of them had very low values and the other three have very high values. And we want to compare the trend of changing.
Could you provide the broken y axis tutorial again？
Jon Peltier says
No, that tutorial is long gone, and I’m not recreating it.
Are you looking for absolute change in the values? Then plot (value – reference value), where reference value is the original value of each series, or the value at a certain reference point.
Are you looking for percent change? Then plot (value/reference value – 1).
Alfonso says
Hi Jon,
Your’ve generated an interesting follow-up to your theories and I’m quite puzzled by the whole thing. I hope you don’t mind me chipping in.
The only reason people visit this website and get annoyed is because Excel does not have a built-in method for easily breaking the Y axis…. and I wonder what the big deal is both for MS and for you? Why this insistence with doing and showing others how to do “the right thing”, even though people seem quite desperate to do something else? What religion is behind the dogma of bar graphs in MS Excel?
You provide no proof that your method is visually easier to understand, and it seems many don’t find it so. We are not born with a capacity to read bar graphs, we get taught to understand the relations between the visual elements. Given the number of people looking for a broken Y axis method, it must have been used widely in teaching them and they are likely to understand its shortcomings.
At any rate, it may be your right to withhold information, but with the title of your blog entry you’re misleading people. It’s like a vegetarian shop advertised as “The Delicious Beef Sausage”. Even if the owners kindly offered some healthy veggie surrogates for free, customers would still have a right to be annoyed at the misleading advertisement. It would seem like they are desperate for a little opportunity to preach their beliefs to others that have innocently come in search of something else.
So, well, those are my two cents…
Alfonso
Adrian M says
Jon,
First of all, I completely agree with your decision not to aid in the proliferation of unintuitive and misleading charts.
That said, I arrived here looking for a way to make a discontinuity in an axis more salient, so I am disappointed that this page does not (anymore) contain instructions on how to do that.
Please note that I was not looking for instructions on how to *create* the discontinuity—Excel already allows for that, as you already discussed with commenter “Neith” here:
”
Neith says: Wednesday, February 15, 2012 at 2:55 pm
[…] small range of high values (from 5,000 to 7,000). […] Is it appropriate to “zoom in” on the range that my data is in, rather than showing the whole axis from 0 to 7000? [..]
Jon Peltier says: Wednesday, February 15, 2012 at 4:48 pm
[…] While a bar chart has the requirement (well, it often isn’t followed, to the detriment of the reader) that the value axis scale has to include zero, a line chart is not bound to zero. You could scale your axis from 5000 to 7000. […]
”
In cases where people are doing as you advised to Neith, surely you would agree that adding some kind of indicator of the break would make the chart less misleading.
…and in case you think Excel has done wrong by letting Neith do what he wanted to do, and you still do not feel like helping people show axis breaks because it might encourage people to use ungrounded (to zero) line charts (and even then I would agree with you)…
Please note that there is, indeed, an application where a chart axis discontinuity is *unavoidable*, and doing one’s best to highlight that break is the only logical thing to be done about it. Commenter “Harrison Rose” already provided a link to such a case: http://www.graphpad.com/support/faqid/165/
For any chart with an axis on a logarithmic scale, it is inappropriate and misleading to show that axis reaching the origin. The discontinuity is there, that fact should be highlighted, and the best way to show it would be to have an actual break (white space) or the classic squiggle that most people were chasing when they ended up here.
So…
Would you *please* post the part of your previous tutorial where you show how to *depict* an axis discontinuity?
I would suggest showing that *without* posting the part about how to plot data on both sides of such a discontinuity would be the ‘right’ thing to do. :)
In the meantime, in my case I thought I could make do by just obscuring the bottom of my (vertical) axis with a empty text block of the same colour as the chart background, but then the problem is making room for that gap. In my specific case, I want the lowest value to be 1000, and I would like to change the axis minimum from 1000 to a lower number to make room for the gap (to force everything up a bit), but if I do that, then Excel wants to show that lower number. I will probably end up doing it anyway and covering up that number with a text box as well, but if you can help, I would appreciate it greatly.
— Adrian M
Jon Peltier says
Alfonso –
I will not help people do the wrong thing, however desperate they may be. Among common bad practices are axis abuse, including breaking axes, tertiary (and even secondary) axes, and improper axis scales. People don’t realize these practices are bad, because they see them all the time, and they themselves (and not their audiences) are familiar enough with the data that they are not relying on the chart to convey this data to themselves.
We infer the values encoded in markers by judging their positions along a scale. Breaking the axis violates the relationship between position and value. We infer the values encoded in bars by judging the lengths of the bars. Cutting out part of a bar, either at one end by not starting the axis at zero or in the middle by breaking the axis (and the bar), violates the relationship between length and value.
Much research has gone into the study of effective graphic displays. Cleveland and Bertin are just two names you could follow up on if you really are interested in improving your graphics, and Few does a fine job of compiling these studies into best practices (complete with bibliographies, so check out his sources).
Jon Peltier says
Perhaps a better representation of a data point for X=0 along a logarithmic scale would be a horizontal line at the value of Y where X is zero. The plotted curve would approach this limit asymptotically, the same way that it approaches the upper limit asymptotically. Using your logic, would you say that one should plot an upper limit at X=infinity, but put the marker at X=0.01, and break the axis there as well?
Adrian M says
Thanks for the reply, Jon. Unfortunately, I am not sure I get what you are suggesting.
By the way, after some more time scouring the internet last night and some experimentation, I finally did construct my graph the way I wanted it—complete with the tell-tale squiggle (well…half of one—a notch). Some kludging with a cover-up text box is required for logarithmic scale. There is a different way to skin the cat for a linear scale (as for commenter Neith’s use), but I am sure you would not want me to post that. ;) For a logarithmic scale axis, you force it to cross the other axis at one tenth of the bottom of the order of magnitude window when your lowest data come in (e.g. if your lowest datum is at 2 000 (in the window 1 000 to 10 000), you force the axis to cross at 100, and you also force the minimum axis value to be 100 (strange that it requires two steps)). Then you turn off the axis line, draw a new axis line with a notch, and cover the bottom value on the scale with a text box filled in with the chart background colour. You could add a ‘0’ label at the bottom of the axis (at the origin), but it’s tricky since just typing the ‘0’ into the cover-up text box gives you it in a different font than the axis labels use. I did not feel like hunting for the proper font, so I left the origin with no label on that axis. Both variables’ values at the origin are strongly implied to be zero in any case.
I am still interested in knowing what you you are talking out, though—especially your comment on approaching limits—so…
Just so we’re speaking the same language, here is more detail on the graph I am talking about:
— My vertical scale is logarithmic (and my horizontal scale is linear).
— I don’t have any data point with x = 0, but if I did, the construction of the chart would not change. If you were thinking of the x-axis being logarithmic, then OK…Let’s consider a datum with y = 0 on my chart (y-axis logarithmic). I do not have any such point, but yes, that really would change the chart.
If I were to try to plot a value of 0 on a logarithmic scale, then I would need to plot it below an axis break. …and given that the other values would be above the break, that would be misleading. I stand with you against doing that (even on a logarithmic axis), and I would even say that a value of 0 on a logarithmic scale plot would not make much sense anyway.
In the end, I just turned this:
1 000 000 | x
0 100 000 | x
0 010 000 |
0 001 000 |
0 000 100 |
0 000 010 |
0 000 001 |—————
into this:
1 000 000 | x
0 100 000 | x
0 000 000 \
0 000 000 /
0 000 000 |—————
(Ignore all the leading zeroes, including sets of all zeroes. They are just there to help the ASCII art, since I can’t switch to a monospace font.)
The x-axis crossing the y-axis at a y value of one is not a proper origin (0,0), so it should not be presented as such.
Do you agree with the change I made?
— Adrian M
Jon Peltier says
Adrian –
The example you gave had a logarithmic X axis and a 0 to 100% linear Y axis, and it had a very well-defined shape, with an asymptote to Y=0 at very small X and to Y=100% at very large X. That’s what I was working with. Any changes in the configuration of the data and chart would require a different (perhaps related) approach.
Adrian M says
OK, originally I wasn’t talking about Harrison Rose’s example, I was just pointing out that a good example had been linked to earlier in the comments. To talk about that example, though…
The upper end of the scale is fine the way it is, since it does not purport to show infinity.
The lower end of the scale should have an obvious break between the 10^(-10) and the origin, though, since otherwise a viewer might assume that the graph is showing x = 0 (and that if Datum 2 is twice as far away from the vertical axis as Datum 1, then the x-value of Datum 2 is somehow the same amount or factor more than that of Datum 1 as the x-value of Datum 1 is more than 0). …or, instead of having a break between the scale and the origin, the origin could be eliminated entirely. After finding your article on showing tick marks without an axis (https://peltiertech.com/axis-with-tick-marks-no-line/), that’s an option I will use some of the time as well: Just remove the *other* axis!
PS: That graph is very strange in showing a break between the 10^(-10) and 10^(-9), since those values really are beside each other on the same scale of the rest of the x-axis. That graph is also misleading in having the x-axis not cross the y-axis at y = 0, but instead at y = -15 or so.
June Lewis says
Very clever. Thanks.
Brian says
Thanks for this – a great alternative to the broken graph!!! Just what I was looking for :)
Marius Aurelian says
Like many of the commenters above i came here looking for a method to break the y-axis and like all of them i found something totally different and instead of a modern scientist i found a fundamentalist preacher. Because, you see, there are instances when a y-break is not only an option but rather a necessity. A small minority of researches in biodiversity (or pest management) studies use y-axis breaks for clarity and conciseness without distorting the real data. The keyword here is REAL. Before you start suggesting that transformations ought to be applied please do yourself a favour and read some (rather enlightening) scientific publications on why transformations are bad (i.e. they distort the data, lack clarity, do not take into account multiple zeros, and so on). One example is O’Hara & Kotze (2010). Working with real data is rather new-school ideology and i agree, it is very challenging, but we love challenges because natural complexity should be studied as it is. Let me give you one example: we are doing a comparison between the numbers of moths captured in an old-growth oak forest patch vs. an early succession one (as a result of a relatively recent forest fire). The early succession forest patch has a few oaks, but they are rather young and sparsely distributed. Now, all ten moth species studied are more abundant in the old growth forest patch (statistically significant). However, one particular species, which although follows the same trend, is incredibly much more abundant in both habitats than all the others combined, i.e. 778 and 392 individuals in the old growth and new growth, respectively. The numbers for the other nine species vary between 2 and 68 individuals. Survery was replicated six times. There is no relationship between the ten moth species. We could have chosen only one or twenty species. So, this is one the few cases when a y-break is fully justified, but i highly doubt a logical argument can persuade extremism. Frustrated, indeed i am, although i am much more saddened and dissapointed by your nanny-state like mentality, than outright frustration. Dark ages always begin when intellectuals withold information and turn into fundamentalist preachers who ignore exceptions, logical reasoning and loose sight that people can think for themselves and can discern right from wrong without outside intervention. Congrats for playing your part…
Crawf says
One simple solution to make this look more like a broken column chart is to simply change all data points on top table to be white and transparent – leaving only your columns with large values visible above – you may want to adjust your axis and number formats too for appearance and understandability
Jon Peltier says
Hide the small columns in the upper chart to lose any comparison between large and small values. Not a great idea.
Sonia says
Hi Jon,
Very useful tips. But I would like to know how to break ‘X-axis’ for e.g. if I have datasets of 3 different time (yr 2000, 2001 and then 2010), I want 2010 to be a further apart from 2001 on the X axis (i.e. apply a break in between them) and which would also make my line joining the data points of 2001 and 2010 break on the graph. How do I do it in excel? Could you please help me either by explaining me here or emailing me (sonal.career@gmail.com)?
Thanks in advance.
Jon Peltier says
Sounds like you don’t want to break the axis so much as have value-based spacing. An XY chart will give you this spacing. To avoid connecting the points for 2001 and 2010, insert a blank row in the data range. It has to be blank; any formulas that return “” or otherwise look blank are not blank, and you’ll get a connecting line, or worse, a data point at zero.
Fred says
“I wish I had the control over Google’s search results that you seem to think I have”
Here’s a suggestion on how to save people from coming to your site expecting to see how to break the Y-axis and ending up disappointed and angry when they don’t find it: change the title. You are aware that Google search results are based on the contents of your pages, aren’t you? Unbelievable.
david says
Took a bit to get the hang of it, but it works nice. I never liked the appearance of charts broken in Excel. Well done.
Jess says
Jon,
Although I agree that using a break between values on the y-axis can be misleading and problematic, I need to break my x-axis for completely different reasons. I have Sessions on the x-axis and break would show a break in data collection (e.g., for the holidays) even though the numbers would remain the same (e.g. a break between session 4 and 5). The break is later explained in the caption or article so there is no misinterpretation. In this particular case, not having a break would be misleading because the data is not continuous.
I will continue to look for a tutorial on how to do this. Thank you.
Jon Peltier says
Jess –
Another approach is to break the data, not the axis. If you break the axis, the reader loses the sense that a week or more is lost over the break, whereas breaking the data (formatting the particular line segment to use no line, or inserting a data without a value) clearly indicates the break in data collection without distorting the sense of elapsed time.
Dave says
This is going to sound stupid, but as much as your explanation makes sense, I have a situation where a broken chart is the only option. My professor insists that graphs must start from zero, but we have data that is clustered from 47-49. A bar that goes that high shows no differentiation. The only thing that makes sense is to break the axis so that I can go by ones between 45-50.
In cases where only one or two series are a different scale, this makes perfect sense. However when the data is clustered and your prof is acting idiotic, there has to be a way to break the graph. But I guess it’s the pencil-and-ruler method.
Jon Peltier says
Dave –
If you’re breaking the axis, then it isn’t really starting at zero, is it? It’s starting where you broke it. Give your prof two charts, a bar chart where the axis starts at zero, and the values are indistinguishable, and a line/scatter chart with an appropriately scaled axis along which you can easily read the different values. Perhaps you could include a link to an introductory visualization site, as well.
C.C. says
This was very helpful. Thank you!
Tanya says
Hi Jon,
I am wondering whether you will have a suggestion for me. When reporting the distributions of mean scores on the x-axis (0 to 1000 scale), the range of scores for various jurisdictions is very restricted. We have rescaled the chart (300 to 700) to better capture these fine differences in score distributions. But an additional piece of information we want to communicate in the chart is the range of scores of the full scale (all possible test scores), and the neatest way to do this is to break the x-axis (instead of making a note addressing this underneath the chart). The space limitation of the report would not allow us to include the panel chart, as there are too many jurisdictions to fit on a single page.
Any suggestions are welcome!
Tanya
Jon Peltier says
I’m not sure why breaking the axis is “neater” than a note stating that possible scores range from 0 to 1000. Perhaps the X axis title could read “Test Score (possible range 0 to 1000)”.
Dominic says
Has anybody found a tutorial where they teach how to break the axis? Tired of wasting my time scouring the internet for a tutorial that actually allows me to break the axis in a way where it visually looks good such as above. Definitely don’t agree with the author here, if anybody finds a tutorial it certainly would be a huge help for my paper!
Thanks.
Ryan says
I am a little lost. I don’t understand how you truncated the data above to about 7.5. Mine continue on to the 30+ million. I missed a key step?
Thanks,
Ryan
Jon Peltier says
Ryan –
I didn’t explicitly show it, my bad. I actually put that data in another range, and changed the large values to the smaller value and plotted those.
Mark says
The title of this blog post is inconsistent with your declared authority on what is “right”. It is misleading, I suspect intentionally so motivated by some perverse psychological reward. If you want to engender a debate, do so in a forum where you are not both prosecutor and judge. Consider, “using panel charts to display extended y-axis data”, or something similar. If you don’t want to change the title, why do you think it is appropriate?
excelbase says
Hi Jon -thanks for the tip. Its really useful but is there anyway for you to post a step by step tutorial or even a completed worksheet with the chart so I can follow through exactly how you went about getting the final output. Ive given it a try but I can’t seem to get to the final output. Thanks for your contribution.
Statstudent says
Jon,
It seems you have a well developed opinion on “breaks” in the graph, and especially don’t like to place “breaks” in the x axis. However, I am taking a statistics class and with some of our assignments (frequency histograms), the teacher wants a break in the x axis, if and only if, the data entry never reaches zero. For example, if the lowest price range of GPS devices goes only as low as $59, there is no need to include the amounts from 0-58.5. Our textbook routinely uses breaks in the x axis, albeit probably to conserve space on the pages. Regardless, it just doesn’t make sense to me why someone in your shoes would keep that type of knowledge hostage from people that want to learn.
Jon Peltier says
If there’s no need to show 0-58.5, why do you need a break in the axis that shows zero? Especially in a histogram.
To me it makes sense not to disseminate techniques that hinder comprehension and obfuscate data. I also know how to make a pretty nice speedometer type chart in Excel, but I’m not teaching that either.
Since such techniques are also very difficult, posting them leads to questions from people who haven’t followed complicated protocols, or who got stuck and want me to complete the task for them. This is a secondary reason for me not to post such procedures, since the confusion of data is more important.
Caroline says
Hi Jon – Sorry to harp on the broken axis thing, but just thought I’d throw my problem into the mix. I understand how breaking any axis can be misleading, and never do it … except maybe this one time?
I am working on few graphs representing responses to a survey given out from 2005 – 2013. My boss, however, lost the 2010 data. I am just looking for a neat way to show a “break” in the X-axis where 2010 should have been without having to insert a cheesy symbol/picture of a squiggly line. I don’t want my graphs to jump from 2009 to 2011 without an explanation (which I’ve included in text form), but I would also like the axis to show a squiggle to indicate the missing data.
If you can help, (via email, here, or by suggesting a different method), that would be lovely. Otherwise I understand, and I’ll just stick in a hand drawn squiggle
Thanks so much!!
Caroline
Jon Peltier says
Caroline –
You can show a break in the data many ways without having to break the axis.
– You could simply leave that year blank. There will be a gap in bar charts, a longer connecting line in line charts.
– For the axis, you could hide the missing label by leaving the corresponding cell blank if it’s a line or bar chart, or by using a custom number format like
[<2010]0;[>2010]0;;.You’ve explained the missing data in the text. No need to dwell on it in the chart. The gap in the data or axis labels indicate that there is missing data. An actual break in the axis does so as well, but if this is used to remove the gap between the 2009 and 2011 data, you risk having people misinterpret the data.
Kerryn says
Hi Jon,
with this panel chart business, would you be able to post a video tutorial on how to do it. I have a scatter plot consisting of data from 0-20, and then 100 to 120, or some info on how I would do this with a scatter plot?
Thankyou
NicholasRoose says
Jon,
Thank you very much for this post. I need to demonstrate graphically the variance in a simple set of numbers (total deposits by bank), and I can’t figure out how to apply your methodology. Will this work with only one data series? I have tried to follow your instructions but I get quite lost once you introduce the secondary axis. Specifically, I don’t understand this: “first step in creating this panel chart is to plot all six series (primary and secondary versions of the original series)” and when you say to “rescale the two axes as shown above, to allocate the primary and secondary to separate panels.” Can you explain this more? I saw others request if there’s a video tutorial available, but haven’t been able to find it.
I would be incredibly grateful for any help or guidance you can provide. Thank you very much,
Nick
Tony Woodhouse says
For the many people who do want to create a split y-axis chart in Excel see this example
Jon – I know I won’t persuade you, but my reason for wanting a broken y-axis chart was to show 4 data series in a line chart which represented the weight of four people on a diet. One person was significantly heavier than the other three.
The most useful insight we wanted to graphically represent was how well the four diets were going comparatively (e.g. the rate or loss of gradient of the line for each person, compared with the others). It seems to me that a broken y-axis is just fine for this application especially given that it works to mask the fact that I’m grossly overweight! :-) (e.g. the loss of accurate representation of the relative weight of each participant is actually a benefit)
I think my point is really that to say “broken y-axis is always bad” will never be true in all circumstances, even if you don’t agree with my specific example. Best wishes, Tony
Jon Peltier says
Tony –
You could approach this in a number of ways.
1. Plot weight change, with zero hopefully at the top of the Y axis, and losses becoming increasingly negative.
2. Plot weight divided by initial weight, with 100% hopefully at the top of the Y axis.
3. Make a panel chart using the methodology of Column Chart with Primary and Secondary Axes but applied to a line chart.
Miguel says
Dear Jon,
I’m a novice. Is it possible to get the details for every step to do this. I’m familiar with spreedsheets but I’m no expert. I would really like to create these panels so a step by step would be much appreciated.
Sincerely,
Miguel
Jon Peltier says
Miguel –
This tutorial is pretty much step-by-step; use your imagination to find commands you are not sure of. The tutorial I linked to in my previous comment is also step-by-step.
Jim says
I have line graph with elapsed time along the bottom. I want to break the x axis to and show the final targeted value (we wanted 36.6 by 2020). The target value was identified in 2010 so that is where I would like the target line to start and run horizontally from 2010 on. I can do it once by manually paintbrush editing the graph, but it is not easily repeatable. I need to be able to update yearly (if not more often) and continue to report on it. Ideally I would like to hand the excel graph off to someone else and have them just update/add values and graph would easily automatically update until the actual measurements are within 1-2 years of 2020 (the target year).
Sample image can be found at:
http://www.russells-online.com/Broken_X_Axis_with_target_line-sample.jpg
Jon Peltier says
Personally I would dispense with the broken axis and make the data label for the horizontal line “2020 Target: 36.6”
Karen says
Thank you very much, this really helped!
Dan says
Jon, thanks for the tutorial. I’m doing stat courses at Uni and the lecturers emphasize the need for clarity if we use a figure (like a graph) to represent our data. I struggled to represent very skewed data while trying to follow this mantra before finally coming across your tutorial. The two panel approach is much more efficient than side-by-side. I also opted to remove all of the ‘one-off, or loner’ data points and include those in the Figure Description section (for example, all these are n=1, density = 0.03, etc.). This has made for a very uncomplicated, aesthetically pleasing graph, while still getting all the important quantitative information across.
joe says
This sounds like a good idea, if only I could get past Step 1… (how do I get Excel to show the axis on the right also?)
Mike says
sorry to ask, it would be nice if you can give me details of how you can insert the break….?
Jon Peltier says
An axis break is a horrible thing to do to a chart and to its readers. I am not helping anyone break an axis.
Anonymous says
Thank you for this tutorial. It was very helpful. I was looking to put a break in my axis, and I agree that this is a much better solution. Also, I think my boss will be pretty impressed! Also, I’m glad the title is of the tutorial is “broken y axis”- I wouldn’t have found this page otherwise, so I would never have thought to search for a panel chart.
John Lorenc says
Hi Jon,
An excellent tutorial. For dullards like me, you might consider putting this text:
“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.”
In the body above. Before I wised up and read the comments, I was tearing my hair out about this!
Thanks again for a great tutorial.
Sarah says
Well, you’ve done a good job of showing up in google search results. I’m a little offended, frankly. It’s awfully presumptuous to think the only reason one would want to break an axis is to compensate for some outliers. I want to break an access for the following scenario:
I have narcolepsy as well as hypersomolence and insomnia. My doctor has asked me to keep a sleep log, the old fashioned way (by hand). I thought it would be fun to put it in excel so I could make fun charts and look at patterns. Except, I get to the first chart I want to look at, and I need a broken access. I want to look at bed times (which range between 9PM and 3AM, and wake times, which are a bit more condensed, between 5:30 AM and 8:00 AM. After much finageling, I actually have them beautifully plotted on a line graph, but its difficult to look at clearly because of the giant gap between 8AM and 9PM (where i’m not sleeping, in the case of the example – i’ll get to nap times in another chart). Suggestions?
Jon Peltier says
If it’s a sleep log, why not plot from noon to noon instead of midnight to midnight? Then your gap is only 3 AM to 5:30 AM, and the difference between the lines is your actual time asleep (not your time awake).
In fact, if you want a neat visualization, set up your data like shown below. You can use dates instead of just day names. Plot the time you went to bed the night before (or the morning of) the day/date, and the time you woke up. For the AM times, add 1 so AM is later than PM (type 1 into an unused cell, press Enter, copy the cell, select the cells with AM times, use Paste Special from the Paste button on the Home tab of the ribbon, and choose the options Values and Add). Plot in a line chart. I set the Y axis scale as follows: Min = 8 PM (0.83333), Max = 10 AM (1.416667), and Major Unit = 2 hours (0.08333).
You can make it even more interesting if you select one of the line series, then select Up/Down Bars from the Plus icon next to the chart in Excel 2013 or the Chart Tools > Layout tab in 2007/2010. Pick a nice fill color for the bars and use no border, format both line series so they use no lines, and format either of the line series so it has a gap width of 75% instead of the default 150%.
Sarah says
Thanks, Jon!
That’s pretty much what I came up with. I like the way you did the columns, though.
Graphs are in tabs 2 and 3. I’ll be doing plenty more, and some statistical models once I get more data.
Alice says
Just spent about 40 minutes at work reading through all of the comments and having a good chuckle in my office! It seems some people are completely unable to accept the word ‘no’!
I’m still going to hunt for a solution to breaking the y-axis for my graph – the panel chart will not work for the graph I wish to create! But thanks for the entertainment all the same!
JM says
Hi,
I need to know how to broken the x axis.
Don’t care about correlation between values.
Need to have, for e.g., 0 to 4 and 50 to 55.
Thank you
Anonymous says
Panel charts are great if you like clutter. I suggest that you update your URL so that it is not misleading. You clearly believe your way is better for presenting data but the URL (and the old hyperlinks that bring people here) mislead people about what they are going to find when they get here.
Harry Flashman says
I have just had a client request a report without about 48 bar charts, each with a broken axis. What a pain! If anyone is looking for instructions see this blog post: https://alesandrab.wordpress.com/2014/03/17/broken-column-and-bar-charts/
That said, I agree with John Peltier, Broken Axis charts are evil. They take a long time to make and they distort the data. I can sort of understand it if the differences lower values were still fairly significant, but in my clients case the reason certain values are barely visible is that the values were very low.
matthew demmer says
Hi Jon,
As someone sensitive to statistical abuses, I appreciate this blog post very much. However, isn’t it a bit overly dogmatic to say that you should NEVER use a truncated axis? Some data is actually misleading if it’s not included. Take the SAT for example. If a student were to randomly guess answers on an SAT (or most standardized tests) they would attain a score higher than zero. Let’s say that score by random guess score were say 200 out of 800. Then 200 should really be the starting value of the y axis given that reasonably no one would score worse unless they tried. BUT, it is POSSIBLE to score a zero. But in this case, being statistically virtuous is misleading in the other direction. A student who improves from a 600 to a 700 has done incredibly well for himself or herself but given that the bottom 200 points are misleadingly present the graphical representation of that improvement is less impressive. So a) wouldn’t a truncated y axis starting at 200 be reasonable here? and b) wouldn’t a broken axis be less misleading than simply starting at 200 as excel would have it?
Jon Peltier says
Matthew –
This post is about the practice of cutting out the middle of an axis scale, which gives the appearance that the larger values are much closer to the smaller values than they are.
Changing an axis scale so it does not include zero is okay for some types of chart. It should not be used for bar charts or area charts, where the value is encoded by the height of a shaded element in the chart. For line or XY charts, where the value is encoded by the position of a marker along the axis, starting an axis above zero is perfectly acceptable.
Layn says
How do you get just 1 gridline to show up and split the 2 panels?
Jon Peltier says
Layn –
That single gridline is actually a horizontal axis with no axis labels or tick marks.
Cirano Melville says
Sorry to ask, it would be nice if you can give me details of how you can insert the break…. Please?
Ryan says
Hi Jon,
First let me say: I completely agree with you! The Y axis should never be morphed with a break. However, I’m wondering about your thoughts on indicating a break in time on the X Axis. Consider the following example:
You’re collecting daily data on a student, let’s say homework completion data. You begin collecting the data, and Spring Break takes the child out of school. You begin data collection again immediately after Spring Break. Now you have a gap in time where no data was collected because no homework was available. There are two ways to show this: 1) use dates, and allow those dates to simply be empty and 2) use of a phase line with appropriate annotation on the graph. However, a third solution is a break in the x axis. The third is much more efficient, and is not explicitly manipulating the perception of the data.
Just curious about your thoughts.
-Ryan
yuri says
What a knob with an attitude – even if the point is absolutely valid.
Chim20 says
Peltier, please do not assume you know what is best for every scenario. A very real reason to use a split axis is to have a log plot on the upper half of the broken line and linear chart on the bottom to essentially just show a zero data point. In my field we look at relations that span many orders of magnitude but also require a data point at zero to reinforce the presence of say an asymptotic behavior to demonstrate a good fit to some nonlinear model. For example, I may look at varying the flow of a chemical in a reactor over many orders of magnitude to see the impact on some property of a crystal. However, due to hardware (MFC) limitations I can only reduce the chemical flow so far, and it may be difficult to see the asymptotic behavior with that data alone. A data point with no flow is the solution, but clearly that cannot be plotted on a log plot. This is a very valid reason to use a broken plot, so please do not impose your opinion on the matter that your way is always better. You could have just said these are alternative methods and left it at that.
Chim20 says
To continue my last response, the solution appears to be that other software is required that has built-in features for splitting the axis. As an Excel expert, you should be aware of the limitations of Excel and know when to direct people elsewhere for a solution rather than trying to force a solution. Sorry for being harsh, I have found a lot of useful information on your website, but this article definitely did not take the right approach to the problem.
Jon Peltier says
I respect your contrary view. If your audience is familiar with such a usage, then by all means do whatever you want to your charts.
In most cases, people want to break axes simply to enable all points to appear on a chart without regard for values, and this causes misrepresentation of the data they are showing. It is for this >99% of cases that I do not choose to teach how to break axes.
As an Excel expert, I am painfully aware of Excel’s limitations, as I collide with them every day. Many of Excel’s capabilities can be viewed as limitations, in that they encourage techniques that hinder good data presentation. These include 3D effects, shadows and color gradients, and the like. I discourage the use of these, and I am not going to teach methods that further hinder good data presentation in >99% of cases.
In addition, most people do not want an alternative to Excel, either for simplicity or because their company does not allow them to install other software.
bobbi says
Hi, do you have a step by step guide how to actually do this or a csv of the chart itself? I get to the part about creating the 2nd series and formatting them to the secondary axis but the 1st series disappears and it still doesnt break into the second panel. There has to be a step I am missing. Your help is appreciated.
Jon Peltier says
Bobbi –
I presume you’re following the steps in the section entitled Making the Panel Chart (It’s Easy!).
Well, I realized it wasn’t so easy, because I left out the details, so I’ve rewritten the last half of this tutorial. The steps are now spelled out in sufficient detail that I’m sure you can follow along.
Alex says
“Broken Axis” was not questioned years ago. It even could be find in better newspapers for the academic public. We were warned about it in statistics and urged to really show the break, rsp. stress it be out of scale. That was simply it.
Nowadays, people’s concern is they might be sued by anybody for an “incorrect” statement, as which broken axis might occur to the dumb. Like the cat dried in the microwave.
It is, how it is. It is no critics to anybody. Broken axis needs one chart. Panel chart needs two. I like Panel Chart, however.
Jon Peltier says
Broken axes were criticised long ago. In her 1969 book Practical Charting Techniques, Mary Spear wrote:
“The draftsman may have indicated that the scale was broken using several methods, but that is risky. The distorted impression is the one remembered, not the broken scale.”
She also criticised 3D charts, bar charts without a zero baseline, and overuse of pie charts; these are all issues we are still plagued with.
Thanks to Xan Gregg, who recently reviewed Spear’s book.
guideadda says
This post is about the practice of cutting out the middle of an axis scale, which gives the appearance that the larger values are much closer to the smaller values than they are.
Changing an axis scale so it does not include zero is okay for some types of chart. It should not be used for bar charts or area charts, where the value is encoded by the height of a shaded element in the chart.
guideadda says
The draftsman may have indicated that the scale was broken using several methods, but that is risky. The distorted impression is the one remembered, not the broken scale. https://www.guideadda.com/pureit-water-purifier/
zepeu says
Hello,
Thank’s a lot ! This was really useful, but there is a small detail that I don’t like, the 0 isn’t indicated on the second axis :(
In my case, it would be necessary, but I have no idea how to show it :(
Thank’s anyway !
Jon Peltier says
Zepeu:
In my example, both axes show zero. Perhaps you need to adjust the custom number format you are using?
Jessica Dery says
Thank you!! This is a wonderful tutorial! I was wondering if it is possible to turn a stacked and clustered column graph into a paneled one, still showing both stacks and clusters?
Jon Peltier says
Hi Jessica –
It should be possible, but you need to double the number of series, with matching ones in the bottom and top panels, and with an additional transparent series to allow the upper panel series to float in the upper panel, above the lower panel series.