Excel Charts With Horizontal Bands

You may want to highlight certain ranges of values in a chart, to make it easier to judge the values in the chart. For example, you may want to highlight “good” values with green or blue, and “bad” values with red. Or you may want to highlight the standard deviation bands in a control chart. This banding is not a native feature of Excel, but you can use standard chart elements (i.e., stacked columns or in this case stacked areas) to achieve this effect. Read on to learn how.

Chart With Horizontal Bands

This technique works with XY (Scatter), Column, and Line charts, and all of the chart triptychs in this tutorial show these chart types in that left to right order. Download the annotated workbook HorizontalBandWorksheet.xlsx if you want to follow along in an existing workbook.

Original Charts and Data

This screenshot shows the data used in this exercise. Column A has the X values for the XY (Scatter) charts, column B has the X labels for the Column and Line charts, and column C has the Y values for all charts. Column E lists the values at the tops of the bands, from the bottom up, starting with the top of the blank area below the lowest band. Column F has a label for these bands, which I’ve simply filled with the intended colors of the bands. Columns G and H have the same values, G2:H2 containing the lowest value from F2 (=F2), and the rest of the cells containing the band heights (e.g., =F3-F2). These heights will be used to populate the values of stacked area charts, which will serve as our bands.

Chart With Horizontal Bands - Data

Here are the XY (Scatter), Column, and Line charts of our original data.

Original Chart Without Bands

You can start with a chart containing your original data, and add the bands, or you can follow a somewhat easier approach which starts from scratch, building a banded chart, then adding the original data. I’ll start the tutorial with the second approach. Skip ahead to Adding Bands to Existing Chart if you are not starting from scratch.

Starting From Scratch

Select the data for the bands (the shaded range F1:H8) and create a stacked area chart (not stacked 100% area), with series data in rows. The charts all look the same for a few steps, until we actually have a series that has either XY, Column, or Line type.

Chart With Horizontal Bands - In Progress

This is as good a time as any to apply your fill colors to the bands. It is best to use light shades, so the bands do not overwhelm the original data.

Chart With Horizontal Bands - In Progress

Copy the original data, select your chart, and use paste special to add the data as a new series. It is added as another stacked area series.

Chart With Horizontal Bands - In Progress

Change the added series to the desired chart type (XY Scatter, Column, or Line). Now the charts assume their unique appearances.

Chart With Horizontal Bands - In Progress

Format the new series and assign it to the secondary axis. This was already done in the first chart when the added series was converted to XY type.

Chart With Horizontal Bands - In Progress

Add a secondary horizontal axis to the chart. This was also done in the first chart when the added series was converted to XY type.

Chart With Horizontal Bands - In Progress

The rest of the protocol is the same as for the case where you start with your original chart and add the bands. Skip the next section, and follow the instructions in Finishing the Chart below.

Adding Bands to Existing Chart

Copy the range with the band data (F1:H8 in our example), select the chart, and use Paste Special to add the data as new series, with data in rows.

Chart With Horizontal Bands - In Progress

One by one, change the chart type of the added series to stacked column (not stacked 100% column). This is the step that is so tedious, you’ll wish you had started from scratch (the above protocol).

Chart With Horizontal Bands - In Progress

Apply your fill colors to the bands. It is best to use light shades, so the bands do not overwhelm the original data.

Chart With Horizontal Bands - In Progress

Format the original series, and assign it to the secondary axis.

Chart With Horizontal Bands - In Progress

Add a secondary horizontal axis to the chart.

Chart With Horizontal Bands - In Progress

Finishing the Chart

Format the primary (bottom) horizontal axis so the crossing axis is positioned on tick marks.

Chart With Horizontal Bands - In Progress

The next four steps switch the axes left and right, top and bottom.

Format the primary (left) vertical axis so the crossing axis crosses at the maximum value.

Chart With Horizontal Bands - In Progress

Format the secondary (right) vertical axis so the crossing axis crosses at the automatic position.

Chart With Horizontal Bands - In Progress

Format the primary (top) horizontal axis so the crossing axis crosses at the maximum category.

Chart With Horizontal Bands - In Progress

Format the secondary (bottom) horizontal axis so the crossing axis crosses at the automatic position.

Chart With Horizontal Bands - In Progress

