Excel Charting Utilities | Custom Solutions | Training | Charts and Tutorials | PTS Blog

Peltier Tech
Chart Utilities

Books at Amazon.com

Buy me a coffee

If this topic or the whole site has been helpful, please support further development by treating me to a cup of coffee.

Excel Run Chart with Mean and Standard Deviation Lines.

Unlike some commercially available statistics packages, Microsoft Excel does not offer a built in capability to draw lines corresponding to statistical values for a series, such as the mean and the mean ą k standard deviations. The example on this page uses the Add a Horizontal Line to a Column or Line Chart: Error Bar Method to add statistical indicators to a simple run chart.

This example uses 25 values from a normally distributed population:

{6.384, 6.190, 2.757, 3.469, 6.694, 5.319, 5.805, 5.562, 5.621, 5.285, 3.247, 3.796, 5.477, 6.149, 8.312, 5.374, 1.236, 7.802, 4.823, 5.315, 4.537, 7.240, 7.174, 3.449, 4.078}

These values are listed in column B of the worksheet. In D1:F4 of the worksheet, formulas are entered to produce the following values:

       Stats
25.5   3.583   Mean - SD: 3.58
25.5   5.243   Mean: 5.24
25.5   6.904   Mean + SD: 6.90

Cell D1 is blank, while cell E1 has a text label. The formulas in D2:F4 are:

=COUNT(B:B)+0.5   =AVERAGE(B:B)-STDEV(B:B)   ="Mean - SD: "&TEXT(E2,"0.00")
=COUNT(B:B)+0.5   =AVERAGE(B:B)              ="Mean: "&TEXT(E3,"0.00")
=COUNT(B:B)+0.5   =AVERAGE(B:B)+STDEV(B:B)   ="Mean + SD: "&TEXT(E4,"0.00")

This simple formula was entered into cell H1 to get a count of the number of points:

=COUNT(B:B)

A simple run chart was constructed using the data in column B, adding appropriate axis titles.

Run Chart with Lines Step 1

Range D1:E4 was copied, the chart was selected, and Paste Special was used to add the data as a new series, with series in columns, categories in the first column, and series names in the first row.

Run Chart with Lines Step 2

This added series is not useful as a line chart. It was selected, and using Chart Type from the Chart menu, it was changed to an XY Scatter series.

Run Chart with Lines Step 3

Excel automatically added secondary axes to the chart and assigned the XY series to them. The unneeded axes were removed using Chart Options from the Chart menu, and unchecking all secondary chart checkboxes on the Axes tab.

Run Chart with Lines Step 4

Horizontal (X) Error bars were added to the XY series. The Minus option could have been selected with a value of 25 manually entered, but a more flexible approach was used: after clicking in the Custom (-) entry box, cell H1 was selected, so the box contained a link to cell H1 (=Sheet1!$H$1). The line color of the error bars was changed to red

Run Chart with Lines Step 5

Data labels were added to the XY series, using the Show Values option. The font color of the labels was changed to red.

Run Chart with Lines Step 6

The more descriptive labels in F2:F4 were applied to the XY series using Rob Bovey's Chart Labeler, a free add-in available at http://appspro.com. It's easy to install, easy to use, and highly useful. John Walkenbach's Chart Tools, another free add-in from http://j-walk.com, provides similar functionality.

Run Chart with Lines Finished

This technique works equally well with a column chart.

Run Chart with Lines (Columns)

Peltier Technical Services, Inc.

Commercial Utilities | Custom Solutions | Training | Charts and Tutorials | PTS Blog

Peltier Technical Services, Inc., Copyright © 2010. All rights reserved.
You may link to this article or portions of it on your site, but copying is prohibited without permission of Peltier Technical Services.

Microsoft Most Valuable Professional

My MVP Profile