# Broken column and bar charts

It’s been some time since I’ve written a post on Excel graphs and charts, mainly due to a lack of inspiration on my part.  I have to be visually inspired with my charts and the ones that I usually use are all on my blog.  That being said, since hosting a webinar on Excel for the women’s network of the company I work for, I have been inundated with requests and as a result I am inspired again!

One of the requests boiled down to a mismatch of data needing to be displayed in the same chart.  I.E. one of the series displayed in the chart would be very large compared to the others.  Here is my sample data table:

As you can see most of the data is below 1,000 units.  The last series, (series 4) is just above 35,000 units.  If you plot that data as a normal column chart you will get something like this:

It’s almost impossible to read the values for Series 1-3, 4 & 5.  What we need is a broken column chart.  By using a broken column, you can see both the detail at the bottom of the scale and at the top.  How is it done?  You can download the worksheet I used here.

## Step 1 – work out what you want the chart to look like

The first thing we need to do is work out where the different groups of data lie.  We have one group of data that ranges from 100 to 900.  Series 4 on the other hand is just over 35,000.  So how could we display that data to get clarity?  We need to have a lot of room on the graph between 0 and 1,000 to be able to view the different heights of the columns.

Looking at the data, if we had a chart that had 0, 250, 500, 750, 100, a break, then 35000, 35250 and 35500 as the scale we would be able to see most of the detail clearly.  Something like the graph below:

## Step 2 – organise the data

To do this, we need to do is make some calculations to work out how to draw the chart.  We want the break to occur at 1,000 units and restart at 35,000 units.  The minimum is zero units and the maximum 35,500 units.

Once this data is entered into excel, we can use the Name Manager to assign these values to formulae.  In my worksheet, the value for the break is entered into cell B15.  By clicking on this cell, and then clicking on the Name box, we can enter a name.  I’ve named this value SPLIT_BREAK_COL.

I’ve entered similar names for the other values: SPLIT_MIN_COL, SPLIT RESTART_COL, and SPLIT_MAX_COL

Then we can do some calculations.  In your worksheet create three new columns: “Before”, “Break”, and “After”.  Assuming that your first row of data is in Row three, the “Before” column is column C, and that the “2013” column (the original data) is column B, your calculation in cell C3 should look like this:

=IF(B3>SPLIT_BREAK_COL,SPLIT_BREAK_COL,B3)

This means if the value in cell B3 is greater than the value of the break, then the cell should display the value of the break, if not, the original value.  If you copy the formula down the rows, every cell should show the original value except for series 4 which should read 1,000.

In the Break column, the calculation in cell D3 should read something like this:

=IF(B3>SPLIT_BREAK_COL,50,NA())

This means if the value in cell B3 is greater than the value of the break, then the cell should show the value 50, otherwise enter a #N/A value.  This value fo “50” will create the “Break” once graphed.

In the After column, your calculation in cell E3 should look like this:

=IF(B3>SPLIT_BREAK_COL,B3-SPLIT_RESTART_COL-1,NA())