Hide the primary (top) horizontal axis by formatting it so it has no line, no axis ticks, and no tick mark labels. Select the primary (right) vertical axis and press Delete.

Chart With Horizontal Bands - In Progress

You were either done in that last step, or you can add a fill color outside (above and below) the bands by formatting the plot area with the desired fill color.

Chart With Horizontal Bands - Finished

Negative Data and Charts

What makes this technique more flexible than that in my original tutorial is that stacked areas can span the horizontal axis without the strange behavior of stacked column charts. To illustrate, here is the same data as above, offset so it spans the horizontal axis.

Chart With Horizontal Bands - Negative Data

Here are the corresponding charts without (top row) and with (bottom row) formatting the plot area to shade above and below the bands.

Chart With Negative Horizontal Bands

Arbitrary Data and Charts

In the examples above, the bands are uniform in width, and each band touches the bands on either side without gaps. This isn’t necessary, of course. The bands can be any width, and there can be gaps between adjacent bands (the gaps being transparent bands). The worksheet below has the same original data as above, but the bands have arbitrary top and bottom values, and there are a couple gaps.

Chart With Horizontal Bands - Arbitrary Data

The chart doesn’t care what the band values are or how we format them.

Chart With Arbitrary Horizontal Bands

Peltier Tech Chart Utility

