Grouping Pivot Data — Dates, Number Ranges & Custom Groups

Grouping bundles individual items into bigger categories — dates into months, numbers into ranges, similar items into one group. It turns a pivot from a long list into a readable summary.

Table of Contents

1. Why Group Data
2. Group Dates by Month & Quarter
3. Group Numbers into Ranges
4. Group Text Items
5. Ungroup
6. Do It with AI

Why Group Data

Raw pivot rows are granular. If you have daily dates, a pivot shows one row per day. Grouping collapses those rows into a level that reads better — monthly, quarterly, or in custom ranges.

Why it is useful: a monthly total is far easier to analyze than 365 daily rows, and grouping numbers into brackets (like price bands) reveals patterns that individual values hide.

Group Dates by Month & Quarter

1. Drag the Date field into the Rows area so each date is a row.
2. Right-click any date in the pivot.
3. Choose Group.
4. In the dialog, tick Months and Years (so the same month in different years stays separate).
5. Click OK.

The pivot now shows one row per year, nested with one row per month, with Total sums.

Simple explanation: Grouping added two extra "bucket" levels above the date. You can also pick Quarters instead of months if that's how your business reviews performance.

Best practice: always group by Years and Months together. If you group only by Months, January of 2025 and January of 2026 collapse into one row and the totals are wrong.

Group Numbers into Ranges

You can turn exact numbers (like revenue) into bands:

1. Put a numeric field in Rows (for example Units).
2. Right-click any number → Group.
3. Set the dialog values:
   • Starting at: 4 (the bottom of your range)
   • Ending at: 15
   • By: 5
4. Click OK.

The pivot now shows bands like 4-9, 9-14 — every unit count summarized into its own bracket.

How the "By" number works: it's the width of each band. Changing it from 5 to 10 doubles the band width and halves the number of bands.

Group Text Items

Grouping isn't only for dates and numbers — you can bundle label items together.

1. Put a text field (like Product) in Rows.
2. Select the row labels you want to combine (hold Ctrl to pick several).
3. Right-click → Group.

Excel wraps them in a new item named Group1. Click it, type a new name (for example "Top Sellers"), and press Enter. The selected items now fold under that group.

Use case: combining East and South into a "New Regions" group for a report, without changing the source data.

Ungroup

To revert, right-click anywhere inside the grouped field and choose Ungroup.

• Ungrouping a date or number field removes the extra bucket levels.
• Ungrouping a text group splits the bundled items back into separate rows.

Common Mistakes

• Months without Years: selecting only Months merges the same month across different years, producing inflated totals.
• Text dates won't group: if your date column is stored as text, the Group option is grayed out. Convert the column to real dates first (Data → Text to Columns, or a DATEVALUE formula).
• Grouping when blanks exist: blank values inside the field can make grouping behave oddly — clean the data first.

Do It with AI

• Copilot in Excel: select the pivot's date field and type Group the dates by month and year. Copilot applies the grouping to the active pivot for you.
• ChatGPT: stuck on the Group dialog numbers? Prompt Here are revenue values from 100 to 5000. Propose 5 equal bands and the "By" interval to set in Excel's Group dialog. It does the band-width math. Then ask Grouping by Months only broke my totals across years — why? for the classic explanation.