Excel provides a number of ways to create a frequency distribution, but none of those methods is easier than using a pivot table. Figure 18-26 shows part of a table of 221 students and the test score for each. The goal is to determine how many students are in each ten-point range (1–10, 11–20, and so on).
Figure 18-26: Creating a frequency distribution for these test scores is simple.
On the CD | This workbook, named test scores.xlsx, is available on the companion CD-ROM. |
The pivot table is simple:
The Score field is in the Row Labels section (grouped).
Another instance of the Score field is in the Values section (summarized by Count).
The Grouping dialog box that generated the bins specified that the groups start at 1, end at 100, and are incremented by 10.
Note | By default, Excel does not display items with a zero value. In this example, no test scores are below 21, so the 1–10 and 11–20 items are hidden. To override this setting, access the PivotTable Options dialog box, click the Display tab, and put a check mark next to Display Item Labels When No Fields Are in the Values Area. |
Figure 18-27 show the frequency distribution of the test scores, along with a pivot chart, created by choosing PivotTable Tools Options Tools PivotChart.
Figure 18-27: The pivot table and pivot chart shows the frequency distribution for the test scores.
Note | This example used Excel's Grouping dialog box to create the groups automatically. If you don't want to group in equal-sized bins, you can create your own groups. For example, you may want to assign letter grades based on the test score. Select the rows for the first group and then choose Group from the shortcut menu. Repeat these steps for each additional group. Then replace the default group names with more meaningful names. |