This page is an advertiser-supported excerpt of the book, Learn Excel 2007-2010 from MrExcel - 512 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.
Group Daily Dates by Month in a Pivot Table
Problem: My data set has a date on which each item was shipped. When I produce a pivot table with the date field, it provides sales by day. My plant manager loves sales by day, but everyone else in the company would rather see sales by month.
Figure 832 Excel reports daily dates.
Strategy: You can group daily dates to show year, quarter, and month. To do so, you build a pivot table with dates in the Row area of the pivot table:
1. Select a cell that contains a date. Click the Group Field icon in the Options tab. Excel displays the Grouping dialog.
2. The Grouping dialog defaults to selecting months. If your data spans more than one year, it is crucial that you also select years. Select Months and Years. If you don’t choose Years, Excel will group January from one year and January from another year into a single value called January.
Figure 833 Select Months and Years.
The Date field is now replaced with Months. There is a field called Years. Years and Months are shown in the pivot table, although the pivot table is not showing subtotals for each year.
Figure 834 500 rows of daily dates are now 24 rows of months.
3. To add the subtotals for the years field, select a years field, then choose Field Settings as shown above.
4. In the Field Setting dialog, change Subtotals from None to Automatic.
Figure 835 Change the Years field to use Automatic Subtotals.
The result is a report with a subtotal for each year.