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.
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.
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!
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
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.
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
Excellent stuff. Really useful. Thanks!
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.
Hi Wal -
The data is located in BrokenYData.csv.
Hi Jon,
maybe we can use Andy Pope´s solution as an add?
Stefan
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.
Hey could you please explain how to brake the y axis
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.
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
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.
NOOO…. @Jon, please bring back the broken axis page! It was so useful!
Joy -
The technique is too complicated for most people, and the charts it makes are difficult to interpret correctly.
Hi Jon,
Can you please share the XL file for ease for novices like me.
Regards,
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
Your method is no clearer, but nice try.
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
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
“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.
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
I just manually changed the values. If I were building a reusable approach, I’d probably write some formulas or code to do this.
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
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).
Please teach us how to break Y axis according to the first picture.
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.
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
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?
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.
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?
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.
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!
*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*
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.
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 :(
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.
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.
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.)
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
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
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
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.
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
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.
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?
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.
Thank you!
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.
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.
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
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
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.
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.
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.
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.
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
Liezl -
If the panel method interferes with calculations of area under the curve, wouldn’t the broken axis method as well?
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!
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.
I got it! Thank you for helping!
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
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.
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.
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.
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
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!
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!
Jon, I see you responded to other users about the same problem. Thank you!
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
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?
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
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?
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.
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.
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.
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?
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.
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
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.
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.
Panel charts are o.k….in some occasions. But I need broken y axis for my presentation, no room for panel chart. what now?
Best practices are only for when they’re convenient?
Jon,
Stand your ground. I like this solution with the panels very much.
Bob
can anybody tell me how to draw broken y axis chart???????
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.
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.
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
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.
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
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?
way to have a misleading title
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.
Daniel -
You can make the panel chart in either orientation.
Here’s a complicated example with side-by-side panels:
http://peltiertech.com/Excel/ChartsHowTo/PanelChart1.html
This one’s less complex:
http://peltiertech.com/WordPress/how-to-build-a-simple-panel-chart/
Here are a couple 2×2 panel charts:
http://peltiertech.com/WordPress/how-to-build-a-2×2-panel-chart/
http://peltiertech.com/WordPress/marimekko-replacement-2-by-2-panel/
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?
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?
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?
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.
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.
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,