There is simplicity in breaking the column of a chart, and we are going to justify this simplicity. You will follow as I break one column.
But first, look at the data we want to break column on:
Create another table, which is titled Break Info. The break is where you would like to break, and the resume is where the column should resume after the break.
Click on the value beside max in your new table (1), and type Break_Max in the name box (2).
Note: Repeat this step on the rest of the data, using SPLIT_RESUME, SPLIT_BREAK, and SPLIT_MIN, as the name.
You should create a new table, with the original data. Start with the labels.
Breaking a column
Click on a cell beside new label (1), and type in =IF(B4>SPLIT_BREAK;SPLIT_BREAK;B4) (2).
Double-click on the small square that shows when clicking on the result of previous formula.
Click under break (1), and type =IF(B4>SPLIT_BREAK;1;NA()) (2).
Double-click on the small square.
Click under continue (1), and type ==IF(B4>SPLIT_BREAK;B4-SPLIT_RESUME;NA())
Double-click on the small square.
Mark between G3 and J15 (the new data).
Inserting a 2d bar chart
Click on insert (1), column chart (2), and choose 2D chart bar (3).
Right-click on the break (1), select white color in fill (2).
Note: Repeat this on to continue series, and then double-click on continue, and break in the legend series, and press delete on your keyboard.
In conclusion, we just created a chart with the break in one column. You can keep working on the chart, doing things like changing the "Original" series name. This is how our chart looks like, with a broken column.
Alternative solution
Another method to break a chart's column in Excel is to use a combination of stacked and regular columns. Here are the steps to follow:
- Create a regular column chart with your data.
- Right-click on the data series that you want to break and select "Format Data Series" from the context menu.
- In the "Format Data Series" dialog box, select "Fill" from the left-hand menu.
- Under "Fill," select "No fill" and then select "Border color."
- Select a border color that will separate the two sections of the column.
- Change the chart type of the data series to a stacked column chart by selecting "Change Chart Type" from the "Design" tab in the ribbon.
- In the "Change Chart Type" dialog box, select "Stacked Column" and then select "OK."
- Right-click on the data series again and select "Format Data Series" from the context menu.
- In the "Format Data Series" dialog box, select "Fill" from the left-hand menu.
- Under "Fill," select a color for the top section of the column.
- Adjust the transparency of the top section of the column by using the "Transparency" slider.
- Click "Close" to apply the changes.
Your chart's column will now be broken into two sections with a border separating them. The top section will be filled with color and have a transparency effect applied to it. The bottom section will be a regular column. You can further customize the formatting options to create the desired effect.
Further reading: Chart with a break Y axis