Microsoft Office Tutorials and References
In Depth Information
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 171-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, 2008, and December 31, 2009. The goal is to summarize the sales information by month.
Figure 171-1: You can use a pivot table to summarize the sales data by month.
Figure 171-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 171-3. In the 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 171-4).
 
Search JabSto ::




Custom Search