THE EXCEL GUIDE Mastering Data & Spreadsheets

Home
Data Analysis and Sorting

Group Pivot Table Excel: Dates, Numbers & Text

Learn how to group dates, numbers, and categories.
By:

Why does your pivot table show 365 rows of daily sales when your director only ever asks about quarterly trends? That's not a data problem. It's a grouping decision you haven't made yet, and the default is costing the reader time they don't have.

After fifteen years building reporting dashboards at a financial services firm, I'd argue grouping is the most under-designed feature in Microsoft Excel. Most analysts get their data into a PivotTable and stop. They accept whatever Excel auto-applies and move on. That's the same logic as accepting default chart colors: Excel made the choice, not you. This guide covers how to group pivot table data in Excel by date, number, and custom text category, plus how to fix it when Excel refuses to cooperate.

How to group pivot table data in Excel by date, number, and category
Grouping transforms raw daily data into the reporting intervals your audience actually uses.

What You'll Be Able to Group, and What to Check Before You Start

There are three types of pivot table grouping in Microsoft Excel. Date grouping lets you collapse daily data into months, quarters, or years. Numeric grouping lets you bucket continuous values (sales totals, ages, scores) into defined ranges. Custom text grouping lets you manually select rows and assign them to a named category, useful for combining regions or product lines that don't share a natural field value.

The Three Types of Grouping and When Each One Applies

Date grouping is the most common use case and the one most likely to go wrong silently. Number grouping is underused: it's excellent for KPI reporting where you want to encode decision thresholds directly into the table structure, like flagging orders under $500 versus over. Text grouping is manual and slower, but it's the right tool when your source data doesn't have a clean category column and you can't modify it.

Before you try any of them, if you haven't worked through your data quality yet, the Preparing Data for Analysis in Excel guide is worth a read first.

The Data-Quality Check That Prevents Most Grouping Errors

Excel can't group a date column that contains even one blank cell or one text value mixed in with real dates. Run a quick filter on your date or number column before building the pivot table. Look for blanks, look for cells formatted as text, and fix them at the source. Trying to repair this inside the pivot table after the fact is slower and more frustrating than a two-minute pre-check.

Step 1: Group Dates by Month, Quarter, or Year Inside Your Excel Pivot Table

With clean source data confirmed, you're ready to group. Here's the process:

  1. Click any date cell in the Row Labels or Column Labels area of your pivot table.
  2. Right-click and select Group. The Grouping dialog opens.
  3. Select your interval from the list: Seconds, Minutes, Hours, Days, Months, Quarters, or Years. You can select more than one.
  4. Selecting both Months and Years, for example, gives you a hierarchy that lets readers drill down.
  5. Click OK. Excel collapses the dates into your chosen grouping immediately.

For most executive-facing reports, Quarters is the right starting point. Monthly is appropriate when the reader is managing against a monthly budget. Daily is almost never the right default for a summary report, and I've seen it cause real confusion. In 2016, a dashboard I built for a VP team used weekly grouping when the business review cadence was quarterly, and two directors spent a full meeting arguing about a trend that didn't actually exist at the quarterly level. Wrong grouping misleads without announcing itself.

Why Auto-Grouping Behaves Differently in Excel 2016 and Later

Starting with Excel 2016, Microsoft 365 automatically groups dates by year, quarter, and month the moment you drag a date field into a pivot table. That's occasionally helpful and often surprising. To turn it off, go to File → Options → Data and uncheck Disable automatic grouping of Date/Time columns in PivotTables.

If you inherit a workbook and the pivot table is already auto-grouped in a way you don't want, right-click the date field and select Ungroup, then re-group with the intervals you actually need.

Step 2: Group Numbers into Ranges and Manually Group Text Items in Your Pivot Table

Once you've handled date grouping, numeric and text grouping follow the same right-click path, but the logic behind each is different enough to treat them separately.

Grouping Numbers into Buckets Using the Starting At and Ending At Fields

Right-click any numeric value in your pivot table's row or column area and select Group. The dialog asks for a Starting at value, an Ending at value, and a By interval. Say you're looking at order values from $0 to $10,000 and you want to understand order frequency by $1,000 buckets. Set Starting at to 0, Ending at to 10000, and By to 1000. Excel builds the bins and labels them automatically.

The bin size is a design decision. A $1,000 interval might be right for a sales report; a $500 interval might be too granular for an executive summary and exactly right for an operations team managing fulfillment thresholds. The interval you choose communicates what resolution of decision-making you're expecting from the reader.

