How to Create a Bell Curve Chart (2024)

Summary

A bell curve is a plot of normal distribution of a given data set. This article describes how you can create a chart of a bell curve in Microsoft Excel.

More Information

n the following example you can create a bell curve of data generated by Excel using the Random Number Generation tool in the Analysis ToolPak. After Microsoft Excel generates a set of random numbers, you can create a histogram using those random numbers and the Histogram tool from the Analysis ToolPak. From the histogram, you can create a chart to represent a bell curve.

To create a sample bell curve, follow these steps:

1. Start Excel.

2. Enter the following column headings in a new worksheet:

   A1:Original B1:Average C1:Bin D1:Random E1:Histogram G1:Histogram

3. Enter the following data in the same worksheet:

   A2: 23 B2:A3: 25 B3: STDEVA4: 12 B4:A5: 24A6: 27A7: 57A8: 45A9: 19

4. Enter the following formulas in the same worksheet:

   B2: =AVERAGE(A2:A9)B3:B4: =STDEV(A2:A9)

   These formulas will generate the average (mean) and standard deviation of the original data, respectively.

5. Enter the following formulas to generate the bin range for the histogram:

   C2: =$B$2-3*$B4

   This generates the lower limit of the bin range. This number represents three standard deviations less than the average.

   C3: =C2+$B$4

   This formula adds one standard deviation to the number calculated in the cell above.

6. Select Cell C3, grab the fill handle, and then fill the formula down from cell C3 to cell C8.

7. To generate the random data that will form the basis for the bell curve, follow these steps:

   1. On theToolsmenu, clickData Analysis.

   2. In theAnalysis Toolsbox, clickRandom Number Generation, and then clickOK.

   3. In theNumber of Variablesbox, type1.

   4. In theNumber of Random Numbersbox, type2000.

      Note:Varying this number will increase or decrease the accuracy of the bell curve.

   5. In theDistribution box, selectNormal.

   6. In theParameterspane, enter the number calculated in cell B2 (29 in the example) in theMeanbox.

   7. In theStandard Deviationbox enter the number calculated in cell B4 (14.68722).

      See Also

      How to Calculate Normal Distribution in Excel - Learn ExcelMean Value: The Mean Value: Centering Your Data on Excel s Normal Distribution Graph - FasterCapitalHow to Normalize Data in Excel: A Step-by-Step Guide for Beginners - Support Your Tech7.3: Normal Distribution

   8. Leave theRandom Seedbox blank.

   9. In theOutput Optionspane, clickOutput Range.

   10. TypeD2in theOutput Rangebox.

       This will generate 2,000 random numbers that fit in a normal distribution.

   11. ClickOK.

8. To create a histogram for the random data, follow these steps:

   1. On theToolsmenu, clickData Analysis.

   2. In theAnalysis Toolsbox, selectHistogram, and then clickOK.

   3. In theInput Rangebox, typeD2:D2001.

   4. In theBin Rangebox, type C2:C8.

   5. In theOutput Optionspane, clickOutput Range.

   6. TypeE2in theOutput Rangebox.

   7. ClickOK.

9. To create a histogram for the original data, follow these steps:

   1. On theToolsmenu, clickData Analysis.

   2. ClickHistogram, and then clickOK.

   3. In theInput Rangebox, typeA2:A9.

   4. In theBin Rangebox, typeC2:C8.

   5. In theOutput Optionspane, clickOutput Range.

   6. TypeG2in theOutput Rangebox.

   7. ClickOK.

10. Create labels for the legend in the chart by entering the following:

    E14: =G1&"-"&G2E15: =E1&"-"&F2E16: =G1&"-"&H2

11. Select the range of cells, E2:H10, on the worksheet.

12. On theInsertmenu, clickChart.

13. UnderChart type, clickXY (Scatter).

14. UnderChart sub-type, in the middle row, click the chart on the right.

    Note:Just below these 5 sub-types, the description will say "Scatter with data points connected by smoothed lines without markers."

15. ClickNext.

16. Click theSeriestab.

    See Also

    The Normal Distribution

17. In theNamebox, delete the cell reference, and then select cell E15.

18. In theX Valuesbox, delete the range reference, and then select the range E3:E10.

19. In theY Valuesbox, delete the range reference, and then select the range F3:F10.

20. ClickAddto add another series.

21. Click theNamebox, and then select cell E14.

22. Click theX Valuesbox, and then select the range E3:E10.

23. In theY Valuesbox, delete the value that's there, and then select the range G3:G10.

24. ClickAddto add another series.

25. Click theNamebox, and then select cell E16.

26. Click theX Valuesbox, and then select the range E3:E10.

27. Click theY Valuesbox, delete the value that's there, and then select the range H3:H10.

28. ClickFinish.

    The chart will have two curved series and a flat series along the x-axis.

29. Double-click the second series; it should be labeled "- Bin" in the legend.

30. In theFormat Data Seriesdialog box, click theAxistab.

31. ClickSecondary Axis, and then clickOK.

You now have a chart that compares a given data set to a bell curve.

References

For more information about creating charts, clickMicrosoft Excel Helpon theHelpmenu, typecreate a chartin the Office Assistant or the Answer Wizard, and then clickSearchto view the topic.