Broken column and bar charts

broken column

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:

data tableAs 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:

normal columnIt’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:

broken column

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.

scale break

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.

name boxI’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:

New data table

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.

stacked columnExcel will create a chart as shown below:

graph 1Right 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.

graph 2Make 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:

shape

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:

graph 3

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:

New 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:

graph 4Right 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:

secondary axisRight 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.

XY scatterThe chart will again update:

XY scatter chartRight 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.

graph 5

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.

tick marks

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

graph 6

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:

data labels chart

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:

broken columnbar chart

You can download the sample worksheet here.  Any questions, please don’t hesitate to post a comment!

+Alesandra Blakeston

How to create a JAWS chart

Jaws (1)

As a big movie fan, I can tell you that no horror or thriller has ever had as much effect on me as watching JAWS as a child. I remember begging my parents to let me watch it, and I remember vowing that I wouldn’t be scared. Guess what? I was terrified. There’s something about that those two bass notes repeated over and over faster and faster that is just chilling to the soul. I even made my mum check under the bed twice before I would go to sleep. (Even though I was old enough to know that sharks needed water to breathe, and of course there was no water under my bed.)

JAWS

That being said, a JAWS chart is not so scary.  In fact once you know how, it is actually very very easy!  What is a Jaws chart? A Jaws chart allows you to compare two sets of data.  Both data sets are plotted as bar charts, but one is placed on the bottom x axis, the other on the secondary top axis.

JAWS chart

Step 1: Set up the data

So first you need 2 sets of data.  This could be the sales of 2 products over time; it could be sales of the same product over two different years.  But in the end, you need two columns of data with a third column to make the labels for the horizontal axis

data table

Step 2: Create a normal column chart

Highlight your three columns of data and click on Insert > Column > 2D Clustered Column chart

2d clustered

You should have a chart like the one below:

first draft

Step 3: Move the second series onto the secondary axis

Left click on the second series and press Ctrl+1 to bring up the formatting window.

format data series

Move the chart onto the secondary axis and reduce the gap width to 50%.  Before closing the window, left click on the first series and reduce its gap to 50% as well.  Then close the window.

The chart should now look like this:

second draft

Step 4: Add a secondary vertical axis and adjust

With the chart selected, click on the layout tab.  Then click on Axes > Secondary horizontal axis > Show left to right axis. The chart should update as shown below:

third draft

Double click on the secondary VERTICAL axis.  In the menu box that appears, adjust the axis options so that the “values are in reverse order” and the Horizontal axis crosses at axis value “0”.

adjust axis

Then adjust the maximum scale value to a value that allows you to see the up and down columns without them overlapping.  Note: You will also have to adjust the left primary vertical axis to the same amount.  In my case, I changed both values to “100”.  The graph should then look like the one below:

fourth draftStep 5: Format the chart

Click once on the secondary horizontal axis and then delete it.  Repeat the procedure for the secondary vertical axis and the primary vertical axis.  Adjust the fill colours of each series as you wish.

Right click on the first series and choose add data labels.  Repeat for the second series.  Then left click on the first set of data labels and press Ctrl+1.  Change the label position to inside end.  Then click on the home tab and adjust the font colour and size.  Repeat for the second series and then press the close button.

data labels

Your chart should now look like the one below.  What do you think?  Can you compare the two series easily?  You can download the excel file here, or view it on my Sky Drive below.

+Alesandra Blakeston