In Horizontally Banded Chart Background I showed how to create colored horizontal bands in the background of a chart.

I first wrote up that tutorial back when I was using Excel 97, and the protocol remained the same for Excel versions through Excel 2003. The protocol is pretty much the same in Excel 2007, too, but the rearrangement of the interface into the Office 2007 ribbon has made the old instructions difficult to follow.

Let’s use the same data as in the previous example (with recent dates). This is the data for the main series of values in the chart. The average and standard deviation for the values in B5:B21 are displayed in B1 and B2.

The data for the bands is given below. We’ll use stacked area type series. The value in E2 is three sigma below the mean, so the formula in E2 is =$B$1-3*$B$2. The thickness of each remaining band is one standard deviation, so the formula in F2:K2 is =$B$2. The formula in E3 is =E2, and this is copied and pasted into F2:K2.

The data in A4:B21 is used to make a simple line chart. The Y axis min and max limits have been set to fixed  values of 0 and 22, so they don’t change as we’re adding and formatting the rest of the series.

Copy D1:K3, select the chart, and use Paste Special to add the data as new series, with data in columns, categories in the first column (but don’t replace existing categories), and series names in the first row.

These series are added as line chart type series, but we can’t see them (In 2003 we could, so the next step was easier). The values 0 and 1 on the time scale axis are plotted off scale, so we will move the series to the secondary axis. We can’t click on the series to format them, so we have to use the Chart Element control to select them. This control is present at the left edge of the Layout and Format tabs, the last two Chart Tools tabs.

All of the chart elements are listed in this dropdown control. You can select the hidden series using this control.

One by one, select one of these new series, press Ctrl+1 to open the Format Series dialog, and on the first tab you see, select the Secondary Axis option. The line chart series are now visible. The lower series shows the -3 SD data The upper series is really all of the other added series, which coincide because the Y values are all the same.

When Excel assigns a series to the secondary axis, often it only adds a secondary value (Y) axis, which we see at the right edge of the chart. We need the secondary category (X) axis to make the background work. On the Chart Tools – Layout tab, click the Axes dropdown (near the middle of the tab), select Secondary Horizontal Axis, and choose the Show Left to Right Axis option. (Why does it feel like they’ve dumbed it down a little too far?)

One of the changes in Excel 2007 that decimates productivity and efficiency is that you cannot use the F4 key (shortcut for Repeat Last Action) here. In 2003 you could assign one series to the secondary axis, then select another series using the arrow keys and press F4 to quickly assign is to the secondary axis.

We want the 0 and 1 of this secondary axis to extend to the edges of the chart, but we have to wait until after the next step.

We also want to convert the line chart series into stacked area chart series. One by one, select each added series by right clicking, and choose Chart Type from the pop up menu. Select the Stacked Area subtype of the Area Chart type. Do this seven times to get the stacked colored bands.

Here is another situation in which you cannot use the F4 Repeat Last Action shortcut. In 2003 you could change the chart type of one series, then select another series using the arrow keys and press F4 to quickly change it to the same chart type.

Now we can fix the secondary category (X) axis. Select the axis, press Ctrl+1 to open the Format Axis dialog, and at the bottom of the first tab you see, select the On Tick Marks option for Position Axis.

Now let’s clean up the axes. Format the secondary category (X) axis so that no parts of it are visible: choose None for major and minor tick mark type and for axis labels, all on the first tab, Axis Options. On the Line Color tab, choose No Line. Select the secondary value (Y) axis and press Delete. This forces the stacked area series to use the primary value (Y) axis. Format the dates in the primary category (X) axis so they are easier to read.

Those colors have to go. Here is the chart with stacked area series fills formatted with the Standard Colors in the Chart Tools – Format tab’s Shape Fill dropdown. The lowest area series is formatted with no fill, and the plot area is filled with red.

Those are a little too bold, so use the More Fill Colors option to tone down the fills. I used the Standard Colors as a basis, then on the Custom tab of the Colors dialog, switched to the HSL color model, and changed the Luminosity setting to a higher number in the range of 180 to 200.

Related Posts:

• Clustered-Stacked-Column Combo Chart With Lines
• Fill Below an XY Chart Series (XY-Area Combo Chart)
• Clustered-Stacked Bar Charts
• Order of Legend Entries in Excel Charts
• Candlestick Alternative: Individually Colored Up-Down Bars
• Fill Between XY Chart Series (XY-Area Combo Chart)
• Clustered-Stacked Column Chart with Target Line
• Clustered-Stacked Column Charts with Vertical Separators
• Clustered-Stacked Column Charts
• Area Chart – Invert if Negative