Broken Y Axis in an Excel Chart

Comment from Naomi B. Robbins
Time: Friday, November 18, 2011, 11:46 am

I read the title, saw the top figure and thought to myself, “Finally I’ve found an area where I disagree with Jon.” Then I read, “You won’t find it here.” Another great post!

Comment from Patrick M
Time: Friday, November 18, 2011, 12:11 pm

Jon,

Brilliant, as usual. I have fended off requests before for a broken y axis, but I never thought about panel charts as an alternative. (As for logarithmic scales for most audiences: don’t even go there!)

Patrick

Comment from Bob
Time: Friday, November 18, 2011, 6:33 pm

Hi Jon,

I recently tried using your older “broken axis” method for a report. It was complicated and the results were not satisfactory.

My only observation with this method is the tick marks. I would want to make sure the steps on the right and left axis line up properly.

Cheers,

Bob

Comment from Rob
Time: Saturday, November 19, 2011, 7:20 am

Excellent stuff. Really useful. Thanks!

Comment from Wal
Time: Sunday, November 20, 2011, 2:02 am

It would be nice and help to include the data setup for plotting this as ell. Tricky to work out without some clues. Is it possible to send this?? Otherwise very helpful.

Comment from Stefan
Time: Tuesday, November 22, 2011, 4:13 am

Hi Jon,

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

Stefan

Comment from Bat
Time: Thursday, November 24, 2011, 4:14 am

Hey could you please explain how to brake the y axis

Comment from James Mansell
Time: Friday, November 25, 2011, 6:45 am

Thanks for very helpful article, just working through I can’t work out what scale setting should be on the secondary axis? Can you let us know?

Thanks

James

Comment from Joy
Time: Wednesday, November 30, 2011, 12:16 pm

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

Comment from Ninad
Time: Thursday, December 1, 2011, 2:22 am

Hi Jon,

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

Regards,

Comment from Joe
Time: Thursday, December 1, 2011, 8:22 pm

Hello Jon,

Can you please email me the old instructions for breaking the Y axis? I have an old slide that I made that way, and once a year I need to update it. My boss doesn’t like the panel chart version.

Thanks,

Joe

Comment from JJ
Time: Tuesday, December 6, 2011, 6:20 pm

Your method is no clearer, but nice try.

Comment from sixseven
Time: Thursday, December 8, 2011, 1:34 pm

Jon,

How do you truncate the data in the second series to <= to 7.5M? A simple method would be to copy the data to a different place in the spreadsheet and manually change the value, but I get the impression you did this through formatting?

Please help.

-sixseven

Comment from sixseven
Time: Thursday, December 8, 2011, 5:10 pm

Hi John,

Thanks for writing back. I just used this technique on an executive spend report, and they liked it.

So back to my initial question. Your instructions say:

“The secondary axis is scaled to show the smaller values. Note that the two very large values (>30M) have been truncated at a suitably small value (7.5M).”

How did you truncate the large values to 7.5M?

Thanks!

-67

Comment from sixseven
Time: Friday, December 9, 2011, 1:38 pm

Hi Jon,

I’ll give you the benefit of that doubt that you didn’t intend for your message to be snarky. So, thanks for your reply and help!

I used another range too in my report. As you know, there can be many ways to accomplish the same task is excel. I was just wondering if you had a different approach when truncating the value. (formula, formating, etc…)

Thanks again!

-67

Comment from AJ
Time: Wednesday, December 14, 2011, 11:08 am

Hi Jon,

Your explanation looks very good and I totally agree with you for a bar chart application that you shouldn’t split the axis as it distorts the image. However for the problem I currently have, that isn’t a problem and I still need to split the axis.

I have two measurements that were taken over two system configurations and hence at two different times; between 10am and 11am for the first and between 6pm and 7pm for the second. Due to the type of measurement it is, I need to quote the times correctly (And so can’t just move one forward by modifying the time data). The time inbetween the two measurements has no relevance and the second measurement could have been taken directly after the first but it wasn’t. During the measurement itself, time is relevent. Therefore if I was to split the graph it would not be distorted by the way you suggest for bar charts. I don’t think the alternative you suggest is relevent for a time domain plot.

Do you know any other alternatives to splitting the axis in this case? Can you provide advice or links to your previous page showing how to split the axis?

Cheers,

AJ

Comment from Bat
Time: Wednesday, December 21, 2011, 9:45 pm

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

Comment from AJ
Time: Wednesday, January 4, 2012, 4:31 am

Hi Jon,

Thanks for your advice. I had thought of that although was trying to avoid it. It does look reasonable although I still feel that for my application the split axis would be better. The reason I say this is because with the primary and secondary axis, you have to work out which axis is relevant to each plot in the graph rather than just look at it and it is obvious (as in the split axis method).

By the posts you’ve been receiving about the split axis and you’re replies, may I suggest you change the title of the page? I think you’re getting so many posts requesting the tutorial on split axis’ because that is what the page is called and that is what those coming here are wanting to find out (for better or for worse than other methods).

Happy New Year!

AJ

Comment from Rob
Time: Thursday, January 5, 2012, 2:18 pm

I need to do a broken y-axis chart with a line chart, as I am comparing returns over time in various market indexes. Is there an easy way to do this?

Comment from Rob
Time: Thursday, January 5, 2012, 2:30 pm

Thanks for the quick reply, I agree paneling is a better implementation for Bar Charts. I am using a line chart that compares a few stocks against each other, over time and a line chart allows us to easily see the change and the change relative to the other stocks, however seeing as some stock prices are a lot lower than other (some in low hundreds, others in high thousands) there is a huge amount of white space in the chart. I need them to maintain the scale against each other so log axis wont work, an paneling seems to make more sense for comparing magnitudes rather than percentage changes.

Thoughts?

Comment from Rob
Time: Thursday, January 5, 2012, 3:15 pm

That is something that was discussed, thank you for your help, I think I have found a solution by moving the smallest data set to the secondary axis. If I keep the differential from min to max axis values the same for both I can position them near each other without losing the ability to see them relative one another. If I hide the smaller values on the primary axis and larger values on the secondary axis it makes it easy to see them closer together.

Thank you for all your help!

Comment from Lou Dean
Time: Wednesday, January 11, 2012, 7:08 am

*sobs*

Please can we have the instructions back?

I know that you don’t want to perpetuate a bad chart but I loved the split axis chart and it always worked for me.

I haven’t had to use it for a while and the panel chart just doesn’t cut the mustard!

*wanders off to find more tissues*

Comment from Partha Banerjee
Time: Wednesday, January 11, 2012, 5:34 pm

Hi Jon,
My daughter wants to use your concept for one of her project demo at Fulton County Tech Fair, GA. She is at her middle school. While I have seen your license agreement and understand that she can, still I will appreciate if you send your consent in email response.
Thanks in advance.

Comment from Marcin K
Time: Tuesday, January 17, 2012, 4:54 am

Hi Jon,

My problem is common with Robs, I`ve got a line chart and i have to break Y axis, unfortunately it is impossible to use other way than break the axis , cause this is one of the points of my exam :(

Could U provide any tutorial for line chart ?

I`ll be greatful … nobody knows how to do it :( and the column chart tutorial is not valid for line type :(

Comment from Naomi B. Robbins
Time: Tuesday, January 17, 2012, 7:30 am

Marcin,

One possibility is to use a full scale break. By a full scale break I mean two separate panels next to each other: one with the scale before the break and one with the scale after the break. This is more forceful than the two little parallel lines often used that are difficult to notice. I’ll add a figure as an example when time permits.

Comment from Johannes D
Time: Tuesday, January 31, 2012, 11:04 am

And another satisfied reader. I came for the broken y-axis and left as a convert panel diagram user. It’s still some handwork for the gradient, but much easier to maintain than an ugly axis hack.

Comment from Adrian
Time: Wednesday, February 8, 2012, 7:16 am

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

Comment from Patrick
Time: Wednesday, February 8, 2012, 8:33 am

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

Comment from Adrian
Time: Wednesday, February 8, 2012, 8:47 am

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

Comment from Joana
Time: Thursday, February 9, 2012, 7:38 am

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

Comment from Neith
Time: Wednesday, February 15, 2012, 2:55 pm

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?

Comment from Neith
Time: Wednesday, February 15, 2012, 4:52 pm

Thank you!

Comment from Pierre
Time: Sunday, February 19, 2012, 8:11 am

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.

Comment from Naomi B. Robbins
Time: Sunday, February 19, 2012, 9:25 am

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.

Comment from Pete
Time: Sunday, February 19, 2012, 3:33 pm

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

Comment from Heather
Time: Monday, February 20, 2012, 2:44 pm

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

Comment from Judy
Time: Saturday, March 3, 2012, 8:17 pm

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.

Comment from Marcela
Time: Monday, March 5, 2012, 8:00 pm

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.

Comment from Javed
Time: Thursday, March 8, 2012, 1:00 am

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.

Comment from Liezl
Time: Monday, March 12, 2012, 12:33 pm

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

Comment from Shiuan
Time: Wednesday, March 14, 2012, 4:54 pm

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!

Comment from Shiuan
Time: Thursday, March 15, 2012, 10:21 am

I got it! Thank you for helping!

Comment from Mara
Time: Friday, March 23, 2012, 1:53 pm

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

Comment from Matt
Time: Monday, March 26, 2012, 9:25 am

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.

Comment from Alex
Time: Wednesday, March 28, 2012, 3:08 pm

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

Comment from Alex
Time: Wednesday, March 28, 2012, 4:36 pm

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!

Comment from RJ
Time: Monday, April 2, 2012, 5:06 pm

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!

Comment from RJ
Time: Monday, April 2, 2012, 5:15 pm

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

Comment from Patrick O
Time: Wednesday, April 4, 2012, 8:59 pm

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

Comment from Frank
Time: Saturday, April 7, 2012, 6:57 pm

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

Comment from Bob
Time: Tuesday, May 15, 2012, 2:41 pm

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?

Comment from Bob
Time: Wednesday, May 16, 2012, 3:17 pm

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.