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 centre of the curve.
Launch Excel and click "New" to create a new spreadsheet.
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.
Type your data to plot in the "A" column.
Type the following formula in the B2 cell, where "X" is the number of the "A" column cell where the data stops:
This is the average of the "A" column data.
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:
This is the standard deviation of the "A" column data.
Type "=$B$2-3*$B4" in the C2 cell; type "=C2+$B$4" in the C3 cell. These formulas generate the histogram bin range.
Position your mouse over the C3 cell corner. Drag down to the bottom of the "A" column data to fill the formula down.
Click "Tools" and "Data Analysis." Click "Random Number Generation" in the "Analysis Tools" section, then click "OK."
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.
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.
Click the "Output Options" pane and click "Output Range." Type "D2" and click "OK."
Click "Tools," "Data Analysis" and "Histogram." Click "OK."
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.
Click "Output Range" in the "Output Options" pane. Type "E2" and click "OK."
Select the range of cells from A2 across to E2 and down to the bottom of the data. Click "Insert" and "Chart."
Click "XY (Scatter)" under "Chart type." Click "Next" and "Finish." The bell curve chart appears with the original random data.
Save your work often throughout the creation of the chart.
Tips and warnings
- Save your work often throughout the creation of the chart.
Things you need
- Microsoft Excel