Grouping and Ungrouping Data

PivotTables group inner field items under each outer field heading and, if requested, create subtotals for each group of inner field items. You might find it convenient to group items in additional ways-for example, to collect monthly items into quarterly groups or sets of numbers into larger numeric categories. Excel provides several options for grouping items.

Creating Ad Hoc Item Groupings

Suppose that after looking at Figure 15–3 you decide you’d like to see the domestic and international sales figures grouped into a category called Retail. To create this group, select the Domestic and International items anywhere in the table. Then click the Options tab under PivotTable Tools, and click Group Selection in the Group group. Excel creates a new heading called Group1:

Now you can rename Group1 by simply typing over any instance of it.

Grouping Items in Date or Time Ranges

Figure 15–22 shows a PivotTable that summarizes daily transactions by payee. As you can see, the data in this table is extremely sparse. Most intersections between a day item and a payee item are blank.

Figure 15–22: To make the data in this table more meaningful, you can group the date field.

You’ll find the  Transactions.xlsx file in the Sample Files section of the companion CD.

To make this kind of table more meaningful, you can group the date field. To do this, select an item in the field. Then click the Options tab under PivotTable Tools, and click Group Field. Excel responds by displaying the Grouping dialog box, shown in Figure 15–23.

Figure 15–23: Excel gives you lots of ways to group by date.

Excel gives you a great deal of flexibility in the way your date and time fields are grouped. In the By list, you can choose any common time interval, from seconds to years, and if the standard intervals don’t meet your needs, you can select an arbitrary number of days. You can also create two or more groupings at the same time (hold down Ctrl while you select); the results of grouping by both Quarter and Month are shown in Figure 15–24.

Figure 15–24: In this table, daily data is grouped by months and then by quarters.