Knowing how to break the y-axis in Excel is a useful trick to learn if you ever have disproportionate data that needs to be illustrated clearly in a chart. The technique basically cuts unnecessary space from the middle of the y-axis. For example, if the bars in a chart range between 10 to 20 then suddenly jump into the hundreds, you can cut the larger bars down to size while still making their values obvious to your audience. While there are several ways to do this on a chart, this method takes only a few minutes, without the need for plug-ins or an advanced degree in mathematics.
Launch Excel and open a new workbook. For illustration purposes, type “10,” “12,” “13,” “500” in the first column from cells A1 through A4. If you created a chart from these values, the last value would overwhelm the chart, making the smaller numbers nearly indistinguishable.
Highlight the column of numbers by dragging the cursor over them. Press “Ctrl-C” to copy them. Click cell “B1” and press “Ctrl-V” to paste them in the second column. Click cell “B4” and change the value from “500” to “50.” Adding this column
allows you to customize the chart without changing the original data in your worksheet.
Drag the cursor across cells “B1” to “B4” to highlight them. Click the “Insert” menu and select “Chart.” Select a “Column” chart and click “Next.” Continue clicking “Next until you get to the “Chart Location” menu. Select “As a New Sheet” and click “Finish.”
Click on the background to open the Format Plot Area dialog box. Click the “Color” menu and change it to white. Click "OK."
Click the “Insert” menu and select “Autoshapes.” Click the “Line” tool. Drag the tool diagonally across the middle of the last bar. Double-click the line. Click the “Color” menu and select white. Type “20” in the Weight text field. Click “OK.”
Click the “Text Box” tool. Drag it beside “10” on the y-axis and type “10”. Draw text boxes beside each of the other numbers in the y-axis, typing “500” beside “50.”
Double-click a number in the y-axis. Click the “Color and Lines” tab. Select “None” in the “Tick Mark Labels” section. Click “OK.” The numbers in the y-axis disappear, leaving the numbers you typed in the text boxes as the y-axis values.