Adding shaded areas to normal curves like this is a challenging task in Excel charting. But once you know how, it’s not difficult to do.
There are two reasons the task is challenging. First, the method is difficult to figure out in the first place. And second, different versions of Excel change the way you work with error bars, which is the feature you use to assign the shading.
In An Introduction to Excel’s Normal Distribution Functions I presented several figures somewhat like the one above. This article explains how to create the figures in Excel.
Even if you have no particular reason to chart a normal curve, you might find the techniques interesting, because you might need to use similar techniques when you create other charts.
Set Up Your Control Worksheet
In general, you create a normal curve just as you create any other chart in Excel: You set up the data and then chart it.
To set up the data, open a new workbook with at least three worksheets. Name one of these Control, one Data, and the other Report.
In the Control worksheet, first set up the area shown here.
After you enter the labels in column A, assign them as range names in column B. To do so, select the range A2:B8 and choose Formulas, Defined Names, Create from Selection. Ensure that only Left Column is checked, then choose OK. Do the same for the range A11:B14.
The numbers in the Settings section are values; enter them as shown. Here’s a short explanation of each:
Mean. The average value of your data.
StdDev. The Standard Deviation of your data.
NumRows. The number of rows of data to be plotted.
Zmin. The smallest number of standard deviations to be plotted.
Zmax. The largest number of standard deviations to be plotted.
PctShade. The percentage of the shaded area from the left side of the curve, from .0001% to 99.999%.
PctClear. The percentage of the unshaded area from the left side of the curve, from .0001% to 99.999%.
(Here’s another way of looking at the PctShade and the PctClear: The PctShade is the maximum (right-most) probability that will be shaded beneath the curve. PctClear is the minimum (left-most) probability that will be shaded.)
The numbers in the Calculations section are returned by formulas:
ShadeLeft =NORM.S.INV(PctClear)*StdDev+Mean
The NORM.S.INV function is the inverse of the NORM.S.DIST function. Given the probability (PctClear) that a variable is within a certain distance of the mean, it finds the z value…the number of standard deviations. To calculate the extent of the shade at its left side, we multiply the NORM.S.INV result by the amount of the standard deviation, then add the mean.
ShadeRight =NORM.S.INV(PctShade)*StdDev+Mean
We use the same logic with the PctShade area as for the PctClear area. By doing so, we define the right side of the shaded area.
CurveMin =Zmin*StdDev+Mean
This formula defines the ending point for the left side of the curve.
CurveMax =Zmax*StdDev+Mean
This formula defines the ending point for the right side of the curve.
Set Up Your Data Sheet
The Sequence value in column A is a counter, from 1 to the number of rows of data. The counter is for convenience only; it’s not used in the formulas. In this example, I used 100 rows.
Enter 1 in cell A2, then select the column from cell A2 through the bottom of your spreadsheet. To do so, hold down Ctrl and Shift; and press the Down-Arrow key. To enter the sequence values, choose Home, Editing, Fill, Series, to launch the dialog. (Or type Alt, H, F, I, S.) In the dialog, enter 100 as its Stop value; then choose OK.
Column B returns the Z values from the lowest value to the highest value:
B2: =Zmin
B3: =(Zmax-Zmin)/(NumRows-1)+B2
Column C calculates the chart’s X (horizontal) value for each Z value:
C2: =B2*StdDev+Mean
Column D calculates the chart’s Y (vertical) value:
D2: =NORM.DIST(C2,Mean,StdDev,FALSE)
Column E calculates the Y values for the area to be shaded:
E2: =IF(OR(C2>ShadeRight,C2<ShadeLeft),NA(),D2)
Copy cell B3 and the formulas in the range C2:E2 down their columns to the last sequence number, in row 100.
To complete this step, assign the labels in row 1 of the Data sheet to the data beneath those labels. To do so, first select the range A1:E101. Choose Formulas, Create From Selection. (Or choose Ctrl + Shift + F3.) In the Create Names dialog, make sure only Top Row is checked; then choose OK.
Set Up Your Chart
You will use one data series to generate the normal curve and another data series to display the shaded area.
To set up the chart of the normal curve, select the range C2:D101. Choose Insert, Charts, Scatter. In the drop-down box, choose Scatter with Smooth Lines.
After you do so, Excel will generate your initial chart. Its line color might be different from mine, but it should otherwise resemble the first example below.
Excel will create your chart on the Data worksheet. Select the chart object; press Ctrl + X to cut it; activate the Reports worksheet; then press Ctrl + V to paste it into that worksheet.
If you click on the chart’s curve, your formula should look something like this:
=SERIES(Data!$D$1,Data!$C$2:$C$101,Data!$D$2:$D$101,1)
You’ll find this formula easier work with if you convert its cell references to range names and remove the chart title. To do so, edit the formula to:
=SERIES(,NormalCurve.xlsx!X,NormalCurve.xlsx!Y,1)
Here, NormalCurve.xlsx is the name of my workbook, but your’s might be different.
To set up the data series for the shaded area beneath the curve, it’s easiest to copy the first data series and then modify it.
To copy the data series, first select the curve in the chart. Then select the SERIES formula in your formula bar. Copy this formula by pressing Ctrl + C. Then press Esc to deselect the formula.
To paste the data series, first click anywhere on the edge of the chart object. After you do so, the SERIES formula will disappear. Then click in the formula bar area; press Ctrl + V; and then press Enter.
After you press Enter, your chart will look like nothing has changed. This is because your new series is behind your first series. Your new series now is Series 1 and your original series has been changed to Series 2…as you can see in the right-most argument in your SERIES formula.
(To be clear, when Excel creates a chart it draws Series 1 first, then Series 2, and so on. So if two chart series overlap—as they do here—the higher-number series always will be plotted on top of the lower-number series.)
We’re going to modify Series 1. To select it, first click on the curve. Then look in your formula bar to see which series you’ve selected. If you see Series 2, press Ctrl + Down-Arrow once to select Series 1.
With Series 1 selected, change its formula so that it looks like one of these two versions:
=SERIES(,Data!$C$2:$C$101,Data!$E$2:$E$101,1)
=SERIES(,NormalCurve.xlsx!X,NormalCurve.xlsx!Area,1)
You’ll use the second version if you have assigned range names as I suggested earlier.
At this point, your chart will look about the same as it did before. So let’s change that…
With Series 1 selected, click on the Chart Elements icon, check the Error Bars option, and then click on More Options…
…which will open the Format Error Bars task pane.
In the Format Error Bars pane, click on the Error Bar Options arrow icon, as shown here…
…and then choose Series 2 Y Error Bars.
With the Vertical Error Bar options displayed in the pane, specify…
- Direction: Minus
- End Style: No Cap
- Error Amount: Percentage, 100%
Your chart now should look something like this:
Near the top of the Format Error Bars pane, choose the paint can icon. In the Line section, choose Solid Line. Choose the color you want. (I chose Gold, Accent 4.) And specify an initial Width of 2 points.
Your chart now should look something like this:
To remove the horizontal error bars, you need to select them and delete them. To select them, in the Format Error Bars pane, click on the Error Bar Options arrow icon, and choose Series 2 X Error Bars. Now press your Delete key.
Your your normal curve with a shaded area is essentially complete, but you will need to adjust your chart at times by adjusting the width of Series 2 (the blue line you can see) and the width of your error bars. Specifically…
- If you make your error bars’ lines too thin, you’ll see strips of white between your error bars.
- If you make your error bars’ lines too thick, you’ll see tiny square edges peeking out from behind your Series 2 line.
- To hide those tiny corners, you can widen the Series 2 line.
You also could clean up your chart. In this image, for example, I deleted the gridlines, set the width of the blue line to 3 points, selected a darker color for the line, set the number format of the Y axis to “.00”, and gave both axes a bold font.
And to add the title for this image, I added the text to a formatted cell above the chart, and then copied the cell and the chart below it as one image.
