Bell curves are often used in math to depict normal distributions. They are graphs which peak at the point of highest probability, and illustrate the probability distributions around either side of the peak. You can draw these by hand, but it’s much easier with a spreadsheet. Let’s learn how to build a bell curve in Microsoft Excel.
How to Build a Bell Curve in Microsoft Excel
Building a bell curve in Microsoft Excel involves two key processes: understanding how the curve illustrates statistical data, and then the art of building the actual graph.
A bell curve is really a visual illustration of two of the most fundamental statistical measurements in the world. These are the mean and standard deviation of a dataset. The mean – also called the average – will be found at the peak of the curve. In essence, the average outcome of a given scenario is rendered as the most likely in a bell curve.
A standard deviation helps measure the variability in a dataset. The lower the standard deviation value, the more concentrated the values are around the mean. A standard bell curve measures three standard deviations above and below your mean.
As you might expect, to begin building a bell curve in Excel, you’ll need to know those two key numbers: the mean and standard deviation of your dataset.
One of the most common uses for bell curves comes in the case of test scores. Exams and other scores are often graded along a curve; which, as you may guess, can be visually represented by a bell curve. Let’s construct a bell curve example based on a dataset of ten test scores.
The scores are listed on an Excel sheet, in cells A2:A11. The first step to building a simple bell curve is to find the mean of the dataset. Click into cell C1, and type =. This tells Excel that you’re inputting a formula. Continue by typing AVERAGE(. Your formula reads:
=AVERAGE(
Then, click and drag to highlight the test scores, and close the parentheses. The complete AVERAGE formula is:
=AVERAGE(A2:A11)
Hit Enter, and Excel will compute your mean.
In cell C2, compute the standard deviation using Excel’s built-in STDEV.P function. It’s helpful to also round your standard deviation to the nearest whole number, in order to construct a cleaner and simpler bell curve. Your formula, combining Excel’s ROUND and STDEV.P functions, is:
=ROUND(STDEV.P(A2:A11),0)
Then, find the limits in your dataset, calculated as the mean plus and minus three standard deviations. In a pair of two separate cells, use these two formulas:
=C1-3*C2
=C1+3*C2
The results form the upper and lower limits for your bell curve.
Place your lower limit of 48.4 in a cell, and then place the 49.4 in the cell immediately below it. From here, you can click and drag the lower right corner of the two cells to fill in your range. Stop when you reach your upper limit value of 114.4.
Now, input the NORM.DIST function in the cell immediately right of your lower limit. The formula should read:
=NORM.DIST(F1,81.4,11,FALSE)
Hit Enter, and then copy the formula downward across your remaining data. This builds an Excel normal distribution, from which you can create a bell curve.
Finally, click and drag your cursor to select your dataset. Then, go to the Insert tab on Excel’s ribbon. Click on the Scatter dropdown, and find the option for Scatter with Smooth Lines.
Click on its thumbnail, and Excel will automatically insert a basic bell curve onto your spreadsheet.
As you can see, it’s easy to build a bell curve, thanks to the powerful tools inside Microsoft Excel.