Comments

  1. Jon, Thank you for another great tutorial.

    For fun, I remade this in Tableau as well :)
    http://public.tableausoftware.com/views/databands/Dashboard
    Thankfully, Tableau provides a reference band feature that can be based on a formula.

  2. Hi Jon,

    You have a great website, thanks for all the knowledge you have shared over the years.

    I am trying to create a vertical-banded glidepath chart, with each “color band” being a date range (within a quarter) comprising one “wave”, and one or more performance metrics (e.g, Customer Satisfaction, Revenue) being the series plotted as line curves across these “waves”.

    If you can add an example of doing something like that to this page, it would be really useful, as charting the performance metrics over a period is a common requirement for many people.

  3. Veekay –

    Is this what you want?
    Highlight Certain Time Periods in a Chart

  4. Jon, that was close to what I was looking for, and it is a good insight too.

    If the time periods on an axis are asymmetrical, color-shading them vertically using a MOD formula on the date might not be the answer.

    I had also understood from another of your articles that the series for the background color bars can be coded as percentages of the width of the chart. This worked for me and I was able to build the chart exactly the way I needed.

    Thanks a lot for all your wonderful advice!

  5. I am completely new to Excel and not very computer savy. I have made a list of my movies in alphabetical order in column A and the actors in column B. I want to add a horizontal line separating each movie and a vertical down the center separating A from B. The program has these lines but they do not print.
    Can anyone help me?
    Thank You and Merry Christmas to all.

  6. Bill –

    Not sure what you’re looking for. Formatting in the worksheet?

  7. I have set up a spreadsheet in which I can specify a date range within a long time series and the graph will automatically adjust horizontally and vertically to display the data. This means the vertical axis is set for automatic, so max and min values depend on the data range selected. When I add color bars per your method, I can’t see any way to sync the max and min of the display values of the color bars to those of the data. I can work around by always reformatting the color bar vertical axis whenever the data max/min changes, but that gets pretty tedious.

    Any ideas on how to fix?

    Thanks!

  8. Jon,
    thank you for a great tutorial.

    I am interested if you could suggest how I can add horizontal bands in case my chart is already using both axis – primary & secondary. I tried but not successfuly.

    Thanks in advance.

  9. Yevhen –

    You’ll have to figure out which set of axes you can put the bands on, and modify the protocol accordingly.

    Is this a case where the data would be better in two charts (or in a panel chart) than on two axes? This is true of 99% of the dual axis charts you’ll ever encounter.

  10. Jon,
    thank you for feedback.

    I think about actions I have to do with the chart and it’s clear, more likely i’ve missed some step previously, i’ll try the actions once again.

    Unfortunately, I am not the owner of this report, so I am not authorized to divide two axes in two charts :/

  11. Wow, has anybody actually tried this?

    I’ve spent 3 hours meticulously following these instructions, and it doesn’t work. I am using Excel 2010 … Did Microsoft just make this impossible to do with 2010?

  12. Ron –

    I wrote this tutorial using Excel 2007, and the protocol should be the same in 2010.

    Where does your approach fail? Are you starting from scratch with a banded chart and adding your data, or are you adding bands to an existing chart? Does your value axis include negative and positive numbers?

  13. Jon,

    I have, in fact, followed your examples for both ways (start with banded data first, and start with existing chart). I have even started with your data and followed your instructions to try to come up with the charts that you do. No dice. I’ve also tried to apply the procedure to my data … no dice. I only have positive data, like your example.

    I have also had tremendous difficulty with Excel 2010 screwing up the graph when changing from primary to secondary axis. It looks fine when everything is referenced to the primary axis, but as soon as I change it to a secondary axis, the values (bands) get placed on the graph in random order. I’m trying to do a red, yellow, and green band (good, caution, bad) for my bands. Once I change them, the order gets mixed up, and it’s always a different order. Some of your instructions are also set up significantly different than Excel 2003 (what I used to use), so some of your instructions don’t have a 1:1 mapping to Excel 2010. I have had to guess, and I may be guessing incorrectly, but I’ve tried to do the best I can with what MS has given.

    Regards,
    Ron

  14. Ron –

    I opened a new workbook in Excel 2010 and entered the data I used when writing this tutorial.

    I created charts both ways: Starting with an XY, line, or column chart and adding bands, and starting with a chart with bands only, and adding XY, line, or column series.

    I followed the steps as written, conscious not to add any steps or leave any steps out. The charts all ended up exactly as expected, that is, exactly as described in this article.

    I’ve annotated and uploaded this new workbook, which you can download from HorizontalBandWorksheet.xlsx.

    If you want, send your workbook to me at jon-at-peltiertech-dot-com. I’ll work it through for you.

  15. Robert Manning says:

    Hi Jon,

    I’ve followed your instructions and can’t make this work. I can create the two graphs separately, but can’t get them to work together. The graph I’m trying to create is one that shows the Customer Satisfaction Index over 4 quarters which is just a single average point per quarter. In the background I’m trying to create 3 colors. Red from 0 to 50% for Customer Dissatisfaction, Yellow from 50 to 85% for Customer Satisfaction and Green from 85 to 100% for Customer Delight. As I said, I can create the area chart and the line chart separately, but can’t manage to get these to work together properly.

    Any help would be greatly appreciated.

    Thanks
    Bob

  16. Hi Peltier,

    Excellent tutorial. I’m working on a gantt chart with pointers to show actual dates and I want a background with horizontal bars of predefined colors. I’ve got both the gantt chart and pointers up already (I’m using “Line with Markers” chart for the pointers and “Stacked Bar” for the gantt chart). Only problem is when I add a new chart for the horizontal bars (I use “Stacked Area”), it seems to fill the area within the pointers and not the whole chart. How do I go about adding horizontal bars to my chart independent of the two series since I believe these two series have already fully utilized the primary and secondary axes.

    Thanks,
    Kara

  17. Thanks for the great post. I am using Excel 2007 and the graph plotting part works out fine. But I can’t seem to figure out how to shade the bands. Did you change the colour of the bands one by one or is there any workaround?

  18. Molanpa –

    Yes the bands need to be formatted individually. Right click on a band, and choose Format Data Series. Choose No Line for Border Color, and click Solid Fill on Fill to choose your fill colors.

  19. Hi John,

    Using you horizontalbands.xlsx, I managed to get a very nice graph with horizontal bands in Excel 2003. However, then I encountered one problem. When a colleague of mine opened the file with Excel 2010 the horizontal bands had disappeared. I cannot figure out the reason. Do you have any idea?

    Thanks in advance for a short reply,
    Harold

  20. There are certain incompatibilities between 2003 charts and 2007/2010 charts. I don’t know offhand what went wrong in your chart.

    Does the original HorizontalBandWorksheet.xlsx work on both computers? If so, you may have unintentionally broken something when you made changes to the chart to accommodate your data.

  21. Harold replied:

    The original HorizontalBandWorksheet.xlsx works on both computers.
    Today I started all over but now I created it first in 2010.
    The conversion to 2003 keeps the graphs as designed in 2010.

    The root cause of not getting a proper conversion from 2003 to 2010 is still not known to me; but via this new way I don’t have to know it anymore.

    My follow-up:

    The incompatibility of charts between Classic (2003) and New (2007-2010) Excel is just something we have to deal with. I’m not surprised that 2003 can read the 2007/2010 charts. The charting infrastructure in 2003 is smarter, more flexible, and more robust than the new charting engine introduced in 2007.

  22. Thanks for the step-by-step explanations. I’ve used the “adding bands to existing chart” option and it works perfectly. I only found that using the stacked area chart type option (step 2) works better than the stacked column chart type. With the stacked column chart type, i get two seperate bands of colour, wheras the stacked area type provides the single band between the y-axis i want.

  23. AnneBet –

    Are the two bands on the columns separated horizontally? That means you need to change the gap width to zero.

  24. This blew my mind! Really incredible–very impressed you figured all that out, and very grateful you documented it!

    One question–my chart is tied to a pivot table, and it works beautifully, though I’ve noticed that the width of bands remains constant once it’s set up (I imagine driven off the original data pasted in). Using your example data, the x-axis categories are A through F…imagine if you ran a pivot, and then you had A through G…the G value isn’t displayed because the bands aren’t wide enough. Likewise if you only have A, B, and C, they are all displayed but the bands continue on to the right for the original width (even though you don’t need that extra width). Is there any way to make the width dynamic and tied to the number of categories in the data?

    Again, thanks so much!

  25. Hi Jon,

    Thanks for the helpful tutorials, I’ve really enjoyed them. Unfortunately, I can’t seem to get past this problem that is driving me crazy out of my mind. I get to the step where you say to copy, then “paste special” the data into the chart. But there is NO paste special option! I’ve tried it every which way I can think of, including restarting excel in “safe mode” and everything else. Please help, I can’t figure out what I am doing wrong.

  26. If you click on the little down triangle next to Paste on the Home tab of the ribbon, one of the options is Paste Special.

  27. Would it be possible to add colored bands to a chart like this URL, so that 1 band corresponded to each position? This chart has more positions than I need – I’m thinking about 4. Example: -5, 5, 10, 15 so that 1 position is below the horizontal axis, and the other 3 are above the horizontal axis at increasing (but equally spaced) distances. Could I then add 4 bands to this chart so that 1 band extended the width of the chart below the horizontal axis all the way across (effectively providing a background color for all milestones at the -5 position) as well as 3 distinct bands above the horizontal axis – each also stretching the width of the chart – 1 band at the +5 position, another at +10 and the third at the +15 position? The position could then be used to group similar milestones together (e.g., iteration start dates at -5, my milestones at +5, another person’s milestones at +10, and dependencies at +15)? That would be a very powerful visual.
    http://office.microsoft.com/en-us/templates/timeline-with-milestones-TC102930035.aspx

  28. Jimm –

    Sure, what you want isn’t too tricky. In this article I already show how to get some bands below the axis, and also how to use bands of arbitrary thickness.

  29. Hi,

    Would this chart type be achievable with data on the secondary vertical axis? I’m trying to plot a % against a £ value, with the common relationship of red / amber / green / gold target levels. I’d love to use this chart to plot them so I can show the relationship between the two relative to their position on colour based plot area, I’m struggling to see how I can achieve this though.

  30. Paul –

    I’ve set this up so the data is plotted on the secondary axis and the bands on the primary axis.

    If you use a line chart, then you could plot one line and all bands on the primary axis, and the other line on the secondary axis.

Subscribe without commenting

Trackbacks

  1. […] falls in, but it will never let me use it (greyed out). this is the tutorial I was trying to use: Excel Charts With Horizontal Bands – Peltier Tech Blog I have provided a minimum working example for you to look at if it helps, showing what I am trying […]

  2. […] document.write(''); This tutorial might be easier: Excel Charts With Horizontal Bands […]

Write a Comment

I welcome comments from my readers. If you have an opinion on this post, if you have a question, or if there is anything to add, I want to hear from you. Whether you agree or disagree, please join the discussion. If you want to include an image in your comment, post it on your own site or on one of the many free image sharing sites, and include a link in your comment. I'll download your image and insert the necessary html to display the image inline.

Subscribe without commenting

Peltier Tech Chart Utility

 

Create Excel dashboards quickly with Plug-N-Play reports.