Grouping by Number

Contributed by: Dawn Griffiths

Difficulty level: Beginner

Problem

You have a PivotTable with a number field and want to group summary values by numeric ranges.

Solution

Suppose you have a PivotTable with Student and Score fields, where Score contains numeric values and Student contains text values, and you want to count how many students scored 1 to 10, 11 to 20, 21 to 30, and so on. You can do so by following these steps:

1. In the PivotTable Fields pane, drag the Score field to the Rows section and the Student field to the Values section.

2. Select one of the Score cells in the PivotTable; then choose PivotTable Analyze ⇒ Group ⇒ Group Selection to open the number Grouping dialog box.

3. In the Grouping dialog box, use the Starting At and Ending At boxes to specify the lower and upper limits of the groups and the By box to specify the interval for each group. To group values from 1 to 100 where the interval for each group is 10, you’d type 1 in the Starting at box, 100 in the Ending At box, and 10 in the By box.

4. Click OK to apply the changes to the PivotTable (see Figure 1).

   

   Figure 1. The Grouping dialog box for number fields and the corresponding PivotTable

To remove the groups, select a cell containing one of the group’s items, then choose PivotTable Analyze ⇒ Group ⇒ Ungroup.

Discussion

This recipe is similar to Recipe 11.16 except that it shows ...