How to group dates in PivotTable

In Excel, you can group and ungroup PivotTable dates in order to make data more readable. You can group dates by seconds, minutes, hours, days, months, quarters and years. I’ll also show you how you can group data by weeks.

Here, you can download the example file.

  1. Click any cell inside a table and insert a PivotTable (Insert >> Tables >> PivotTable).
  2. Select Date and Units. When you select Date, Excel will automatically add Quarters and Years.

  1. Uncheck them. Now the data is automatically grouped by months

  1. In order to ungroup it, right-click one of the months and select Ungroup.

  1. Now, each day has its own row.

Now, you can group dates the way you want.

Group by years

  1. Right-click any row inside the date column.
  2. Click Group.
  3. From the Grouping window, click Years.

The data is grouped to the year 2018 and 2019. Two years that are present inside the table.

Group by month

  1. Right-click any row inside the date column.
  2. Click Group.
  3. From the Grouping window, click Months. We have only January and December and these two months will be displayed inside the PivotTable.

Group by days

Grouping by days displays our data the same way as ungrouped PivotTable because each row in our data represents a single day. Only the way the date is formatted will change.

You can’t change the data formatting easily because the date is not formatted as a date (number really) but as text. If you want to read more about formatting dates in PivotTables, you can read Jon’s article.

Group by weeks

Now, we are going to group dates by weeks, what’s different in this grouping is that you won’t find Weeks in the grouping Window.

But we know that a week has 7 days, so choose a day and set a number of days to 7.

This is how the result looks like.