How to Group Dates in Excel Pivot Table

Wrangling raw, daily data in an Excel Pivot Table can quickly turn your neat report into an endless list of dates that’s impossible to read. If you’ve ever tried to analyze website traffic or sales trends, you know the frustration. This guide will walk you through exactly how to group those dates into clean, organized buckets like months, quarters, and years, all directly within a Pivot Table.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

First, Why Should You Group Dates in a Pivot Table?

Before jumping into the how-to, it’s helpful to understand why this feature is so powerful. Transforming a long scroll of individual dates into structured time periods moves you from raw data to real insight. It’s the difference between looking at a pile of bricks and seeing a finished house.

There are three main benefits to grouping your dates:

• Spotting Trends Becomes Effortless: Is your business seasonal? Do sales consistently spike at the end of each quarter? Can you see month-over-month growth? Aggregating daily data into larger timeframes makes these patterns jump off the page. Seeing sales data for January 1st, January 2nd, and January 3rd isn't nearly as useful as seeing a single, combined total for "January."
• Your Reports Become Instantly Readable: No manager or client wants to scroll through 365 rows of daily data to understand yearly performance. A summary grouped by months or quarters is professional, concise, and tells a clear story that everyone can understand at a glance.
• Improved Performance: Pivot Tables are incredibly efficient, but forcing them to display hundreds or thousands of individual date rows can eventually slow them down. Grouping dates simplifies the calculation and makes your workbook faster and more responsive.

Preparing Your Data for Grouping

Excel is powerful, but it's also particular. Before you can group dates effectively in a Pivot Table, you need to make sure your source data is set up correctly. This small bit of prep work will save you from major headaches, particularly the infamous "Cannot group that selection" error.

Follow this quick checklist:

1. Use a Dedicated Date Column: Ensure you have one column exclusively for dates. Don't mix dates with text or other numbers in the same column.
2. Ensure All Cells are Formatted as Dates: The most critical step. Your date column must be formatted as a date in Excel. To check, select the column, right-click, choose "Format Cells," and make sure the "Date" category is selected. If your dates are stored as text (a common issue when exporting from other systems), Excel won't know how to group them.
3. Eliminate Blank Cells: The grouping function can fail if there are any blank cells within your date column. The easiest way to find them is to select your data range, add a Filter (Data tab > Filter), click the dropdown on your date column, and uncheck "Select All," then scroll down and tick the box for "(Blanks)." This will show you any empty rows you need to fix or remove.
4. No Text Allowed: Similarly, having even a single cell in the date column with text like "Pending," "TBD," or "N/A" will prevent grouping. Use the filter method described above to find and remove any text-based entries.

Once your data is clean and properly formatted, you're ready to build your Pivot Table.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Step-by-Step: How to Group Dates in a Pivot Table

Now for the main event. We'll use a common example: a simple table of daily sales data with two columns: Date and Sales.

Step 1: Create a Basic Pivot Table

First, turn your data into a Pivot Table. If you’re not familiar with this process, it only takes a few seconds:

1. Click anywhere inside your data table.
2. Go to the Insert tab on the Excel ribbon.
3. Click PivotTable. Excel will automatically guess your data range.
4. Choose whether to place the Pivot Table in a new worksheet or an existing one, then click OK.

Now, from the "PivotTable Fields" pane on the right, drag the Date field into the Rows area and the Sales field into the Values area. At this point, you'll see what we wanted to avoid: a long, messy list with a separate row for every single day.

Step 2: Access the Grouping Feature

This is where the magic happens. Right-click on any cell containing a date within your Pivot Table. In the menu that appears, click Group....

Step 3: Choose Your Grouping Period (Months, Quarters, Years)

After clicking "Group," a new dialog box will pop up. This is your control center for time-based analysis.

Here, you'll see a "By" list of different time units. For a standard year-over-year report, you can select multiple options. Click to highlight Months, Quarters, and Years (hold Ctrl or Command to select more than one). Click OK.

Instantly, your Pivot Table will transform. The messy daily view is replaced with a perfectly organized, collapsible report broken down by year, quarter, and month. Excel has also cleverly added new fields (Years, Quarters) to your PivotTable Fields list, which you can use just like any other field.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

How to Group Dates by Week

Grouping by week is a common need, but the option is surprisingly missing from the default "By" list. The trick is to group by a specific number of days instead.

1. Right-click your date column in the Pivot Table and select Group....
2. In the dialog box, deselect any options that are already highlighted. Select only Days.
3. Next to "Number of days," change the value from 1 to 7.
4. Important Pro-Tip: Pay attention to the "Starting at" date. Excel defaults to the first day in your data. If you want your weeks to start on Mondays, you must change this date to the first Monday of your date range. For example, if your data starts on Jan 4, 2024 (a Thursday), you would manually change the "Starting at" date to Jan 1, 2024 (a Monday) to ensure all your 7-day buckets are correctly aligned as Mon-Sun weeks.
5. Click OK. Your data is now grouped into neat, 7-day intervals.

Grouping by Custom Date Ranges (e.g., 14-Day Periods)

The same logic for weekly grouping can be applied to any custom period. If you run marketing campaigns in 14-day sprints or want to see bi-weekly performance, simply follow the steps for grouping by week but enter 14 (or your desired number) in the "Number of days" field.

Troubleshooting: What to Do When Date Grouping Doesn't Work

Sometimes, things go wrong. If you’ve followed the steps perfectly and Excel still isn't cooperating, it's almost always a problem with your source data.

The "Cannot group that selection" Error

This is the most common error message, and it has two primary causes:

1. Blank Cells: You have a blank cell somewhere in your date column. As mentioned in the preparation section, filter your source data column to find and remove any "(Blanks)".
2. Text Cells: You have a value that is formatted as text instead of a proper date. Double-check that the entire column is set to the "Date" format. Even a single text cell will break the feature for the whole Pivot Table.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

How to Ungroup Dates

If you make a mistake or want to go back to the original daily view, ungrouping is simple. Just right-click on any of the grouped date labels in your Pivot Table and select Ungroup. Your table will immediately revert to showing individual dates.

Final Thoughts

Learning to group dates transforms an Excel Pivot Table from a simple data summarizer into a powerful analysis tool. It allows you to quickly move beyond raw daily numbers to identify meaningful trends, create clean reports for your team, and make smarter, data-driven decisions.

While mastering Pivot Tables is a valuable skill, building these reports is often just one small piece of a much larger data puzzle. The real challenge comes when you need to merge data from multiple platforms - like your Google Analytics traffic, Shopify sales, and Facebook Ads spend - to see the complete picture. Historically, this meant hours spent manually exporting CSVs and wrestling spreadsheets together. We believed this entire process of getting answers from your data could be smarter and faster. That’s why we built Graphed, where our AI handles the difficult work for you. You connect your data sources once, and then simply ask in plain English, "Show me my sales by month versus my ad spend for this year." We'll instantly build a live, real-time dashboard that answers your question, with no manual work required.