An Excel Tutorial:

How to Create Normal Curves
With Shaded Areas in New Excel

Using Excel 2007 and beyond, you can create shaded areas in charts to specify areas of special interest. Here, we show you how to do this using normal curves.

by Charley Kyd, MBA Microsoft Excel MVP

In An Introduction to Excel's Normal Distribution Functions I presented several figures somewhat like the one below. This article explains how to create the figures in New Excel. If you use Excel 2003 or before, you should read How to Create Normal Curves with Shaded Areas in Classic Excel.

Even if you have no particular reason to chart a normal curve, you might find the techniques interesting. This is because you might need to use similar techniques when you create other charts.

First, however,  this link explains how to get the free workbook that contains working examples of the charts that follow. The download also includes a workbook for Classic Excel, which is Excel 2003 and before.

Set Up the 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 Reports.

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 Insert, Name, Create. 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   =NORMSINV(PctClear)*StdDev+Mean

NORMSINV is the inverse of the NORMSDIST 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 NORMSINV result by the amount of the standard deviation, then add the mean.

  ShadeRight  =NORMSINV(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 the Data Sheet

This figure shows the top of the Data sheet. Let's take each of these columns in turn:

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, you have two choices. First, you can use Classic-Excel's key combination Alt, E, I, S to launch the Series dialog. Second, you can choose Home, Editing, Fill, Series, to launch the dialog. Enter 100 as the stop value in the dialog; 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:  =NORMDIST(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 101.

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. In the Create Names dialog, make sure only Top Row is checked; then choose OK.

Set Up the 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 probably will 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!$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. To do so, edit the formula to:

=SERIES(,NormalCurve.xls!X,NormalCurve.xls!Y,1)

Here, NormalCurve.xls is the name of my workbook. Your SERIES formula probably will have a different name.

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 the formula bar. Copy this formula by pressing Ctrl+C. Then press Esc to de-select 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 and the words "Chart Area" will appear at the far left side of your formula bar. Then click in the formula bar areap; press Ctrl+V; change the last argument for the new SERIES formula from 1 to 2; and then press Enter.

After you press Enter, your chart will look something like this.

At this point, the chart displays two identical data series, Series 1 and Series 2. Because we want the curve to be shown on top of the shaded area, we'll modify Series 1 to display the shading, and we'll leave Series 2 undisturbed.

(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, higher-number series always will be plotted on top of lower-number series.)

To select Series 1, first click on the curve. Then look in your formula bar to see which series you've selected. If you see Series 2, press your Down Arrow key once to select Series 1.

With Series 1 displayed in your formula bar, change the 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.xls!X,NormalCurve.xls!Area,1)

You'll use the second version if you have assigned range names as I suggested above.

At this point, your chart will look about the same as it did before. So let's change that condition...

With Series 1 selected, choose Chart Tools, Layout, Error Bars, More Error Bars Options. In the Vertical Error Bars tab, choose Minus, No Cap, and specify a Percentage error amount of 100. Then choose Close.

We need to change thos chart in several ways.

First, we need to make the error bars thicker, which will eliminate the banding in the shaded area. Also, we typically will change the color of the shaded area. And we need to get rid of the "fuzz" above the green curve.

To format the error bars, first click on the black error-bar lines in your chart. Press Ctrl+1 to launch the Format Error Bars dialog. In the Line Color tab, specify the color you want. In the Line Style tab, enter a Width of 2.

(You should use the smallest width that will turn the vertical bars into a solid area. So you'll probalby need to make this value larger if you make the Plot Area larger.)

This figure shows your results. 

The "fuzz" above the curve is caused by short, horizontal error bars that Excel adds to the figure. Here's how to get rid of the fuzz:

Click on the on the vertical error bars, which consists of the brown area in this chart. Then tap your left arrow once to select the horizontal error bars. To delete them,  just press Delete.

After you make these changes, your chart should look something like this chart.

Now you need to clean up the chart slightly.

To eliminate the legend at the right, select the Legend object within the Chart Area, and then press Delete. Then click and drag the right side of the Plot Area so that it fills the Plot Area.

Your chart now will look something like the next one below. (However, you'll probably need to increase the width of your error bars, because your Plot Area has just become larger.)

If you want to format the chart so that it looks more like the figure at the beginning of this article, you need to take several additional steps.

First eliminate the fill and border colors from the Chart and Plot areas. To do so, select the Chart Area and press Ctrl+1. In the Fill tab, choose No Fill. In the Border Color tab, choose No Line.

You also can change the Plot Area without dismissing the dialog. So after you're done editing the Chart Area, just click the Plot Area of the chart. Now set the Fill color of the Plot Area to No Fill, and the Border Color to No Line. Then choose Close.

Now assign a light color pattern to the cells behind your chart. To do so, select the area then use the Fill Color icon found in the Home, Font group. (I know, this adjustment has nothing to do with fonts, but that's where Microsoft put the Fill Color icon.)

If you want to remove the gridlines, just click on one of the gridlines and then press your Delete key.

Finally, to assign a number format to the Y axis, first select the axis. Press Ctrl+1. In the Number tab choose the Custom category and type in this number format: #,###.00. Then choose OK.

Your chart now should look something like the one shown here.

By the way, you could generate a chart figure that looks the same if you had assigned the background color to the Chart Area rather than to the cells behind the chart. However, by relying on the cells behind the chart, you get a lot more power to format your chart figure.

To illustrate, here's a variation of the previous chart, with a simple heading. This heading is easy to create in cells, but more work to create using the chart figure and a text box.

Now, you merely need to adjust your chart's size as needed.

As I mentioned at the beginning of this article, use this link to  download a free copy of the workbook described here. The link also provides downloads for other articles posted on this site.

Further Information About Charting Normal Curves

I'd like to thank Jon Wittwer, PhD, for introducing me to this method for shading curves. He understands a lot more about techniques for statistical analysis than I do. In Graphing a Normal Distribution Curve in Excel, he provides a brief description of this method, and provides a download file with additional examples.