Pin Me

Microsoft Excel: Group By Week In A Pivot Table

written by: Mr Excel•edited by: Tricia Goss•updated: 11/18/2011

Problem: In the last example you noticed that the Grouping dialog allows you to group by second, minute, hour, day, month, quarter, and year. You need to group by week.

  • slide 1 of 2

    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.

    1) 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.

    2) Create a pivot table with dates in the Row area. Right-click the date, choose Group and Show Detail and then Group.

    3) 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.

    4) 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.

    See all Microsoft Excel tips

  • slide 2 of 2


    Fig. 926Fig. 927Fig. 928Fig. 929