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.
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.
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.
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.
You can create a (fake?) chart with exactly the appearance that you want:
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.
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.
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.
Go to the chart, and remove the tick labels of the Y-axis.
Add a series with the dummy data points.
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.
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.
PS: see also the links below. I still think my alternative is better.
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/