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. Paste Special is found at the bottom of the dropdown menu on the Paste button, which is on the Home tab of the Excel ribbon. It is added as another stacked area series.
Right-click on the added series, choose Change Series Chart Type from the pop-up menu, and change the 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. Paste Special is found at the bottom of the dropdown menu on the Paste button, which is on the Home tab of the Excel ribbon.
One by one, right-click each added series, select Change Series Chart Type, and change each 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.
Joe Mako says
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.
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.
Jon Peltier says
Veekay –
Is this what you want?
Highlight Certain Time Periods in a Chart
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.
Thanks a lot for all your wonderful advice!
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.
Jon Peltier says
Bill –
Not sure what you’re looking for. Formatting in the worksheet?
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!
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.
Thanks in advance.
Jon Peltier says
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.
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 :/
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?
Jon Peltier says
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?
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
Jon Peltier says
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.
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
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
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?
Jon Peltier says
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.
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?
Thanks in advance for a short reply,
Harold
Jon Peltier says
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.
Jon Peltier says
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.
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.
Jon Peltier says
AnneBet –
Are the two bands on the columns separated horizontally? That means you need to change the gap width to zero.
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!
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.
Jon Peltier says
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.
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
Jon Peltier says
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.
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.
Jon Peltier says
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.
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.
Jon Peltier says
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.
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!
Jon Peltier says
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.
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
Jon Peltier says
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.
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.
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
Jon Peltier says
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.
Anonymous says
I believe the excel document you provided as a template at the top is no longer working.
Jon Peltier says
The download link still works, and the examples in the file are fine. What problem have you seen?
Natasha says
Hi Jon, really excellent tutorial! I am having difficulty though with horizontal bands. I have a large dataset where each line represents a visit date at a site and contains a column that indicates number of days between the visit date and the previous line’s visit date. (For example, for site A the number of days between a visit on Feb 1 and a visit on Feb 14 is 13.)
I have created a line with markers chart (Excel 2010). The range of days is on the Y axis. The X axis groups the dates by month and year. The plot area is the timeframes between visits for 5 sites. I would like to create one horizontal band that highlights the range of days between 28 and 42 all the across the x axis. The purpose is to be able to highlight how many visits fell within the acceptable range of 28 and 42 days.
I have added columns for min (static value of 28 on all rows) and max (static value of 42 on all rows). I copied these columns but when I go to Paste Special on the chart nothing happens. I have tested this out on a small dummy table from scratch and it works ok. unfortunately, I have to use the existing data table already there.
Jon Peltier says
Natasha sent me her workbook, and I discovered why she couldn’t add the stacked bars to construct a horizontal band across her chart. Her data is in a pivot table, and the chart is a pivot chart. Pivot charts are very powerful, but also limited in that they cannot plot any data which is not in the pivot table.
The options are to (a) make a regular chart from the pivot table data, then add the stacked column data from outside the pivot table, or (b) include the stacked column data in the pivot table data source, which Natasha had done, but due to the complexity of her data and the arrangement of fields in her pivot table, this did not result in a continuous band across the chart.
Geert says
Hi Jon, thank you very much for providing this very informative website !
I have a rather unusual question regarding the use of color in a chart, I guess.
I made a bar chart, where the vertical axis values are names and I would like to to give these names a colored, rectangular background (a different and very specific color for each value). Any idea how I can do that ?
Jon Peltier says
Hi Geert –
Try this approach: Individually Formatted Category Axis Labels.
Geert says
Hi Jon,
thank you very much for your help (and for your very quick reply).
This is just what I needed ! :-)
Sam says
Just a comment to say thanks for this – really helpful. You lost me at a couple of points, but your responses to the questions in the comments section got me back on track.
Jon Peltier says
Sam, where did you get stuck. I’ll try to do a little editing to help the next reader.
Sam says
Hi Jon,
The main one was finding ‘paste special’. I usually use right click to choose how to paste; I wasn’t aware of it being in the ribbon. Why there is a difference between what’s available in the ribbon and the right-click options we may never know…
The only other problem I encountered was changing the chart type for the series. That was more my unfamiliarity with formatting chart elements/separate series in Excel.
Thanks again
Jon Peltier says
Sam –
I’ve expanded the descriptions of Paste Special and Change Series Chart Type in the text, in an attempt to help future readers.
Bill Flarsheim says
Hi Jon,
I’ve seen your posts about Excel on Facebook, but never came to the site before today. This wasn’t exactly what I wanted, but it was close enough to get me going. I needed vertical bands to show different steps of a batch reaction behind the power curve for the agitator shaft. It came out pretty well.
Jon Peltier says
Hi Bill –
Long time no see. Glad this article got you started. If you have an example you’d like to share, I can work it into a new tutorial.
Cody says
What a wonderful resource!
Is it possible to have the ‘top of the band’ values change along the horizontal axis? or does each band have to have the same value like this? I’m trying to show expected growth over time in my chart…
Jon Peltier says
Cody –
You know I can do anything with charts, right?
Modify the data for the bands as shown below. the Clear band has Y value of 1 at both the left and right edge of the chart, and my three colored bands start at 1 on the left and end at 4, 7, and 10 on the right. Being stacked areas, they all have a left value of zero and a right value of 3.
So I created a stacked area chart, reformatted the bands with light colors, and 50% fill so the gridlines show through. I added my data, converted it to a line chart on the secondary axis, added secondary axes, switched the positions of the primary and secondary horizontal axes. Finally I deleted the secondary vertical axis and hid the primary horizontal axis.