How to Group Dates (by week) in Pivot Table in Excel

This post will guide you how to group a pivot table by day of week in Excel. How do I group data by week number in pivot table in Excel.

Group dates by week in Pivot Table

It should be very useful to group dates by week, days, year, months, etc. And it can help you to analyze your data to get the different views by dates, weeks, months, years, etc.

Assuming that you have a list of data in range A1:B15 as shown below:

group dates by week pivot table1

You need to create a pivot table based on the data in A1:B15, and then group dates by week (7 days) in the pivot table. How to do it. Just do the following steps:

#1 select the range of cells that you want to create pivot table based on.

group dates by week pivot table1

#2 go to INSERT tab, click the PivotTable command under the Tables group. And the Create PivotTable dialog will appear.

group dates by week pivot table2

#3 select Existing Worksheet radio button in the Create PivotTable dialog box, and select one cell reference as the destination to place the pivot table. Click Ok button.

group dates by week pivot table3

#4 you will see that a pivot table is created based on the selected range of cells.

#5 select any cell in the date column in the existing Pivot table. Right click on it, and select Group… from the popup menu list. And the Grouping dialog will open.

group dates by week pivot table4


#6 select Days option from the By list box in the Grouping dialog box. And deselect any other selected options. Then enter number 7 in the Number of days text box. Click Ok button.

group dates by week pivot table5

#7 you would notice that your pivot table has been grouped by weeks starting with 1/1/2019 as shown below:

group dates by week pivot table6

Note: you can use the above steps to group dates by months, quarters or years in the pivot table.


Leave a Reply