How to Group Dates in Pivot Table Google Sheets

Staring at a pivot table with rows and rows of daily dates can feel like looking at a single tree instead of the whole forest. You have the raw data - daily sales, user sign-ups, website traffic - but spotting any meaningful trend is nearly impossible. This guide will walk you through exactly how to group dates in a Google Sheets pivot table so you can easily analyze performance by month, quarter, or year.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Why Bother Grouping Dates in a Pivot Table?

Grouping dates converts your chaotic, granular data into high-level summaries that tell a story. Instead of seeing hundreds of individual daily entries, you can answer critical questions at a glance:

• Which month had the highest sales last year?
• How did Q2 performance compare to Q1?
• Is our website traffic growing year-over-year?

By summarizing daily information into buckets like months or quarters, you turn endless rows of data into actionable insights. It’s the difference between seeing a daily sales figure of $542 on June 3rd and understanding that June's total sales were up 15% from May.

Before You Begin: Prep Your Raw Data

A successful pivot table starts with clean source data. Before creating your summary, take a moment to ensure your spreadsheet is structured properly. Your data will not group correctly if it is not clean.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Basic Data Hygiene Rules:

• No Blank Rows or Columns: Your data should be a continuous block. A blank row or column can make Google Sheets think the data range has ended.
• One Header Row: Use a single, clearly labeled header row at the top (e.g., "Date," "Product Category," "Revenue").
• Consistent Date Formatting: This is the most important rule. Every value in your date column must be a valid date recognized by Google Sheets. If you have a mix of date formats or plain text (like "November 5th, 2023" spelled out), the grouping feature won't work.

To fix inconsistent date formatting, highlight your date column, go to Format > Number, and select Date. This forces all values into a standard date format that pivot tables can understand.

Here’s a simple example of a well-formatted data set ready for a pivot table:

How to Group Dates: The Standard Method

Once your data is clean, you can create a pivot table and group your dates. Google Sheets makes this incredibly easy with a built-in function.

Step 1: Create Your Pivot Table

1. Click anywhere inside your data range.
2. Go to the menu and select Insert > Pivot table.
3. A dialog box will appear. Confirm the data range is correct and choose whether to insert the pivot table on a new sheet or the existing one. For clarity, "New sheet" is usually the best choice.
4. Click Create.

Step 2: Set Up the Basic Pivot Table

Now you have a blank pivot table and the Pivot table editor pane on the right. Let's add our fields to it.

• Under "Rows," click Add and select your date column (e.g., "Date").
• Under "Values," click Add and select the metric you want to measure (e.g., "Revenue"). Ensure it is set to "Summarize by: SUM."

At this point, you'll have a pivot table that looks much like your source data - a long list of individual dates with their corresponding revenue. This is the problem we're here to solve.

Step 3: Create the Pivot Date Group

Here's where the main action happens. To start grouping, follow this simple step:

1. In the generated pivot table, right-click on any of the date values in the first column.
2. From the context menu that appears, hover over Create pivot date group.
3. A fly-out menu will appear with several grouping options.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Step 4: Choose Your Grouping Method

Google Sheets offers several handy presets. Let's look at the most common ones.

Group by Year-Month

This is arguably the most useful option for monthly tracking. Selecting Year-Month will transform your daily data into a tidy summary of total revenue per month.

Your messy pivot table from Step 2 will instantly roll up into something like this:

• 2023-Jan: $1,420.50
• 2023-Feb: $2,150.75
• 2023-Mar: $1,890.00
• ...and so on.

Group by Year-Quarter

If you do quarterly business planning, this option is perfect. Selecting Year-Quarter aggregates your data into three-month blocks, helping you align with broader business cycles.

Your pivot table would update to look like:

• 2023-Q1: $5,461.25
• 2023-Q2: $6,300.00
• ...etc.

Other Grouping Options

• Year: Great for high-level annual comparisons.
• Quarter: Groups all data by quarter, regardless of the year (i.e., all Q1s are combined).
• Month: Excellent for analyzing seasonality. This combines all a month's data across multiple years (e.g., you can see how all Januarys typically perform).
• Day of the week: Helps you see if certain days consistently perform better (e.g., Are weekends your highest revenue-driver?).

The Helper Column Method: For Custom Groupings

The standard "Create pivot date group" feature is powerful, but what if you need a grouping it doesn't offer, like by week, fortnight, or a custom sales season? For that, we turn to a simple but effective technique: the helper column.

A helper column is a new column you add to your original source data to create your own custom labels. The pivot table can then use this new column for grouping.

Example: Grouping by Week Number

Let's say you want to see revenue totals for each week of the year.

1. Go back to your source data sheet.
2. Insert a new column next to your date column. Let's call it "Week Number."
3. In the first cell of this new column (e.g., D2), enter the following formula, assuming your dates are in column A starting from A2:

=WEEKNUM(A2)

1. Press Enter, then drag the fill handle (the small blue square at the corner of the cell) down to apply this formula to all your rows.
2. Now, go back to your pivot table sheet. In the Pivot table editor, you'll need to update your data range to include this new D column. Typically, you do this by clicking the grid icon next to the "Data range" at the top of the editor and re-selecting your entire data set with the new column.
3. Finally, in the "Rows" section of the editor, remove the "Date" field and Add your new "Week Number" field.

Your pivot table will now be neatly grouped by the week number, showing aggregated data for week 1, week 2, and so on.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Troubleshooting: "Create pivot date group is greyed out."

The most common issue people face is when the "Create pivot date group" option is disabled or greyed out. This almost always happens for one reason: Google Sheets doesn't recognize your date column as pure dates.

Here’s how to fix it:

• Check for Text Values: Scan your date column for any cells that look like dates but are actually stored as text. Sometimes a tiny apostrophe ' at the beginning of a cell can cause this.
• Find Blank Cells: A blank cell within your date column can also prevent the grouping feature from working. Either remove the entire row or enter a valid date.
• Re-format the Column: The quickest solution is often to highlight the entire date column in your source data, then go to Format > Number > Date. This will force a consistent format on all cells and often reveal any text values posing as dates.

Final Thoughts

Grouping dates transforms a raw data log into a powerful strategic asset. Instead of getting lost in daily noise, you can now zoom out to see yearly trends, quarterly performance, or monthly seasonality with just a couple of clicks. Whether you use the simple right-click method or create a custom helper column, you're now equipped to analyze time-based data far more effectively in Google Sheets.

Of course, building and refreshing pivot tables - especially when you’re pulling data from multiple sources like Google Ads, HubSpot, or Shopify - can be a tiring weekly chore. This is the exact manual loop we set out to eliminate with Graphed. We connect to your tools so you can simply ask for what you need - "Show me my revenue by month from Shopify and which ad campaigns drove it" - and get a live, automated dashboard in seconds. If you find yourself spending more time updating reports than analyzing them, it might be the faster way to get the answers you need.