Excel Charts
How to Create Normal Curves With Shaded Areas in Excel
Using Excel 2007 and above, you can create shaded areas in charts to specify areas of special interest. Here's how to do this using normal curves.
by Charley Kyd, MBA Microsoft Excel MVP, 20052014
The Father of Spreadsheet Dashboard Reports

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
still 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
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 (rightmost) probability
that will be shaded beneath the curve. PctClear is the minimum
(leftmost) probability that will be shaded.)
The numbers in the Calculations section are returned by
formulas:
ShadeLeft =NORMSINV(PctClear)*StdDev+Mean
The NORMSINV
function 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 ClassicExcel'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: =(ZmaxZmin)/(NumRows1)+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 dropdown 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 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 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,
highernumber series always will be plotted on top of
lowernumber 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.
Here's your initial result. It doesn't look very good, but at
least we're heading in the right direction.
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 errorbar
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.
