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 Productivity Tools for Excel/Office
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
Bring handy tabs to Excel and other Office software, just like Chrome, Firefox and new Internet Explorer.
Amazing! Increase your productivity in 5 minutes. Don't need any special skills, save two hours every day!
300 New Features for Excel, Make Excel Much Easy and Powerful:
- Merge Cell/Rows/Columns without Losing Data.
- Combine and Consolidate Multiple Sheets and Workbooks.
- Compare Ranges, Copy Multiple Ranges, Convert Text to Date, Unit and Currency Conversion.
- Count by Colors, Paging Subtotals, Advanced Sort and Super Filter,
- More Select/Insert/Delete/Text/Format/Link/Comment/Workbooks/Worksheets Tools...
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.· 24 days 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.· 2 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.· 8 months 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.
To post as a guest, your comment is unpublished.· 9 months agoCan any set of data be made to look like a Bell curve this way?? What if the Kurtosis of the data was not within +/- 3 for a Normal Distribution?
To post as a guest, your comment is unpublished.· 9 months agoClear example -- a lifesaver!