Backgrounds and Fills

Fill Under or Between Series in an Excel XY Chart

Monday, September 9, 2013 by Jon Peltier 187 Comments

Monday, September 9, 2013 by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2023, All rights reserved.

Shading between plotted lines with a light color can enhance some charts. The shading may help to indicate a target range for the data.

I’ve written earlier tutorials about this topic, but I have had to change sequences of steps in the protocol because more recent versions of Excel were not as flexible with order of operations as Excel 2003 (RIP).

XY-Area Combination Charts

Recently in Shaded Quadrant Background for Excel XY Scatter Chart I showed how to generate a background grid of colored rectangles.

XY Chart With Colored Quadrant Background

This technique plotted the XY chart data on the primary axes and the Area chart data on the secondary axes. It also took advantage of a trick using the category axis of an area (or line or column) chart: when used as a date axis, points that have the same date are plotted on the same vertical line, which allows adjacent colored areas to be separated by vertical as well as horizontal lines.

To fill under or between XY series, we’ll make use of this same approach: XY data on primary axes, Area data on secondary axes, with a secondary date axis.

Fill Under One XY Series

Let’s start with the simple case of filling color below an XY plot. Simple data, simple chart of type Scatter With Straight Lines and Markers.

We need to do some calculations to produce data for the area chart series (see below). The min and max X for the XY chart’s X axis are entered in B11 and B12 (these are Excel’s automatic scale limits in the XY chart above). To provide reasonable resolution, we’ll scale the area chart’s X data from 0 to 1000. The formulas for the area chart’s X values in column D are simple interpolation formulas to properly rescale the data.

The X values are extended so that the first calculated X value in D5 is repeated in D4, and a zero is placed in D3; also the last calculated X value in D9 is repeated in D10, and the X max of 1000 is entered into D11. These duplicate X values will provide the vertical edges of the shaded region.

The formulas for the area chart’s Y values in column E provide the same Y values for both chart series. The Y values are extended by two cells above and below the calculations, which contain zeros.

The chart already contained the XY data in the first two columns of the data range (B4:C9). Copy the next two columns (D2:E11) , select the chart, then click on the Paste dropdown on the Home tab and choose Paste Special (below left). The Paste Special dialog will appear; make sure the same settings are selected as shown below right.

Here is the computed data and the chart. The added series is an XY chart type, like the first. Don’t change it yet.

Select the newly added series, press Ctrl+1 (numeral one) to open the Format Series Task Pane (Excel 2013) or the Format Series Dialog (2010 or 2007), and under Series Options, select Secondary Axis (below left). Excel adds a secondary vertical axis along the right edge of the chart (below right).

Excel adds a secondary vertical axis along the right edge of the chart.

In Excel 2013, click the plus icon beside the chart, click on the right-facing arrow beside Axes, and check the Secondary Horizontal box. In Excel 2010 or 2007, you’ll have to trudge up the the Chart Tools > Layout tab, click on the Axes dropdown, click Secondary Horizontal, and finally click Show Left to Right Axis.

Now all axes are present and accounted for.

Right click on the “Area” series (which is still of type XY), and choose Change Series Chart Type. In Excel 2013, the Change Chart Type dialog appears. Click the Chart type dropdown in the Area series row, and select Area or Stacked Area (doesn’t matter which in this case, since there’s only one area series). You actually could have switched the area series to the secondary axis in this dialog (as long as you do it before changing the chart type), but I usually forget that it’s become this simple.

In Excel 2007 and 2010, select Area or Stacked Area from the pop-up window.

The series has now been converted to an area series.

Select the secondary horizontal axis (top of chart) and press Ctrl+1 to open the Format Axis Task Pane or the Format Axis Dialog. Under Axis Type, select Date Axis (below left). You may also have to change the Base Unit of the axis to Days.

The area chart series is now perfectly aligned with the XY series. Since it’s plotted on the secondary axis, the area series fills the space between the secondary horizontal axis at the top of the chart and the data points. So it’s above the data, not below. But we’re clever enough not to panic.

Delete the secondary vertical axis (right edge of chart) and the secondary horizontal axis (top of chart). The fill moves into position below the XY plot.

Fill Between Two XY Series

The protocol for filling between two plotted lines is pretty much the same as above. We start with two XY scatter chart series, one above and one below the filled area. We will also need two stacked area chart series, one for the clear region below the lower XY line, and one stacked on top to fill between the XY lines.

Here is the data and the initial chart for the two XY curves. It takes up space, but I’ll leave the legend there until the end to help us keep track of which series is which.

The formulas to calculate the area chart X and Y values are like those used in the simpler example above. Note that the Bottom Area data is the same as the Bottom Line data, while the Delta Fill data is the difference between the Top Line and Bottom Line data.

Copy the area chart data (E2:G11) , select the chart, then click on the Paste dropdown on the Home tab, choose Paste Special, then make sure the settings are correct. The new data is added as additional XY series, which is fine.

