Learn How To Group By Week In A Pivot Table With This Microsoft Excel Tutorial
Strategy: There is a grouping option that will group by week. However, in order to set this up correctly, you will have to grab a calendar.
The dataset has data going back to January 1, 2004. Look on a calendar to determine that this date fell on a Thursday.
-
If you want your week to report from Monday through Sunday, then jot down that the week should start on December 29, 2003, as shown in Fig. 926.
-
Create a pivot table with dates in the Row area. Right-click the date, choose Group and Show Detail and then Group.
-
In the Grouping dialog, Excel defaults to showing the entire range of dates of the dataset. If you left the Starting at: field unchanged, as shown in Fig. 927, your weeks would all start on Thursday. As shown in Fig. 928, change the 1/1/2004 date to 12/29/2003 to have your weeks start on Monday.
-
Unselect the Months selection by choosing it with the mouse. Select the Days choice. This will ungray the Number of Days: field at the bottom of the dialog. Use the spin button to move up to 7 days. See Fig. 928.
Result: The report is redrawn as a weekly report, as shown in Fig. 929.
Additional Details: Excel does not add a “Week” field to the PivotTable Field List. Instead, the field formerly containing dates now contains weeks, but is still called Date.
Gotcha: Once you group by weeks, you can no longer group by month, quarter, year, or any other selection.
Summary: It is possible to group daily dates up to weeks by using the Number of Days field in the Grouping dialog.