How To Group By Week In Pivot Table?

Group By Week In Pivot Table With A Helper Column

In Excel, you can also add a helper column to calculate the week number which relative to the date, and then select the data range including this field to create a pivot table. Please do with following steps:

1. First, please calculate the week numbers to the date, enter this formula:

=YEAR(D2)&"-"&TEXT(WEEKNUM(D2,2),"00")

into a blank cell beside your data, (D2 indicates the date cell that you want to use), see screenshot:

2. Then drag the fill handle over the range that you want to apply this formula, and the week numbers have been filled in this helper column, see screenshot:

3. Then select the whole data range including the field you add in the above step that you want to use for creating pivot table, and click Insert > PivotTable > PivotTable, see screenshot:

4. In the Create PivotTable dialog, choose a new worksheet or a range of current worksheet to place the pivot table under Choose where you want the PivotTable report to be placed section.

5. Click OK, then drag the fields to their relative field list box and put the Week field at the top in the Row Labels section, and you can see the data has been grouped by the week, see following screenshot: