up vote 1 down vote favorite
share [g+] share [fb]

I want to break the Y-axis in an Excel sheet. How can I do that?

For example, suppose I have the data in a range from 0-7000 and then range from 22000-28000. I want a break in the Y axis from 8000-20000.

link|improve this question
feedback

2 Answers

up vote 1 down vote

You cannot break the y-axis on an Excel chart to show several, non-contiguous ranges but you can change the default range by right clicking on the y-axis and selecting Format Axis... and then changing Minimum and/or Maximum from Auto to Fixed then supplying new values.

link|improve this answer
1  
it can be broken but i m not getting how to use add label in that.this is the link which shows how to break y axis. peltiertech.com/Excel/Charts/BrokenYAxis.html – akhil Jun 9 '10 at 10:44
2  
Ouch! That hack requires you to modify the data, use an add-on and overlay other artwork. If you're prepared to go to that amount of effort, I'd suggest keep your spreadsheet data and Excel install clean and simply paste the chart into an image editor and remove the unwanted y-range. – Mike Fitzpatrick Jun 9 '10 at 11:49
Yes, and plus, in excel you have to line up those \\ marks by hand... Even in MSPaint you can do a better job of lining them up using pixel coordinates – Dan Jun 10 '10 at 22:13
@Dan - you don't have to line the "break" shapes by hand. If you select a drawing object, copy, then select a line series and paste, Excel uses that shape for the 'dots' along the series. So in the case with this hack, the custom shape is shown where the extra "cut off" series has points plotted, and they will line up perfectly every time, even when you change the dataset. – AdamV Jul 29 '10 at 19:50
feedback
up vote 0 down vote

I covered this technique in a recent blog post: Broken Y Axis in an Excel Chart

Basically, the point is that you shouldn't try to represent data in a single chart if you have to resort to breaking an axis. No matter how clearly you try to indicate that the axis scale has a discontinuity in it, the precognitive interpretation that the "broken" values aren't far off from the lower values overrides any conscious effort to make sense of the broken values. Not to mention the approach is a pain to create and maintain.

In the article above, I suggest an alternative approach, which is to make a panel chart, one with a Y scale that includes values up to the break, the other panel with a Y axis scale that includes all values, with the smaller values smushed against the X axis. A panel chart takes some effort, not as much as the broken axis, and if you want, you can simply make two separate charts instead of a si

link|improve this answer
Jon Peltier, I don't believe you "covered this technique" when your response is that you don't approve of people doing it. This was incredibly unhelpful- I need to know how to break a y axis in any type of Excel chart. That's why I'm reading the responses to a question called "Excel- how to break Y axis?". – Carol Jan 4 at 23:58
@Carol the answer suggests an alternative approach to achieve the same goal, right? Perhaps the question was too tightly specified... – Yar Mar 9 at 22:05
feedback

protected by Community♦ Mar 9 at 23:04

This question is protected to prevent "thanks!", "me too!", or spam answers by new users. To answer it, you must have earned at least 10 reputation on this site.

Not the answer you're looking for? Browse other questions tagged or ask your own question.