How-to Create a Dynamic Banding on an Excel Line Chart

image

This dynamic shading or banding chart is frequently used in science and in Company Dashboards.  It allows the user to quickly see if their data is within tolerance or a specified range.  This can be easily done in Excel for a line chart and the principle holds true for many types of other Excel Charts.  Here is how to do it.

The Breakdown

1) Add Fields for Minimum and Maximum Horizontal Band Range

2) Setup Your Data and Add 2 Series with Formulas





3) Create an Excel Stacked Column Chart or Excel Stacked Bar Chart

4) Change the Inventory Data Series to a Line Chart

5) Change the Fill Color of the No Fill Series

6) Chart Clean Up

 



Step-By-Step

1) Add Fields for Minimum and Maximum Horizontal Band Range

Since we are going to make a dynamic confidence band for our line chart, we need to be able to specify the band range in our Excel Graph.  This is easy enough.  Just designate to cells in your worksheet for this purpose.  One is for the minimum of the shading range and one is for the maximum.  Users will predetermine the confidence interval that they wish to shade using these two cells.image

2) Setup Your Data and Add 2 Series with Formulas

Next, you need to set up your data and have two new series that will be used for the horizontal banding in your Excel Chart.  The series should mimic the data series that you want on your horizontal axis.image





As you can see in the picture of my chart data table above, I have created 2 new series that mimic the inventory line series.

One is titled NoFill and it should be set to Cell =$D$1 (the minimum for the horizontal band).  What this will do is set the bottom of our shading area.

The other one is titled Band.  This is equal to =$D$2-$D$1 so that we can get the size of the confidence band for the Excel Chart.

Alternately, I would also include this formula in cell E2 to check for errors if the Max is less than the Min:

E2 =IF(D2<D1,”ERROR – MAX < MIN”,””)image



3) Create an Excel Stacked Column Chart or Excel Stacked Bar Chart

Now lets make the chart.  Highlight your data range:image

And then go to the Insert Ribbon and select the Column Button in the Charts Group and then choose Stacked Column Chartimage

Your chart should now look like this:image

4) Change the Inventory Data Series to a Line Chart





Now select the Inventory series and right click on it and select Change Series Chart Type so that we can change this series to a line chart.  You can also select the series and then choose the Change Chart Type button from the Design Ribbon:image

Then choose a Line Chart from the Chart dialog box:SNAGHTML715fc64e

Your resulting chart should now look like this:image

In a few easy steps, we are very close to a horizontal banding solution.

5) Change the Fill Color of the No Fill Series



All we really need to do is close the gaps and hide the NoFill series.  So lets do that now.  Right click on the NOFILL column and then select format data series.  Alternately, you can select the NOFILL column and then press CTRL+1image

Then from the Format Data Series dialog box in the Series Options area, simply change the Gap Width to NO GAP or 0%:SNAGHTML71638652

Then from the Fill area, change the setting to NO FILL and then click OK:SNAGHTML71670300

Your chart should now look like this:image

Looks like what we want.  Lets just make it prettier.





6) Chart Clean Up

Remove the Horizontal Major Gridlines.

Delete the Legend

Add a Chart Title = Inventory

Your final chart should now look like this:image



Looks awesome.  Now you too know how to make a Horizontal Confidence Band or Horizontal Shading in your Excel charts and graphs.  Now go spruce up those Company Dashboards.

Video Demonstration:

 

Let me know if this was helpful in the comments section.  Also, don’t forget tot sign up for the RSS Email feed so you get the next post right in your email inbox.





Steve=True





16 COMMENTS

  1. Thanks for help w/ the banding excel line chart! I spent several hours trying to figure this out. You are awesome, you solved my problem! Thank you so very much!

  2. Thanks for that solution, I’d been beginning to wonder if it was possible to do at all. Used two bands for the normal ranges, Systolic & Diastolic for blood pressure monitoring and can now plot my results against the bands background. Excellent solution!

  3. Great example and easy to follow. How about this?

    If you have an executive dashboard, 25 graphic based charts showing sales across regions, then to put a band on each one then you would have to have two additional series to each chart to complete. The data you are graphing is from a pivott table where adding 50 additional rows to add a band becomes almost impossible. Any recommended approach to this and each band is a different range. thanks

  4. Hi

    Thanks for the post. This chart looks awesome. Is it possible to set y axis labels to show only the values from the bar chart?
    I’m interested to show only two tolerances values from the on the graph, nothing more.

    • Hi Charlie,

      If you are talking about Labels, then yes, you can show as little or as many as you want. Just select the individual series or data point before clicking on the Labels. If that doesn’t work, then you can delete the un-needed ones by selecting them and pressing the delete key.

  5. Hello,
    Very nice work. However I am facing a problem:
    When I try to convert the line series to a column, all series become columns. How do you suggest I overcome this?

    • Hi Manoj, you can try and move the line to the 2nd axis prior to changing the chart type. Then change the chart type. See if that helps with your problem.

  6. Great info.
    I am really having an issue trying to have 2 data rows.

    Does anybody have a file I could use please.

    I can follow the above & it works well but adding a second line doesn’t make it dynamic & spent too long trying to fix it.

LEAVE A REPLY

Please enter your comment!
Please enter your name here