Select one of the Area data series (still plotted as XY), press Ctrl+1 to open the Format Series Task Pane or Dialog, and choose Secondary Axis. Excel gives us the secondary vertical axis along the right edge of the chart. Repeat for the other Area data series.

Right click on the “Area” series (which is still of type XY), and choose Change Series Chart Type. In Excel 2013, the Change Chart Type dialog appears. Click the Chart type dropdown in each of the Area series rows, and select Stacked Area. You could have switched the area series to the secondary axis in this dialog (as long as you do it before changing the chart type).

In Excel 2007 and 2010, select Area or Stacked Area from the pop-up window. The chart probably looks like it’s broken right about now, but don’t panic. Right click on the other area series, choose Change Series Chart Type, and again select Stacked Area.

Select the secondary horizontal axis (top of chart) and press Ctrl+1 to open the Format Axis Task Pane or the Format Axis Dialog. Under Axis Type, select Date Axis. The area chart series are now perfectly aligned with the XY series. Since they’re plotted on the secondary axis, the Bottom Area series fills the space between the secondary horizontal axis at the top of the chart and the Bottom Line data points. So it’s above the data, not below. The Delta Fill area has filled the space between the XY lines.

Delete the secondary horizontal and vertical axes. The Bottom Area now fills below the Bottom Line, while the Delta Fill fills between Bottom Line and Top Line.

Format the Bottom Area series so it has no fill.

Finally, delete the legend.

Fill Between Overlapping Regions

It is possible to extend this approach even further. Suppose we want to fill between two pairs of lines. The following simple data has four series, min and max for set A and min and max for set B. We want to fill between Amin and Amax, and between Bmin and Bmax. If these areas overlap, we’d like to see both.

We can set up the area chart data below, where we have an area below Amin (“Bottom”), one between Amax and Amin (“Afill”), one between Bmin and Amax (“Blank”), and one between Bmax and Bmin (“Bfill”). The “Blank” area between Bmin and Amax extends from Bmin down to Amax, if Bmin is greater, or from Bmin UP to Amax, if Bmin is smaller.

I won’t go through the whole procedure, because it’s really the same as filling between two lines, above. The lines are shown on the left, A in blue, B in orange. The finished overlapping filled regions are shown on the right, again A in blue and B in orange. I’ve used a transparency setting of 50% so we can see gridlines and other data behind the area fills.

The four charts below show each area in turn with the others hidden. First, “Bottom” fills from the axis up to Amin, then “Afill” fills from Amin up to Amax, “Blank” fills from Amax up (or down) to Bmin, and finally “Bfill” fills from Bmin up to Bmax.

Fill Under a Plotted Line: The Standard Normal Curve

For an example of this technique, see another tutorial I’ve written, Fill Under a Plotted Line: The Standard Normal Curve.

Fill Under a Plotted Line: The Standard Normal Curve

Wednesday, January 30, 2013 by Jon Peltier 22 Comments

Wednesday, January 30, 2013 by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2023, All rights reserved.

Fill Below Standard Normal Distribution

A common request I get is to show someone how to apply shading under part of a plotted curve. This can be done easily using a combination chart. Combination charts may sound difficult, but they are actually pretty easy. We could use a line and area chart combination, but that’s not smart enough, so we’ll use an XY and area chart combination.

Area Chart Fill Below XY Plot of Standard Normal Distribution

Plotting the normal curve, in fact, plotting any function, is an easy task. Put the X values for the function into one column of a worksheet, then calculate the Y values in the next column, and plot this data. The data for the standard normal distribution is shown here (it extends down to row 65).

The X values in column A start at -3 in cell A5 and increment by 0.1 down to X=+3 in cell A65. This formula in cell B5 to calculate the standard normal distribution is copied into the range B5:B65.

=NORMDIST(A5,0,1,FALSE)

The data for the area chart (at least for the first cut) is shown below:

Cells C1 and C2 contain the minimum and maximum X values to fill below.

This formula in cell C5 is copied into C5:C65.

=IF(AND(A5>C$1,A5<C$2),B5,0)

First Cut at Filling Below a Plotted Line

This first exercise will show that, for this particular example, a line and area chart combination isn’t sufficient. For other uses, such a combination may be perfect.

This is the data in A4:B65 plotted in an Excel line chart. Note that the labels along the X axis do not denote numerical values. A line chart treats anything in the X values range as nonnumeric labels and plots data at equally spaced intervals along X.

This is the data in A4:C65 plotted in an Excel line chart.

Standard Normal Curve and Highlight Outline

This is how it looks when the “partial” line chart series is converted to area chart type.

Standard Normal Curve, Not Well Filled Under

This doesn’t work quite right. The edges of the shaded region are not vertical. Since the default text-style axis of a line chart (also used by area charts and column charts) plots each point above a category label, the edge of the shaded area slopes from the value at one category to the value at the next.

Filling Below a Plotted Line, Done Right