Every element on a dashboard should be defensible. If you can't say why you chose $1,000 over $500, you chose it by default.

Selecting and Grouping Text Rows by Hand for Custom Categories

For text items, there's no dialog. Here's how to do it:

  1. Click the first row label you want in a group.
  2. Hold Ctrl and click each additional label you want to include.
  3. Right-click and choose Group. Excel creates a new grouping field labeled "Group1."
  4. Rename it immediately to something meaningful.
  5. Repeat for each custom category you need.

This is slow for large label sets, but it's the right tool when you're combining regional territories or product lines that don't share a common attribute in the source data. If you're building a reporting dashboard and want to see how grouping connects to visual design, Pivot Charts with Conditional Formatting picks up right where this leaves off.

Why Grouping Breaks, and How to Fix 'Cannot Group That Selection' in Excel

"Cannot group that selection" is one of Excel's least informative error messages. It appears for three distinct reasons, each with a different fix:

  1. Blank cells in your source data column. One blank is enough. Go back to the source range, fill or delete the blank, refresh the pivot table, and try again.
  2. Mixed data types. A date column with even one cell stored as text will block grouping for the entire field. Run Data → Text to Columns on the date column (clicking Finish without changing anything forces Excel to re-evaluate the cell type), then refresh.
  3. Shared pivot cache conflict. See the section below.

The Pivot Cache Conflict That Groups Two Pivot Tables at Once

When two pivot tables are built from the same source range, Excel may assign them the same pivot cache. Change the grouping in one and it changes in both, automatically, silently, with no warning. As of 2026, Excel still doesn't flag this during pivot table creation.

The fix is to give each pivot table its own cache. The simplest method: select your source data, copy it, paste as values into a new sheet, and build the second pivot table from that range. They'll now have independent caches and independent grouping. You can also use a named table reference for one and a raw range for the other: Excel treats these as separate caches.

For a broader look at how pivot table setup decisions affect reporting quality, the Introduction to Pivot Tables for Dashboards guide covers the structural choices worth making before you build.

Common Grouping Mistakes, and When to Use GROUPBY Instead of a Pivot Table

The most common mistake is grouping before fixing the source data. Run your data quality check first, every time. The second is forgetting to ungroup before re-grouping: if you try to apply a different interval without ungrouping first, Excel sometimes compounds the groupings rather than replacing them. Right-click and Ungroup, then re-group from scratch.

The third mistake is ignoring the shared pivot cache problem until a stakeholder notices their table changed unexpectedly.

One thing worth knowing for 2026: if you're on Microsoft 365 and your grouping task is relatively simple (summarizing sales by region or bucketing data by year), the GROUPBY and PIVOTBY functions may be faster than building a full PivotTable. They're formula-based, so they update dynamically without a manual refresh and don't carry pivot cache complexity. The trade-off is that date grouping in PIVOTBY requires you to handle the date math yourself using functions like YEAR() or TEXT(). There's no dialog. For complex, multi-level date hierarchies, a traditional PivotTable is still the cleaner tool. For a simple one-level summary, GROUPBY is worth considering.

For anyone still getting comfortable with pivot tables generally, the Excel for Beginners starter guide has a solid foundation section before you get into grouping decisions.

Frequently Asked Questions

How do I group dates by month in an Excel pivot table?

Right-click any date cell in the Row or Column Labels area of your pivot table and select Group. In the Grouping dialog, select Months and add Years if you want a drill-down hierarchy. Click OK and Excel collapses the daily dates into monthly summaries automatically.

Why can't I group that selection in my pivot table?

The "Cannot group that selection" error almost always means your source data has blank cells or mixed data types in the field you're trying to group. Check for blanks and for date cells stored as text, fix them at the source, refresh the pivot table, and try grouping again.

How do I stop two pivot tables from grouping together?

Two pivot tables built from the same source range often share a pivot cache, which means grouping changes in one affect the other. To break this link, build one pivot table from the original range and the second from a separate copy of the data. Excel will treat them as independent caches.

When should I use GROUPBY instead of a pivot table?

The GROUPBY and PIVOTBY functions in Microsoft 365 are worth using when your summary is simple (one or two levels of grouping) and you want results that update automatically without a manual refresh. For complex date hierarchies or multi-dimensional analysis, a traditional PivotTable is still easier to configure and maintain.