This means if the value in cell B3 is greater than the value of the break, then the cell should make a calculation (the original value subtracted by the restart value, otherwise enter a #N/A value.  If you copy the formula down the rows, this will create the “remainder of the column.  Every cell should show #N/A except for series 4.

The table should now look like this:

## Step 3 – Plot the table as a stacked column

First, highlight columns C, D and E and then click on the Insert tab > Column > 2D stacked column.

Excel will create a chart as shown below:

Right click on the chart and then click on “Select Data” and click on the “Edit” button to adjust the Horizontal (Category) Axis labels.  Use column A as the range.

Delete the legend and gridlines by clicking on them and pressing the delete key.

We now need to format the Before and After series so that they are the same.  Click on one of the series and press Ctrl+1 to open the dialog box.  You can then change the fill color so that they match.

Make sure that the plot area fill is white and that the graph has no line (outline).

To create the break, simply insert a white rectangle by clicking on Insert > Shapes > Rectangle.  Then create the teeth by adding triangles filled with the same colour as the Before and After series.  Group the shapes together:

Then once it is grouped, click on the group to select it and then press Ctrl+C.  Click on the break series (red in the chart above) and press Ctrl+V.  Your chart should now look like this:

## Step 4 – calculate the new Y axis and scale

To show the break Y axis, we will need to create it.  This is done by adding an XY scatter line to the graph.  First create three new columns: “Labels”, “Xpos” and “Ypos”.  Write in the labels you want (see step 1).  Leave a blank where the break should be.

Then to create the arrow on the Y Axis where the break will be, we need to fill the Xpos column.  I have put zeroes for every label except the break.  This will help to create a vertical line with an arrow.  I want the arrow to be around a quarter of the size of one of the six series, so I entered a value of 0.25 (assuming my scale will be 0 to 6).

The Ypos values will determine where the labels will lie vertically.  In the calculations in Step 2, we determined that the break would be 50 units in value.  To begin with the Ypos and the labels will have the same value. This will change when we hit the break.  To centralize the arrow we will put the “blank” label as the previous value + 25.  The first label after the break will be the blank label value + the other 25.  The rest is steps of 250 to match the earlier scale.

I’ve also added calculations to work out the minimum and maximum of the Y scale:

# Step 5 – add an XY scatter line to the chart

Right click on the chart and choose “Select Data”.  Then click on the “Add” button.

Enter “Yaxis” as the Series Name and the Ypos data as the values.  In my worksheet that is:

=column!\$E\$13:\$E\$21

Press the OK button and then the OK button again.  The chart should update as follows:

Right click on the new series then press Ctrl+1.

In the new menu box that appears, choose to plot the new series on the secondary axis and press the close button.  The graph should update again and you should be able to see the new axis on the right hand side of the chart:

Right click again on the new series and this time choose “Change Series Chart Type”.  In the menu box that appears, choose a XY Scatter chart with Straight Lines.

The chart will again update:

Right click once more on the chart and choose “Select data”.  Choose the Y axis series and press the “edit” button.  Add the X axis values (xpos column).  In my worksheet this is:

=column!\$D\$13:\$D\$21

Then press the OK button and then the OK button again.  You will probably be no longer able to see the XY line on your chart.  This is because of the scale.

Click on the “Layout” tab in the “Chart Tools” section of the ribbon. Then click on Axes > Secondary Horizontal Axis > Show Default Axis.  Your XY line and a new Axis at the top of the screen will appear.

## Step 6 – Formatting

First if you haven’t already done so, make the primary vertical (left Y axis) and secondary vertical (right Y) axis match in terms of scale.  As you can see in the image above, I’ve given both of mine a minimum of 0 and a maximum of 1550 to match my calculations in step 4.  Once this is done you can delete the right hand axis.

Then format the (top) secondary horizontal axis so that the arrow is to one side only.  I’ve given mine a scale of 0 to 6.

Then double click the (left hand) primary vertical axis.  in the pop up menu that appears, choose to have no major tick marks, no minor tick marks and no axis labels.  Click on the line color tab and choose no line and press the close button.

Repeat for the (top) secondary horizontal axis.  Your graph should now look like this:

Double click on the XY line and format it to match the colour scheme you have chosen for the X axis.  In my case grey with a 1px line.  You can do this on the Line Color and Line Style tabs.

## Step 7 – add the XY labels

Click on the XY line.  Then click on the Layout tab of the Chart Tools section of the ribbon.  Then in the labels section, click on Data Labels > Left.  You may need to re-position the chart so that the labels can be read clearly:

Unfortunately these are not the labels we want.  There are addins that you can buy to give you custom labels.  However, it can also be done manually.

Double click on the first data label.  Then in the function (fx) bar, type in the cell reference for the label you want to appear.  So if I am starting at the top most label (1550), I double click it and then in the function (fx) bar I type:

= C21

Repeat this for each label, including the one in the centre of the arrow, which should update to show a blank label.

Re-position those that are difficult to read by double clicking on them and then with the mouse button held down, dragging them into their new position.

Your graph should now be finished!  You can use the same technique with a bar chart as well:

+Alesandra Blakeston

## 32 thoughts on “Broken column and bar charts”

1. Julien Martin-Schmitt says:

Like so much!!

2. Lucilla says:

HI!
Thanks a lot for the useful explanation! My problem is that I need to break a bar in a chart with different series. What I mean is that, to produce this fantastic result I need to use the stacked bar, but in general I need the “normal” bar chart because of different series. Is that possible!?!

Thanks a lot!

1. Lucilla says:

Thank you for your reply! I read yesterday also this post and I feel that it is really close to what I need but…
Just to let me understand: I have 5 series, per each serie I have to graph 7 bars (7 months). So, in the other post instead of “Float, Patterned, Coater,…” I have 5 elements and instead of hours and targets I have 7 points. These elements need to be graphed in clustered bars (not stacked). Then I have to add this trick to broke some columns that are really high…
So, what is your suggestion? how can I organize the data?
Thanks a lot! maybe I confused the ideas more… 🙂

2. I think I understand what you are saying. What you need to do is combine the two posts. I’m not sure if I can explain it in the comments though. Send me your data alesandra@about.me and I’ll have a look

3. Rebekah Myers says:

Hi! This is so helpful, but I’m having issues breaking a single data point in a series in a clustered column graph. I have information for the medicaid amount in two states and one of them is over 800 while the other is under 200. How would I just break the column for the state with 800 if that makes sense? Thanks!

1. So you could break it between 200 and 800 if you want. The size of the break isn’t important, just the scale of what you want to show above and below the chart

4. Jeet says:

for multiple bar chart to compare how we can proceed. for eg
we have two outputs
Data 2013 2015
Series 1 89 76
Series 2 54 35
Series 3 78 83
Series 4 3532 3510
Series 5 12 15
Series 6 23 18
thanks

5. Titto Augustine says:

This is one among the best Excel charts modification I have ever seen! Great job Alesandra!!

6. Jeff says:

This is one of the best things on the internet. You are a wizard. Thank you so much!

7. Sunit says:

Hi Alesandra,

I’ve had a look at your processes to create a broken column and bar charts and I’m still finding it difficult to emulate something similar to my set of data. What I need sounds simple but it seems pretty difficult to figure out, and no one seems to know how to get the right result.

I have a set of high percentage results that are all grouped closely together barring one result, crossing two years. The problem is, is that I want the horizontal axis to begin at 0% and then immediately start at 65% thereafter going onto 100%. Below is my set of data

Car 1 – 2014 – 97.31, 2015 – 97.51
Car 2 – 2014 – 91.15, 2015 – 97.80
Car 3 – 2014 – 96.90, 2015 – 98.22
Car 4 – 2014 – 97.07, 2015 – 97.63
Car 5 – 2014 – 98.03, 2015 – 98.84
Car 6 – 2014 – 97.63, 2015 – 72.59

It would be great if you can help me on this.

1. It’s difficult for me to visualize what you want. Can you tell me why the method posted isn’t working?

8. Diana says:

Hi ! thanks for this. I need to make a boxplot with a broken axis like this, do you think that I can do this using your boxplot method? or what would be the best way of doing it. Thanks!

1. Daniel says:

Hi, thanks for the post – really useful stuff. I’ve got the some problem though, need to do a scatter plot with the y-axis broken… any idea if that can be done, how?! Again thanks for your posts.

2. It can’t. At least not that I know of. Check out Jon Peltier’s excel blog. If anyone can do it he can!

9. Rohit says:

I have 200 values for which I needed to draw bar graph. The data was classified into a 10X20 sets and drawn on a 3 dimensional axis, with 10 X 20 rows along the X and Z axes, while the values along the Y axis. I am unable to break such a bar chart.

10. Thanks for this. I have a client who has asked me to prepare a report with multiple charts with a broken axis. The only thing is this is a very time consuming process. I am sure that I will get faster at in time, but it is still quite a bit of work. The big problem with a broken axis chart in my opinion is that visually it distorts the data. My client complain that he can hardly see the bars for certain elements, but what he fails to understand is that there is a good reason for this… the values were very low. All sense of scale is destroyed by this approach. No criticism of you tutorial of course. Thanks.

1. I completely agree. You might want to consider a panel chart instead. Much better in terms of visual aspect. Much less distortion! Great comment and thanks for stopping by!

11. Barbara says:

Hi, there! This post is fantastic! Really helped me! Do you know if it’s possible to create a Waterfall Chart with this broken column? All together?

Thank you so much!

1. Honestly I’ve never tried. I don’t think so though sorry! But by all means have a go! Let me know what happens…

12. Davi says:

Hi Alesandra! This post helped so me so much! I really appreciate it. Thanks from Brazil! =)