Secrets of the Office Masters: Grouping Items in a PivotTable
Excel PivotTables are capable of splitting data into groups, even when you haven't organized your data in advance. This is a powerful feature that's useful in a variety of circumstances. When you choose to group data in a PivotTable, Excel analyzes the field you've chosen and displays a dialog box with choices that are appropriate for that type of data. For example, if you have a year's worth of daily sales figures, you might want to group them by week, by month, or by quarter. If you have a product catalog in which each row contains a product name, its category, and a price, you might want to group the list of products by category, and then by price within groups: $1.00–$10.00, $10.01–$20.00, and so on.
In the example shown here, a simple worksheet has just two columns: Date and Sales Volume. Each row contains a date and the total sales for that date, in dollars. In total, the sheet contains three years'worth of data. Here's how to create a report that shows monthly trends for all three years:
Create a PivotTable using the Date field in the Row area and the Sales Volume field in the Data area.
Right-click any entry in the Date column and choose Group and Outline, Group from the shortcut menu. Note that Excel correctly determines these are dates and offers to group by month. Because the sample extends over several years, choose Months and Years, and then click OK.
Drag two more copies of the Sales Volume button into the Data area and format each one to show a different summary: Sum, Average, and Min. Adjust the names of each summary as well. This step allows you to see the total sales, average sales, and minimum daily sales for each month during the three-year period.
Use the AutoFormat option to choose one of the Report formats. Adjust column formatting and number formats for each summary cell.
The results, shown in the following Figure, give a month-by-month snapshot of total sales, even though we started with nothing more than a daily list.
Notice the final frill in this useful table: We hid the taskbar and chose View, Full Screen to make as much information as possible visible on the screen, hiding distracting toolbars and title bars.
