I covered this technique in a recent blog post: 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.
1) In order to create a combination dual axis chart, firstly select the data as shown below. 2) Go to Insert > Charts > Insert Combo Charts. 3) Click on the drop down arrow and choose Clustered Column – Line on Secondary Axis.
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, and in particular. 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 IFs 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.
. The pull-down menu provides a series of options you can select to turn on or off. You can decide whether elements such as a line for the axis, a line all the way around the chart, or the title, are shown at all.
You can choose whether values are shown on the Y axis. You can also add tick marks.
This set of items allows you to choose one of four options: no tick marks, inside tick marks, centered tick marks, and outside tick marks. Try each one to see how it looks with your particular graph. You can also turn on or off gridlines in your graph. You can set the number of steps in the Y axis. For instance, when Steps was set to 4, the Y axis had four steps: 25, 50, 75, 100. Now that Steps is set to 5, the steps are 20, 40, 60, 80, and 100.
You can change the format of the numbers (for instance, choosing currency instead of plain numbers). You can specify the number of decimal places shown. You can choose how negative numbers are represented, with either a minus sign or in parentheses.