Chapter 35: Analyzing Data with Pivot Tables
719
Automatic grouping examples
When a field contains numbers, dates, or times, Excel can create groups automatically. The two
examples in this section demonstrate automatic grouping.Grouping by date
Figure 35.5 shows a portion of a simple table with two fields: Date and Sales. This table has 730
rows and covers the dates between January 1, 2008 and December 31, 2009. The goal is to sum-
marize the sales information by month.FIGURE 35.5You can use a pivot table to summarize the sales data by month.
On the CD
A workbook demonstrating how to group pivot table items by date is available on the companion CD-ROM.
The file is named sales by date.xlsx.
Figure 35.6 shows part of a pivot table created from the data. The Date field is in the Row Labels
section, and the Sales field is in the Values section. Not surprisingly, the pivot table looks exactly
like the input data because the dates have not been grouped.To group the items by month, select any date and choose PivotTable Tools ➪ Options ➪ Group ➪
Group Field (or, right-click and choose Group from the shortcut menu). You see the Grouping
dialog box, shown in Figure 35.7. Excel supplies values for the Starting At and Ending At fields.
The values cover the entire range of data, and you can change them if you like.