Santa Clara University

summer-web

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!
Post a Comment

Tags: Excel

Printer-friendly format