Back to Blog
How to create a 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.
- 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.
- 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.
- The frequency formula is =FREQUENCY(data_array, bin_array).
- With cells F2-F8 highlighted, begin to type the formula =FREQUENCY(
- Select the student scores (B2–B21) to add the data_array argument to the formula.
- 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.
- Now type a comma (,) in your formula and then highlight the bins (E2–E8) to add the bin_array argument to the formula.
- Your formula should now read: =FREQUENCY($B$2:$B$21, E2:E8).
- 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.
- 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.
- If you use the chart wizard, follow the steps to create the XY (Scatter), smooth line chart.
- Your chart should look like this:
Posted by Technology Training