# 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.

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.

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

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.

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.

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.

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

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.

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.

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.

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).

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

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

Add a secondary horizontal axis to the chart.

## Finishing the Chart

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

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.

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

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

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

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.

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.

## 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.

Here are the corresponding charts without (top row) and with (bottom row) formatting the plot area to shade above and below the 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.

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

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. veekay says:

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. veekay says:

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.

5. Bill Christian says:

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. Bob says:

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. Yevhen says:

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.

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. Yevhen says:

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. Ron Keen says:

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. Ron Keen says:

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.

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. Kara says:

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. molanpa says:

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. Harold says:

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?

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. AnneBet says:

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. Ryan P. says:

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. Austin says:

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. Jimm says:

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. Paul says:

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.

31. Mark says:

Hi,

I’m struggling with this tutorial.

I have one band I need to put into my graph that will fill in the values from -1.3 to 1.1

I followed the instructions up until the step where a secondary axis must be made (not sure how to do this on a Mac version of Excel) but continuing to read the tutorial it seems I need to add further axes which I don’t want to do. Right now I have two columns covering the span of -1.3 to 0 and 0 to 1.1 but I have no idea how to make these columns run completely horizontally from left to right.

32. Mark –

From your description it sounds like those columns go from Y=-1.3 to 0 and 0 to 1.1 on the primary Y axis and exist only on the first category of the primary X axis. You need to format both of these series so they are on the secondary axis (this will be somewhere in the Format Series dialog). Excel usually only adds a secondary Y axis, which you don’t really need, but you need to add a secondary X axis (on the Mac, this will be in Chart menu > Options, if memory serves). Format the series so the Gap Width is zero, and the bars will span the width of the chart.

33. Emily says:

Thanks a ton for the tutorial, this is fantastic. I’d like to suggest a few additions/modifications since, in practice, the explanation is not comprehensive.

1) I suggest you number the steps for quick reference!
2) The secondary horizontal axis doesn’t always show up automatically once the second series gets layered on. To add it, the “Layout” tab doesn’t exist in Excel 2013, rather, under “Design” there’s “Add Chart Element” drop down, out of which you further dig into the “Axes” menu.
3) The first stage for “Finishing the chart” gives no instructions – likely because you think the step is too simple to explain. However, I was stuck, and I recommend adding: Right click the horizontal axis and select “Format Axis” In the display screen, under “Axis position” select “on tick marks”.

Can’t believe how tricky this is to make such a simple graphic!

34. Emily –

Thanks for your suggestions. This post predates Excel 2013, and it’s hard to keep everything up to date. Note that you can also use the plus icon floating next to the chart to add and remove features like axes.

There’s actually an easier way to set up this kind of chart. I’m planning an update to this tutorial.

35. kyle says:

Can you help me with something that seems like it should be simple. When I go to Layout – Legend, a couple of things happen that I am not wanting:

1) It puts the legend over the chart. Typically when creating charts the system knows to shift the chart over and put the legend on the outside. But I cannot seem to even ‘resize’ the chart (normally grab the corner and resize). It just wants to move the entire chart.
2) The legend is wanting to give me an indicator for the colored horizontal bars. I am wanting the legend to only show indicators for the vertical bars.

Thoughts? Thanks, Kyle

36. Kyle –

What version of Excel?

1. When formatting the legend, one of the options is to Show the Legend without Overlapping the Chart.

2. To remove an unwanted legend entry, click once on the legend to select it, then click again on the legend entry, then click Delete.

37. Peter says:

Hi and thanks for your wonderful tutorials which I have used a lot. I am just having some trouble with this one (in Excel 2010). When I format the primary (left) vertical axis so the crossing axis crosses at the maximum value the bottom band in my graph disappears. I don’t know how to recover it.

38. Nath says:

HI, thanks for tutorial. I managed to set up the line graph with bands in excel 2013. For some reason I had a similar graph that was setup in excel 2007 but when opening in 2013 the band doesn’t go all the way across the graph (it only shows a small part on the left hand side). I have compared setup in both excel 07 and 2013 and everything seems the same. Any ideas? Thanks

39. Hi Nath –

There was once a problem in about Excel 2007, before any service packs, in which the bottom area was really shaped like a triangle. All areas stacked on top were fine. Then in Excel 2011 for Mac, the same problem appeared.

In both cases, and apparently in yours, the solution is to use another area fill series, with values of zero and formatted with no border or fill, and use this as the first series. It will be the one that’s messed up, and the visible ones will be fine.