Broken Y Axis in an Excel Chart

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.

Chart with Broken Y Axis

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.

Chart with Unbroken Y Axis

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.

Chart with Logarithmic Y Axis

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).

Chart with Broken Y Axis

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.

Chart with Panels Having Distinct Y Axis Scales

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.

Primary Axis Data

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).

Secondary Axis Data

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.

Upper (Primary Axis) Panel

The secondary axis (right) has been scaled to keep its data within the bottom half of the chart.

Lower (Secondary Axis) Panel

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).

All Data Plotted on Primary Axis

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.

Data Allocated to Primary and Secondary Axes, Axes Rescaled

Format the secondary series so they have the same fill colors as the corresponding primary series.

Matching Bars Formatted the Same

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.

Axis Labels Cleaned Up

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.

An Effective Gradient Format

Here is the finished panel chart, repeated here for your viewing pleasure.

The Finished Panel Chart

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. 

Peltier Tech Chart Utility

Comments

  1. 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!

  2. 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

  3. 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.

  4. 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

  5. Excellent stuff. Really useful. Thanks!

  6. 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.

  7. Hi Wal -

    The data is located in BrokenYData.csv.

  8. Hi Jon,

    maybe we can use Andy Pope´s solution as an add?

    Stefan

  9. 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.

  10. Hey could you please explain how to brake the y axis

  11. 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.

  12. 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

  13. 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.

  14. NOOO…. @Jon, please bring back the broken axis page! It was so useful!

  15. Joy -

    The technique is too complicated for most people, and the charts it makes are difficult to interpret correctly.

  16. Hi Jon,

    Can you please share the XL file for ease for novices like me.

    Regards,

  17. 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

  18. Your method is no clearer, but nice try.

  19. 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

  20. 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

  21. “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.

  22. 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

  23. I just manually changed the values. If I were building a reusable approach, I’d probably write some formulas or code to do this.

  24. 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

  25. 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).

  26. Please teach us how to break Y axis according to the first picture.

  27. 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.

  28. 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

  29. 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?

  30. 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.

  31. 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?

  32. 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.

  33. 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!

  34. *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*

  35. 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.

  36. 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 :(

  37. 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.

  38. 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.

  39. 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.)

  40. 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

  41. 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

  42. 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

  43. 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.

  44. 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

  45. 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.

  46. 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?

  47. 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.

  48. Thank you!

  49. 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.

  50. 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.

  51. 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

  52. 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

  53. 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.

  54. 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.

  55. 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.

  56. 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.

  57. 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

  58. Liezl -

    If the panel method interferes with calculations of area under the curve, wouldn’t the broken axis method as well?

  59. 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!

  60. 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.

  61. I got it! Thank you for helping!

  62. 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

  63. 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.

  64. 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.

  65. 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.

  66. 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

  67. 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!

  68. 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!

  69. Jon, I see you responded to other users about the same problem. Thank you!

  70. 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

  71. 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?

  72. 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

  73. 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?

  74. 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.

  75. 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.

  76. 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.

  77. 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?

  78. 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.

  79. 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

  80. 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.

  81. 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.

  82. Panel charts are o.k….in some occasions. But I need broken y axis for my presentation, no room for panel chart. what now?

  83. Best practices are only for when they’re convenient?

  84. Jon,

    Stand your ground. I like this solution with the panels very much.

    Bob

  85. can anybody tell me how to draw broken y axis chart???????

  86. 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.

  87. 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.

  88. 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

  89. 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.

  90. 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

  91. 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?

  92. way to have a misleading title

  93. 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.

  94. 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/

  95. 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?

  96. 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?

  97. 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?

  98. 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.

  99. 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.

  100. 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,

  101. 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.

  102. 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.

  103. 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.

  104. 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

  105. 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

  106. 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!

  107. 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.

  108. 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).

  109. 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.

  110. Hilarious all around…

  111. 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.

  112. 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.

  113. 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.

  114. 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.

  115. 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?

  116. 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).

  117. 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

  118. 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

  119. 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).

  120. 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?

  121. 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

  122. 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.

  123. 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 (http://peltiertech.com/WordPress/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.

  124. Very clever. Thanks.

  125. Thanks for this – a great alternative to the broken graph!!! Just what I was looking for :)

  126. 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…

  127. 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

  128. Hide the small columns in the upper chart to lose any comparison between large and small values. Not a great idea.

  129. 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.

  130. 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.

  131. “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.

  132. 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.

  133. 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.

  134. 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.

  135. 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.

  136. 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.

  137. This was very helpful. Thank you!

  138. 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

  139. 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)”.

  140. 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.

  141. 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

  142. 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.

  143. 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?

  144. 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.

  145. 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.

  146. 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.

  147. 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

  148. 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.

  149. 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

  150. 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

  151. 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

  152. 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.

  153. 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

  154. 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.

  155. 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

  156. Personally I would dispense with the broken axis and make the data label for the horizontal line “2020 Target: 36.6″

  157. Thank you very much, this really helped!

  158. 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.

  159. 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?)

  160. sorry to ask, it would be nice if you can give me details of how you can insert the break….?

  161. An axis break is a horrible thing to do to a chart and to its readers. I am not helping anyone break an axis.

  162. 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.

Subscribe without commenting

Trackbacks

  1. [...] Kijk eens naar deze pagina: Broken Y Axis in an Excel Chart | Peltier Tech Blog | Excel Charts [...]

  2. [...] Y axis (very easy) Add or remove a secondary axis in a chart You could make a panel Chart (easy) Panel Chart Or you could create a broken Y axis (hard) (Do a web search for something like Excel Broken Y axis [...]

  3. [...] Interesting idea to use a “panel chart” instead of a broken chart. [...]

  4. […] Maybe one of these links will help. AJP Excel Information – Broken Y axis and columns Broken Y Axis in an Excel Chart – Peltier Tech Blog […]

Write a Comment

I welcome comments from my readers. If you have an opinion on this post, if you have a question, or if there is anything to add, I want to hear from you. Whether you agree or disagree, please join the discussion. If you want to include an image in your comment, post it on your own site or on one of the many free image sharing sites, and include a link in your comment. I'll download your image and insert the necessary html to display the image inline.

Subscribe without commenting

Peltier Tech Chart Utility

 

Create Excel dashboards quickly with Plug-N-Play reports.

 

Site by: Dawud Miracle, Business Coach & WordPress Websites