Grouping dates in a pivot table collapses individual dates into larger time periods — months, quarters, or years — so you can see patterns instead of a list of single days

When you put a date column into a pivot table's row or column area, Excel and Google Sheets treat each date as its own category. If you have 500 transactions across 2024, you get 500 rows instead of 12 months. Grouping dates fixes this by automatically bucketing them into the time period you choose. The steps differ slightly between Excel and Google Sheets, but both let you group by day, week, month, quarter, or year.

You need a pivot table that already exists with dates in the row or column labels. If you have not built a pivot table yet, you will need to create one first — select your data, insert a pivot table, and drag your date column into the Rows area. Once that is done, grouping takes about 30 seconds.

Key Takeaways

  • Grouping dates in a pivot table turns individual dates into months, quarters, or years so you can see totals by time period instead of by single day.
  • In Excel, right-click any date in the pivot table's row or column labels, select Group, and choose your time period from the dialog box.
  • In Google Sheets, click any date in the pivot table, open the Data menu, select Create a pivot table, and use the date grouping option in the pivot table editor on the right side.
  • You can group by multiple time periods at once — for example, by year and month together — to show both the total for the year and the breakdown by month within it.
  • If grouping does not appear as an option, check that your column is formatted as a date, not text.

How to group dates in Excel

Open your pivot table and locate any cell in the row or column area that contains a date. Right-click on that cell. A context menu will appear with several options. Look for and click Group.

A dialog box titled "Grouping" will open. You will see a list of time periods: Seconds, Minutes, Hours, Days, Months, Quarters, Years. Select the period you want. For most business use, Months or Quarters work well. Click OK. Excel will when ready regroup all the dates in that area into the period you chose.

If you want to group by both month and year — so you see "January 2024", "February 2024", and so on, rather than just "January", "February" — do not close the Grouping dialog. Instead, hold Ctrl and click on the other time periods you want to add. Select both Months and Years, then click OK. Excel will create nested grouping with years on the outside and months nested inside.

How to group dates in Google Sheets

In Google Sheets, the grouping option lives in the pivot table editor, not in a right-click menu. Click on any date value in your pivot table. Open the Data menu at the top and select Create a pivot table. This will open or refresh the pivot table editor panel on the right side of the screen.

In the pivot table editor, find the field that contains your dates — it will be listed under Rows or Columns depending on where you placed it. Click on that field name. A small menu will appear with options including "Order" and "Summarize by". Below those, you will see a section for grouping. Click on the grouping option and choose your time period: Day, Week, Month, Quarter, or Year.

Google Sheets will explore the grouping when ready. Unlike Excel, Google Sheets does not require you to close a dialog — the change takes effect as soon as you select it. To add multiple grouping levels, repeat the process for the same field and select a second time period. Google Sheets will stack them automatically.

When grouping does not work

The most common reason grouping fails is that your date column is formatted as text instead of as a date. If you right-click a cell in Excel and the Group option is grayed out, or if Google Sheets does not show grouping options, check the source data. Select the original date column in your raw data (before the pivot table), right-click, and choose Format Cells (Excel) or Format (Google Sheets). Make sure the format is set to Date, not Text or General.

Another reason grouping might not appear is that you are clicking on a value in the data area of the pivot table (the numbers being summed) rather than on a label in the row or column area. Grouping only works on the labels themselves. Click on a date label, not on a sum or count next to it. If you are still unsure which cell to click, look for the date values that appear in the leftmost column or top row of the pivot table — those are the labels you need.

Changing or removing grouping

To change the grouping period after you have already grouped — for example, from Months to Quarters — right-click on any grouped date in Excel and select Group again. The dialog will show your current grouping. Uncheck the period you no longer want and check the one you do want, then click OK.

In Google Sheets, click on the date field in the pivot table editor and change the grouping option to a different time period. The pivot table will update when ready. To remove grouping entirely in Excel, right-click on a grouped date and select Ungroup. In Google Sheets, click the date field in the editor and select "Don't group" from the grouping menu. Both will return your pivot table to showing individual dates.

Grouping by custom date ranges

Excel and Google Sheets both offer only the standard time periods — day, week, month, quarter, year. If you need to group by a custom range — for example, by fiscal year or by 30-day periods — neither tool will do it automatically. Your best option is to create a helper column in your raw data that assigns each date to the custom period you want, then use that column in the pivot table instead of the date column itself.

For example, if you want to group by fiscal year starting in July, add a new column to your raw data with a formula that converts each date to its fiscal year label ("FY 2024", "FY 2025", and so on). Then rebuild the pivot table and drag this new column into the Rows area instead of the original date column. The pivot table will group by your custom periods automatically. This approach takes a few extra minutes upfront but gives you complete control over how dates are bucketed.

Frequently Asked Questions

Can I group dates by week in both Excel and Google Sheets?

Yes. In Excel, right-click a date in the pivot table and select Group, then choose Weeks. In Google Sheets, click the date field in the pivot table editor and select Week from the grouping menu. Both will group dates by the seven-day week starting on Sunday (or Monday, depending on your regional settings).

What happens to my pivot table if I change the grouping after I have already built it?

The pivot table updates when ready. All the data is recalculated and reorganized into the new time period. Your sums and counts stay the same — only the way they are grouped changes. You can switch between grouping periods as many times as you want without losing data.

Can I group dates by year and month at the same time?

Yes. In Excel, hold Ctrl and select both Years and Months in the Grouping dialog, then click OK. In Google Sheets, the pivot table editor will automatically nest multiple grouping levels if you select more than one. You will see years as the outer group and months nested inside each year.

Why does my date column show as text instead of dates in the pivot table?

The source data is formatted as text. Go back to your raw data, select the date column, and format it as Date, not Text or General. Then refresh or rebuild the pivot table. The grouping option will then appear.

Can I group dates by fiscal year instead of calendar year?

Neither Excel nor Google Sheets offers fiscal year grouping directly. Create a helper column in your raw data that assigns each date to its fiscal year (for example, "FY 2024" or "FY 2025"), then drag that column into the pivot table instead of the date column itself.