# How to break the Y axis in an Excel sheet

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.

-

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 single chart.

-
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?". – user112209 Jan 4 '12 at 23:58
@Carol the answer suggests an alternative approach to achieve the same goal, right? Perhaps the question was too tightly specified... – Dan Rosenstark Mar 9 '12 at 22:05

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.

-
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
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 – Jarvin 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

You can create a (fake?) chart with exactly the appearance that you want:

1. Create a column where you subtract 15000, only to Y-values larger than 7000. Use that column as your new Y-values for the chart.

2. Add X and Y values for a dummy series, with some points. If the minimum value in your X-axis is xm, your points are (xm, 0), (xm, 1000), ... , (xm, 6000), (xm, 8000), (xm, 9000), ... Value 7000 is skipped. You can change this.

3. Add cells with the labels that you will use for the dummy series: "0", "1000", ..., "6000", "23000", "24000", ... The values 7000 and 22000 are skipped, since they are overlapping.

4. Go to the chart, and remove the tick labels of the Y-axis.

5. Add a series with the dummy data points.

6. Add the labels to the data points. You can use references to the cells of item 3 (recommended), or enter explicit labels. Entering each label (either a reference or an explicit label) is tedious when you have many data points. Check this, and in particular Rob Bovey´s add-in. It is excellent.

7. Format the dummy series so it is visually ok (e.g., small, hairline crosses, no line).

You can use variations on this. For instance, you can add extra points to your dummy series, with corresponding labels. Gridlines would match the dummy series.

You can use this technique to create an arbitrary number of axis interruptions. The formula for the "fake" Y-values would be more complicated, with `IF`s to detect the interval corresponding to each point, and suitable linear transformations to account for the change in scale for each interval (assuming linear scales; no mixing linear-log). But that is all.

http://peltiertech.com/broken-y-axis-in-excel-chart/

http://ksrowell.com/blog-visualizing-data/2013/08/12/how-to-simulate-a-broken-axis-value-axis/

http://www.tushar-mehta.com/excel/newsgroups/broken_y_axis/tutorial/index.html#Rescale%20and%20hide%20the%20y-axis

-

I agree with @JonPeltier's point of doing this being a bad idea, conceptually. However, if you must, there is a simple solution.

Graph the entire range and save or capture the graph as an image. Open the image in an image manipulation program, like Photoshop or GIMP. Then cut out the area you don't want, move the upper range closer, and add jagged lines to the Y axis at the edges of the break to indicate the discontinuity.

-

## protected by Community♦Mar 9 '12 at 23:04

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).