A PivotTable is only as clear as its rows. Feed it a year of daily dates, and you get 365 cramped lines. Feed it raw ages, and every single value becomes its own row. That detail hides the very pattern you want to see. Grouping solves this. It rolls fine detail into clean buckets, so months, ranges, and custom sets appear instead of noise.
This guide shows how to group any field the right way. First, it explains the three kinds of grouping. Then it walks through dates, numbers, and custom text sets. Real examples and a full troubleshooting section follow. By the end, you will tame even the messiest field in a click or two.
What Grouping Does
Grouping bundles many values into fewer buckets. It works on three kinds of fields. Dates roll into months, quarters, or years. Numbers fall into equal ranges, and text items join custom sets. The infographic below shows all three.
The payoff is a report you can actually read. Instead of hundreds of rows, you see a handful. Moreover, the totals stay exact, since nothing is deleted. As a result, trends jump straight off the page.
Group Dates Into Periods
Dates are the most common thing to group. You can roll them into months, quarters, and years. Better still, you can pick several levels at once. Then Excel builds a neat drill-down hierarchy, as shown below.
Group Numbers Into Ranges
Numbers group into equal bands called bins. You set a starting point, an end, and an interval. Then Excel sorts every value into the right band. Consequently, a messy column becomes a clean distribution.
Build Custom Text Groups
Text fields do not bin automatically. Instead, you pick the items and group them by hand. This is perfect for combining regions or products. For example, you can merge several states into one sales zone.
Ungroup and Regroup Safely
Grouping is never permanent, which is reassuring. You can undo any grouping in a moment. Simply right-click the grouped field and choose Ungroup. After that, the original detail returns untouched.
Grouping and Shared PivotTables
Here is a subtle point worth knowing. Pivots built from one source can share a cache. Grouping a field then affects every pivot on that cache. Because of this, one change can ripple across a report.
Troubleshooting Grouping
All three problems below are the most common. Each has a clear cause and a quick fix.
Excel says "Cannot group that selection"
This error almost always points to messy data. A date or number column may hold blanks or stray text. Even one text entry in a date column breaks grouping. First, go back to the source and scan that column. Then fill any blanks and fix values stored as text. A quick way is to multiply numbers by one, or use Text to Columns for dates. After you refresh the pivot, the Group command works again.
Grouping changed a different report
This surprises many people the first time. Two pivots from the same source share one cache. So a group you set in one appears in the other. First, decide whether that shared behaviour is what you want. If it is, nothing is broken, and the link is by design. However, if you need separate groups, build the second pivot from its own copy of the data. That gives each report an independent cache.
Dates grouped automatically and I did not want it
Newer Excel versions group dates as soon as you add them. That default helps most people, yet it annoys others. To take back control, open File, then Options, then Data. There you can switch off automatic date grouping. After that, dates stay as raw values until you group them yourself. You can still group them manually whenever you choose.
Frequently Asked Questions
- How do I group dates by month in a PivotTable?Right-click any date in the pivot and choose Group. Then select Months, and add Quarters or Years too. Excel builds a drill-down hierarchy for you. So you can expand a year down to a month.
- Can I group numbers into ranges?Yes, drag the number field to Rows first. Then right-click a value and choose Group. Set a start, an end, and a bin size. As a result, values fall into equal ranges.
- Why do I get "Cannot group that selection"?Usually the column holds blanks or text. Even one bad value in a date column blocks grouping. So clean the source and fix any text-formatted dates. Then refresh, and grouping works.
- How do I stop Excel grouping dates automatically?Open File, then Options, then the Data page. There you can turn off automatic date grouping. After that, dates stay as raw values. You can still group them by hand later.