How to Create a Normal Bell Chart in Excel

To create a normal bell curve chart in Microsoft Excel, make a histogram of your Excel data and then use formulas to plot the chart. A histogram uses the standard deviation and mean present in bell curve charts to make a visual representation of the data. After you type the data into the spreadsheet cells, use formulas to find the average, or mean, and the standard deviation. The bell curve is plotted using the mean as the center of the curve.

...
A bell curve chart uses the mean of the data to plot a distinctive shape.

Add Excel Formulas

Step

Launch Excel and click "New" to create a new spreadsheet.

Step

Type "Original" in the A1 cell; type "Average" in the B1 cell; type "Bin" in the C1 cell; type "Random" in the D1 cell; and type "Histogram" in the E1 cell.

Step

Type your data to plot in the "A" column.

Step

Type the following formula in the B2 cell, where "X" is the number of the "A" column cell where the data stops:

Step

\=AVERAGE(A2:AX)

Step

This is the average of the "A" column data.

Step

Type "STDEV" in the B3 cell. Type the following formula in the B4 cell, where "X" is the number of the "A" column cell where the data stops:

Step

\=STDEV(A2:AX)

Step

This is the standard deviation of the "A" column data.

Step

Type "=$B$2-3*$B4" in the C2 cell; type "=C2+$B$4" in the C3 cell. These formulas generate the histogram bin range.

Step

Position your mouse over the C3 cell corner. Drag down to the bottom of the "A" column data to fill the formula down.

Generate Data

Step

Click "Tools" and "Data Analysis." Click "Random Number Generation" in the "Analysis Tools" section, then click "OK."

Step

Type "1" in the" Number of Variables" box; type "2000" in the Number of Random Numbers" box. This generates 2000 numbers. Click "Normal" in the "Distribution" box.

Step

Type the number from cell B2 in the "Mean" box in the "Parameters" pane; type the number from cell B4 in the "Standard Deviation" box.

Step

Click the "Output Options" pane and click "Output Range." Type "D2" and click "OK."

Create Bell Curve

Step

Click "Tools," "Data Analysis" and "Histogram." Click "OK."

Step

Type "A2:AX" in the "Input Range" box, where "X" is the final cell of data; type "C2:CX" in the "Bin Range" box, where "X" is the final cell of data.

Step

Click "Output Range" in the "Output Options" pane. Type "E2" and click "OK."

Step

Select the range of cells from A2 across to E2 and down to the bottom of the data. Click "Insert" and "Chart."

Step

Click "XY (Scatter)" under "Chart type." Click "Next" and "Finish." The bell curve chart appears with the original random data.