We’ll need a more clever way to get our chart, so we’ll use an XY and area chart combination.

We can take advantage of the behavior of the date-style axis of a line chart. This treats numerical X values as dates starting at 1 for January 1, 1900, and incrementing up one unit for each day. In fact, it starts at zero for January 0, 1900. Values are plotted horizontally according to date, so they no longer need to be equally spaced, if the days between points are not equal. You can even plot multiple values for the same date, and the points will be plotted along a vertical line. This is what we want for our shaded region.

Since the date axis only recognizes whole days as whole numbers, and ignores any fractions, we can’t use the same scale as the -3 to +3 for the normal curve, That is also no-go because the date axis only recognizes non-negative dates. But we can normalize our X values so they run from 0 to 10,000 (which is the same as 5/18/1927). The data table is shown below.

A few constants are included in the first two rows of the worksheet. E1, E2 contain min and max X values for the X axis of the normal curve. G1, G2 contain min and max X for highlighting under curve; change these numbers to change which region is shaded. F1 is the max for the area chart’s date axis (the minimum is zero).

The formula to calculate the standard normal curve is the same as in the previous example with the line chart. A5:A65 contains values of -3 to +3, incremented by 0.1, while cell B5 contains this formula, which is copied into B5:B65.

=NORMDIST(A5,0,1,FALSE)

The first two and last two rows of the area chart data range contain dummy values for the unshaded ends of the chart. The formula in cell E5 is:

=E$1

The formula in cells E6, E7 is:

=G$1

The formula way down in E57, E58 is:

=G$2

The formula in E59 is:

=E$2

Cells G5, G6, G58, G59 contain the value zero.

The range D7:D57 have values from zero to one, incremented by 0.02. I judged that this spacing has more than sufficient resolution for a chart 400 or so pixels wide.

The formula in E8:E56 computes the X values used to compute the normal curve for the area chart.

=G$1+(G$2-G$1)*D8

The formula in F5:F58 converts these X values into “dates” for the area chart’s X axis.

=(E5-E$1)/(E$2-E$1)*F$1

Finally, the formula in G7:G57 computes the standard normal values plotted by the area chart.

=NORMDIST(E5,0,1,FALSE)

Let’s start by selecting F4:G59 and inserting an area chart. Doesn’t matter if you use stacked area or unstacked, since there’s only one area series in the final chart, but I used unstacked.

Convert the default text-style axis to a date-style axis. Make sure it is scaled from zero to 10,000.

Convert X Axis from Default Text Axis to Date Axis

Copy the XY data in A4:B65, select the chart, and use Paste Special to add the data as a new series, with categories in first column and series names in first row. This data is added as another area series.

Copy XY Data, Select Chart, Paste Special, Add as New Series

Convert the new series to an XY type. If it doesn’t automatically go onto the secondary axes (and you don’t see axes on all four sides of the plot area), format the new series, and choose Secondary Axis.

Convert New Series to XY Type (Should Automatically Go onto Secondary Axis

Align the curve and the area by setting the secondary X axis (top of chart) so it ranges from -3 to +3.

Change Secondary X Axis (top) Scale to -3 Minimum to +3 Maximum

We want to use the top horizontal axis along the bottom of the chart, so we need to switch the horizontal axes. You have to format the corresponding vertical axis to set this. Format the secondary vertical axis (right of chart) so that the horizontal axis crosses at the automatic position, which means at zero.

Format Secondary Y Axis (right) so Horizontal Axis Crosses at Automatic Position (bottom)

Now format the primary vertical axis (left of chart) so the horizontal axis crosses at the maximum axis value.

Format Primary Y Axis (left) so Horizontal Axis Crosses at Maximum Value

Oh noes! the axis is where we want it, but now we’ve shaded ABOVE the plotted curve. This is because area charts and column charts start at the horizontal axis and rise upward or hang downward to reach the plotted value. Unless the corresponding vertical axis is missing, so select the primary vertical axis (left of chart) and click Delete. All series in the chart use the secondary Y axis if it’s the only one available.

Now delete the primay X axis (top), or hide it by formatting it so it has no line, no tick marks, and no tick labels.

Delete Primary X Axis (top) or Hide (No Line, Tick Marks, or Labels)

Finally, hide the secondary Y axis (right of chart) by formatting it so it has no line, no tick marks, and no tick labels.

Hide Secondary Y Axis (right): No Line, Tick Marks, or Labels

Remove the legend and write a descriptive chart title, and the chart is done.

As promised, changing the values in G1:G2 change where the curve is shaded. Instead of -1.75 to 0.75, let’s change the values to ± 1.5.

Or let’s highlight the curve from 1 to 2.

See how easy it is to use a combination chart to highlight some or all of the area under a curve.

Excel Charts With Horizontal Bands

Monday, September 19, 2011 by Jon Peltier 58 Comments

Monday, September 19, 2011 by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2023, All rights reserved.

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.