K2 Tech Update | Tech Tip
Grouping PivotTable Data By Weeks
Those who use PivotTables extensively know that, on demand, PivotTables will automatically group data together by a number of time and date options, including days, months, quarters, and years. Notably absent from the automatic grouping options is weeks. However, by understanding how to apply the grouping options that are available, we can cause PivotTables to group based on weeks, with our choice of the starting date.
Begin by creating a basic PivotTable similar to the one shown in Figure 1 based on the daily data.
Figure 1 - Basic PivotTable Based on Daily Data
Next, right click on any of the dates and choose Group from the resulting pop-up menu to open the Grouping dialog box. Select the Days option and set the Number of days option to “7” as shown in Figure 2.
Figure 2 - Grouping Date Data by Weeks in a PivotTable
Upon clicking OK, the PivotTable data groups based on seven-day periods, as shown in Figure 3.
Figure 3 - PivotTable Grouped by Weeks
If you wish to adjust the starting date for the grouping, uncheck the Starting at checkbox and specify the new starting date; for example, in the PivotTable shown above, to start the groupings on a Monday enter "1/4/2010" as shown in Figure 4.
Figure 4 - Specifying a Grouping Date that Begins on Monday
The PivotTable groupings will change to begin on Monday and continue through Sunday, as shown in Figure 5.
Figure 5 - PivotTable Grouped by Weeks with Groupings Beginning on Mondays
For a video presentation of this tip, visit www.tinyurl.com/k2tips48