Create Excel Charts with Bands or Threshold in the background – [How To]

Just the other day I was teaching my ACCA F5 students about variance analysis and there we learnt that not every variance needs to be investigated and only those should be probed that breach a certain threshold which was explained with a chart. That’s where I had a light bulb moment to have it done in Excel. Following is the final chart we will be able to make by the end of this tutorial:

Understanding the basics

Simply put we are about to learn combo-charts i.e. a chart that has more than one type of charts. A little tweaking and we will get our desired result. There are two distinct things we want:

1. the bands that show a threshold or some meaningful ranges that help interpret or assess the data
2. the overlaying chart above the bands that plots information to be assessed on the basis of bands

As you can see in the above chart we have three areas of interest namely; above, target and below. Having bands makes it snap easy to know which year’s results are to be probed depending on the policy of the management. Usually any result that is above or below target is to be assessed. See why banded charts rock? So lets learn it!

Creating banded charts – Step by Step

Step 1: Lets say we have data of few years with actual production in column B and the target production in column C. With this information we can calculate the efficiency or percentage of target achieved. To this make a heading in cell D1 named Efficiency and put the following formula in cell D2 and drag the fill handle down to cell D10:

=ROUND(B2/C2*100,0)

This formula is calculating the efficiency in percentage by dividing the actual production in cell B2 over C2 and multiplying with 100. However, the result may not be a whole number and may be in decimals, we have enclosed it inside ROUND() function with an argument of 0 (zero) meaning no decimals allow and round to nearest whole number.

Step 2: As we have three thresholds i.e. above, target and below lets say, if

1. actual result is 0%-30% i.e. lower 30% in terms of target it will be considered Below.
   Make a heading in cell E1 named Below and in cell E2 put the value 30 and fill the same value down to cell E10.
2. actual result is 85% or above i.e. top 15% in terms of target it will be considered Above
   Make a heading in cell G1 named Above and put the value 15 and drag the same value down to cell G10.
3. actual result is 31-85% or mid 55% in terms of target it will be considered Average.
   Make a heading in cell F1 named Average and put the value 55 and drag the same value down to cell F10.

The above two steps have prepared our data and now we are ready to insert the chart.

Step 3: Select efficiency column with Low, Average and Above column using your mouse or keyboard.

Step 4: Go to Insert tab > Charts group > Bar chart > stacked column chart. You will have something like this (colours might be different):

We need to make a whole lot of cosmetic changes to it now!

Step 5: Right-click somewhere empty in the chart and click select data. Select data source box will open. On the right hand side click the edit button and select the years excluding the heading from the year column. Click OK and now you have the years on x-axis which makes more sense.

Step 6: Look for the legend and find the efficiency series and left click on that part inside the chart. This will select the whole series. Go to the design tab > type group > click change chart type button. Change chart type box will open. From there look for efficiency series and from the drop-down select line chart.

See the chart taking shape? 🙂

Step 7: Right click on anywhere on the bar chart and click format data series and reduce gap width to 0%. BINGO!

You have the banded chart with the actual results plotted above it. Our actual objective is already met but lack finesse. So lets work on it a little more.

Step 8: Right-click on the vertical axis and click format axis. Under axis option make maximum value to be 100. Hit enter key to confirm. Scroll down to Number options and from the category drop down select custom and in the format code input bar put this code:

0″%”

This will change the ordinary numbers to percentages and adds meaning to chart.

Step 9: Left click on the leftmost bar of the above series. This will select the whole series. Left click again to select only one bar of that series. Go Design tab > chart layouts group > add chart element dropdown > data labels > center. This will put 15 as the data label.

Step 10: Left click on the data label twice (see the selection box has four squares in four corners of selection) and go to the format tab > current selection group > click format selection button. From the label options check the series name and uncheck value.

Repeat the same process as described in step 10 for Average and Below series so that users can know what each area or band represent.

So! Banded charts in Excel made easy for you. I made some cosmetic changes like color, join types, and data labels to efficiency series and this are how I have mine:

Love the tutorial? Pin it!