Santa Clara University


Software Tips & Tricks

Back to Blog

How to create a bell curve-like chart in Excel

Bell curve-like chart in Excel

Excel doesn't have a simple way of creating a bell-curve out of data, but by using the FREQUENCY formula and an XY scatterplot, you can get a similar-looking chart for visual purposes.

  1. Start by setting up your data into columns. Make sure you have a column dedicated to values or "bins" that you want to plot on the chart. In this example of student scores, column E contains bins from 70 (the lowest score) to 100 (the highest score) with increments of 5 in between. The information in column D is optional, but explains to a user what scores fall within a particular bin.
  2. Next, highlight the empty cells adjacent to the bins. In this example, that would be cells F2–F8. The frequency that each value in a bin occurs among the students' scores in column B will be calculated in these answer cells. Remember that the bins represent a range of values. The bin '80' in cell E4  includes all scores that are greater than 75 but less than or equal to 80.
  3. The frequency formula is =FREQUENCY(data_array, bin_array).
  4. With cells F2-F8 highlighted, begin to type the formula =FREQUENCY(
  5. Select the student scores (B2–B21) to add the data_array argument to the formula.
  6. Type F4 (PC) or Command+D (Mac) to make the cell references absolute. This puts a dollar sign in front of the column letter and row number, for example $B$2.
  7. Now type a comma (,) in your formula and then highlight the bins (E2–E8) to add the bin_array argument to the formula.
  8. Your formula should now read: =FREQUENCY($B$2:$B$21, E2:E8).
  9. To enter the formula, press Shift+Control+Return (PC) or Shift+Command+Return (Mac), not just 'Return' or 'Enter'. This is the way all array functions are entered.
  10. Now that you have your data (F2–F8), you can create your chart. Select the bins (E2–E8) and choose the XY (Scatter), Smooth Line chart by using one of the following methods: In Excel 2003 and 2004 click the chart wizard icon on the toolbar; in Excel 2007 click Insert on the ribbon and go to the Charts group; in Excel 2008 click the Gallery and choose Charts.
  11. If you use the chart wizard, follow the steps to create the XY (Scatter), smooth line chart.
  12. Your chart should look like this:

Comments Comments

Cecil said on Apr 10, 2013
the instructions are incomplete....
mathwizard said on Jun 8, 2013
the graph didn't come proper.
chip said on Jun 28, 2013
TO make the graph appear, select the 'value' colum and the 'frequency' column by dragging the mouse across the cells, then select 'Insert' > 'Scatter' > 'Scatter with lines'.
Dongmongler said on Sep 1, 2014
This sort of worked... needs some more clarity
sf said on Sep 17, 2014
Worked fine for me - thanks!
shanica smith said on May 21, 2015
Am very happy to tell every one to hear my testimony. Am a nurse,this is a story of my love life.I have been married for 4years and on the fifth year of my marriage, another woman take my lover away from me and my husband left me and the kids and we have suffered for 4months until i met a post where this man Dr OLOKUM have helped someone and i decided to give him a try to help me bring my lover back home and believe me i just send my picture to him and that of my husband and after 48hours as he have told me, i saw a car drove into the house and behold it was my husband and he have come to me and the kids and that is why i am happy to make every one of you in similar to met with this man and have your lover back to your self.You can contact him with this email address {LAVENDERLOVESPELL@YAHOO.COM} Thank you Dr OLOKUM LAVENDERLOVESPELL@YAHOO.COM
Post a Comment

Tags: Excel

Printer-friendly format