Chapter 35: Analyzing Data with Pivot Tables
723
Figure 35.11 shows part of a table of 221 students and the test score for each. The goal is to deter-
mine how many students are in each 10-point range (1–10, 11–20, and so on).FIGURE 35.11Creating 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. n
The pivot table is simple:l (^) The Score field is in the Row Labels section (grouped).
l 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 count of zero. In this example, no test scores are less than 21, so
the 1–10 and 11–20 items are hidden. To force the display of empty bins, choose PivotTable Tools ➪ Options ➪
Field Settings to display the Field Settings dialog box. Click the Layout & Print tab, and select Show Items with No
Data. n
Figure 35.12 show the frequency distribution of the test scores, along with a pivot chart. (See
“Creating Pivot Charts,” later in this chapter).