Grouping dates in a Pivot Table in Google Sheets is a powerful way to create monthly, quarterly, and yearly summary reports. It helps you analyze trends in your data without needing formulas like QUERY or SUMIF, which can be tricky for beginners. Even a novice can quickly summarize data using this feature.

For example, suppose you track your day-to-day expenses in Google Sheets. By grouping the date column in a Pivot Table, you can easily see:

• How much you spend each month
• Which month had unusually high expenses
• Quarterly or yearly spending trends

This tutorial is part of our complete Google Sheets Pivot Table Tutorial, covering everything from basics to advanced date grouping techniques.

How to Group Dates in a Pivot Table in Google Sheets

The sample data consists of Date, Item, Area, and Qty in columns A through D. It’s a very basic dataset designed to explain the core concept of date grouping. See the screenshot below.

Step 1: Create a Pivot Table

Start with your sample data in Google Sheets. Before grouping dates, you need a Pivot Table:

1. Select your data range (e.g., A1:D9).
2. Go to the Insert menu → Pivot table.
3. Choose where the Pivot Table should appear (new sheet or existing sheet). Click Create.

Google Sheets will create a new Pivot Table at the chosen location. If you selected a new sheet, a new tab (e.g., “Pivot Table 1”) will open. The Pivot Table editor panel will appear on the right, and the sheet will show a table skeleton ready for you to add Rows, Columns, and Values.

Step 2: Add Rows, Columns, and Values

Set up your Pivot Table:

• Rows: Date
• Columns: Item
• Values: Qty (Summarize by SUM)

Now, if any items repeat on the same date, their quantities will automatically sum, giving you an initial grouping.

Step 3: Find the Date Grouping Option

Date grouping is not available in the Pivot Table editor panel—it’s hidden in the right-click shortcut menu:

1. Go to a cell in the Pivot Table containing a date (e.g., A3:A7).
2. Right-click the cell → select Create pivot date group.
3. You’ll see multiple options for grouping dates: Month, Year-Month, Quarter, Year-Quarter, or Year.

Step 4: Group by Month or Year-Month

• Month: Groups all dates by month, ignoring the year. For example, January 2018 and January 2019 are combined.
• Year-Month: Groups dates by both year and month, keeping January 2018 and January 2019 separate.

Tip: Use Year-Month if your data spans multiple years.

Step 5: Group by Quarter or Year-Quarter

Quarters are commonly used in financial reporting:

• Quarter: Groups months into quarters, ignoring the year.
• Year-Quarter: Groups quarters by year, keeping multiple years separate.

Right-click on a date cell → Create pivot date group > Quarter or Year-Quarter.

Step 6: Group by Year

To create a yearly summary:

1. Right-click any date cell in your Pivot Table.
2. Select Create pivot date group > Year.

This will summarize all data for each year.

Why Date Grouping Might Not Work

If date grouping is not working in your Pivot Table, check these common issues:

1. Dates are stored as text – Make sure your date column is formatted as Date.
2. Empty cells – Fill or remove blank cells in the date column.
3. Incorrect range selection – Ensure you are right-clicking a date cell inside the Pivot Table.

Fixing these will restore the date grouping functionality.

Final Thoughts

Grouping dates in Google Sheets Pivot Tables makes data analysis fast and intuitive. Whether you want monthly expense reports, quarterly sales summaries, or yearly trend analysis, this feature saves you time and eliminates the need for complex formulas.