How to create a bell curve chart template in Excel?
Bell curve chart, named as normal probability distributions in Statistics, is usually made to show the probable events, and the top of the bell curve indicates the most probable event. In this article, I will guide you to create a bell curve chart with your own data, and save the workbook as a template in Excel.
Recommended Excel Productivity Tools
To create a bell chart with your own data, and then save it as an Excel template, you can do as following:
1. Create a blank workbook, and enter the column header In Range A1:D1 as following screen shot shows:
2. Enter your data into the Data column. In our case, we enter from 10 to 100 into Rang A2:A92 in Column Data. (Note: if your data are listing randomly, you'd better sort them by clicking Data > Sort.)
3. Calculate the Average and standard deviation.
(1) In Cell C2 enter =AVERAGE(A2:A92), and press the Enter key;
(2) In Cell D2 enter =STDEV(A2:A92), and press the Enter key;
Note: The A2:A92 is the range we enter our data, and please change the A2:A92 to the range with your data.
4. In Cell B2 enter =NORM.DIST(A2,$C$2,$D$2,FALSE) in Excel 2010 and 2013 (in Excel 2007 please enter =NORMDIST(A2,$C$2,$D$2,FALSE)), and drag the Fill Handle to the Range A3:A92.
5. Select the Range A2:B92 (Data column and Distribution column) ，and click the Insert > Scatter ( or Scatter and Doughnut chart in Excel 2013) > Scatter with Smooth Lines and Markers.
Then a bell curve chart is created showing as following screen shot.
You can format the bell curve by removing legends, axis, and gridlines in the bell curve chart.
And now you can save the created bell curve chart as a normal chart template in Excel with following steps:
1. Save the bell curve chart as a chart template:
A. In Excel 2013, right click the bell curve chart, and select the Save as Template from the right-clicking menu;
B. In Excel 2007 and 2010, click the bell curve chart to activate the Chart Tools, and then click the Design > Save As Template.
2. In the popping up Save Chart Template dialog box, enter a name for your template in the File name box, and click the Save button.
In addition to saving the created Bell Curve chart as a chart template for reusing in future, Kutools for Excel’s AutoText utility supports Excel users to save created chart as an AutoText entry and reuse the AutoText of chart at any time in any workbook with only one click.
Kutools for Excel - Combines more than 300 Advanced Functions and Tools for Microsoft Excel
1. After creating your Bell Curve chart (how to create a Bell Curve chart?), please activate the AutoText pane with clicking Kutools > Navigation > AutoText button at far left of Navigation Pane. See left screenshot:
2. Select the Bell Curve chart, and click the Add button in the AutoText Pane. See screenshot:
3. In the Opening New AutoText dialog box, name this new AutoText entry, and specify a group you save this AutoText entry into, and click the Add button.
So far you have saved the Bell Curve chart as an AutoText entry already. From now on, no matter which workbook you are working with, you can click this AutoText entry of Bell Curve chart to insert it in the workbook by only one click at any time. See screenshot:
Recommended Productivity Tools for Excel
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
To post as a guest, your comment is unpublished.· 2 months agoI followed the instructions above but ended up with a graph which wasn't in a normal distribution. What can I do to make this normally distributed?
To post as a guest, your comment is unpublished.· 2 months agoGood explanation loved it!
To post as a guest, your comment is unpublished.· 6 months agoHi, I have query on NORM.DIST Function in Excel. For probability mass function (False case) contains formula and result is generated. For cumulative (True case) in excel which formula is using to generate the result? For True, it is mentioned as integral of probability mass function formula.
To post as a guest, your comment is unpublished.· 8 months agoI think I'm missing something in the instructions for how to make a bell curve chart. In step 4 the formula creates, in cellB2, a normal distribution for the range of my data in column A. However there is no data in B3 through B93, yet the scatter chart seems to require data in cells B3 through B93. How is that data produced? Any suggestions would be appreciated.
To post as a guest, your comment is unpublished.· 1 years agoGood stuff, much better than some other site I was trying to follow. But, in Excel 2016 at least, you don't need to sort your data for the graph to show properly, its nice to be able to save a step or not have to worry about it if your data might change, extend, or be in a certain order for a reason (e.g. data entry ease). Also, because you made this so easy to follow, I was able to figure out how to add a second set of data (with its own average, standard deviation, and norm.dist values) to the same graph, it makes my graph more interesting. I think it would be cool if you expanded your base example into an advanced example like that. Again, good stuff.