How to Break Chart’s column?

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:

data table

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.

another table

Click on the value beside max in your new table (1), and type Break_Max in the name box (2).

break max

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.

new table labels

Breaking a column

Click on a cell beside new label (1), and type in =IF(B4>SPLIT_BREAK;SPLIT_BREAK;B4) (2).

if split break

Double-click on the small square that shows when clicking on the result of previous formula.

results previous formula

Click under break (1), and type =IF(B4>SPLIT_BREAK;1;NA()) (2).

click under break column

Double-click on the small square.

double click small square

Click under continue (1), and type ==IF(B4>SPLIT_BREAK;B4-SPLIT_RESUME;NA())

if split break column

Double-click on the small square.

double click continue

Mark between G3 and J15 (the new data).

mark between new data

Inserting a 2d bar chart

Click on insert (1), column chart (2), and choose 2D chart bar (3).

insert bar chart

Right-click on the break (1), select white color in fill (2).

right click break column

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.

See also  How to make a Scatter Plot

ready break charts 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:

  1. Create a regular column chart with your data.
  2. Right-click on the data series that you want to break and select “Format Data Series” from the context menu.
  3. In the “Format Data Series” dialog box, select “Fill” from the left-hand menu.
  4. Under “Fill,” select “No fill” and then select “Border color.”
  5. Select a border color that will separate the two sections of the column.
  6. 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.
  7. In the “Change Chart Type” dialog box, select “Stacked Column” and then select “OK.”
  8. Right-click on the data series again and select “Format Data Series” from the context menu.
  9. In the “Format Data Series” dialog box, select “Fill” from the left-hand menu.
  10. Under “Fill,” select a color for the top section of the column.
  11. Adjust the transparency of the top section of the column by using the “Transparency” slider.
  12. 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.