How to use excel to create a bell curve

Jupiterimages/Comstock/Getty Images

Most of us have heard teachers talk about "grading on a curve." The curve they use is a simple version of a bell curve. A bell curve is a standard charting procedure for defining general trends and statistical averages. It is based on the concept of standard deviations. A bell curve visualises the apparent randomness in a data set. The result is a picture of data distribution that organises items into an overall summary of aberration and normalcy. Microsoft Excel can create bell curves based on data in a spreadsheet. The program has built-in statistical functions to calculate the parameters of the bell curve. You can use a variety of methods to construct a bell curve; after you learn the simpler techniques, you may investigate more complex strategies.

Type the word "Mean" into cell E1 and "Standard Deviation" into cell G1.

Type the desired mean and standard deviation for your bell curve into cells F1 and H1. The mean represents the average number from the entire data set. In a bell curve, this is often similar to the median, or the number which occurs most often. The standard deviation is a statistical property based on likelihood of occurrence. A deviation of 1 will include 68 per cent of all the data in a collection. By the third deviation, almost all the data is included. For example, a mean of 5 with a deviation of 2 means that 68 per cent of all the data will fall between the numbers 3 and 7, which are 2 removed from the mean of 5.

Type the number "-4" into cell A2. Select the cell after entering the data by clicking on it once. The desired numbers are arbitrary so long as the subsequent formulas are entered accurately for Excel to generate normally distributed data appropriate for the desired bell curve.

Click the "Edit" menu and select the "Fill" sub-menu.

Choose the "Series" command from the "Fill" sub-menu. A pop-up window will appear.

Select the "Columns" option in the "Series in" section of the "Series" pop-up window. Select the "Linear" option in the "Type" section, and type "0.25" into the "Step value" field. Type "4" into the "Stop value" field and press the "OK" button. The "Step value" is customisable. Enter a smaller number to generate a curve with greater detail and more points, such as "0.1". A higher number will show fewer data points.

Type the formula


into cell B2. Type the formula


into cell C2. These functions generate the complex distribution of data necessary to form a true statistical bell curve.

Select cells B2 and C2 by clicking once on B2 and dragging the mouse to cell C2. Release the mouse.

Copy the formulas down through the entire data range. Hover the mouse over the lower-right corner of cell C2. The cursor will change to a black plus sign. Click and drag the plus sign down to the last row which contains data in column A.

Select columns B and C by clicking on cell B2 and dragging down to the last row that contains data, and over one column to include C.

Click the "Chart" button at the top of the Excel program window. A pop-up window will appear.

Select the "XY (Scatter)" chat type and press the "Finish" button. The bell curve is created.

Most recent