Microsoft Office Tutorials and References
In Depth Information
Tip 81: Grouping Items by Date in a Pivot Table
Grouping Items by Date in a Pivot Table
One of the more useful features of a pivot table is the ability to combine items into groups. Grouping
items is simple: Select them and choose PivotTable Tools➜Options➜Group➜Group Selection.
You can go a step further, though. When a field contains dates, Excel can create groups
automatically. Many users overlook this helpful feature. Figure 81-1 shows a portion of a table that has two
columns of data: Date and Sales. This table has 731 rows and covers dates between January 1, 2012,
and December 31, 2013. The goal is to summarize the sales information by month.
Figure 81-1: You can use the PivotTable feature to summarize the sales data by month.
Figure 81-2 shows part of a pivot table (in columns D:E) created from the data. Not surprisingly, it
looks exactly like the input data because the dates haven’t been grouped.
To group the items by month, right-click any cell in the Date column of the pivot table and select
Group from the shortcut menu. You see the Grouping dialog box, shown in Figure 81-3. In the By list
box, select Months and Years and verify that the starting and ending dates are correct. Click OK.
The Date items in the pivot table are grouped by years and by months (as shown in Figure 81-4).
The Group command is available only if every item in the field is a date (or a time). Even
a single blank cell will make it impossible to group by date.