Group Numbers Into Ranges in a Pivot Table

To group numbers in a pivot table, right-click a number in the row labels, choose Group, and tell Excel where to start, where to end and how wide each group should be. Instead of one row for every age, price or score, you get tidy ranges such as 20-29 and 30-39. This guide is for anyone who builds pivot reports, and it covers the Group dialog, uneven bands, the "Cannot group that selection" error and a formula check.

Why group numbers in a pivot table?#

A pivot table normally gives every distinct number its own row. With 500 employees, that could be 40 rows of ages, each with one or two people. Grouping rolls the numbers into ranges, often called bins or bands, so you can see the pattern at a glance.

The examples use this sheet in A1:C8:

If you haven't built a pivot before, start with the pivot tables lesson and come back.

How to group numbers into ranges#

Build the pivot first, then group the number field:

1. Click inside the data and choose Insert > PivotTable, then OK.
2. Drag Age to Rows and Sales to Values. Drag Employee to Values too; because it is text, it shows as Count of Employee.
3. Right-click any age in the pivot and choose Group. (You can also use PivotTable Analyze > Group Field.)
4. In the Grouping box, set Starting at to 20, Ending at to 59 and By to 10.
5. Click OK.

The pivot now looks like this:

Excel fills Starting at and Ending at with the smallest and largest values in the data. Round numbers such as 20 and 59 give cleaner labels than 23 and 52. With whole numbers, a group labelled 20-29 holds 20 through 29. More counting tricks are in how to count in a pivot table.

How Starting at, Ending at and By work#

The three boxes decide every group:

Any value below Starting at or above Ending at goes into an extra group at the top or bottom, labelled with < or >. That is useful for outliers: set Starting at to 20 and every younger employee lands in one "under 20" group instead of many small ones.

Every group has the same width. You can't make one group 18-24 and the next 25-34 with these boxes. For that, see the section on uneven bands below.

Group numbers into uneven bands#

When the bands you need are not equal, you have two options.

Group items by hand. Select the items for one band (hold Ctrl on Windows or ⌘ on a Mac and click each one), right-click and choose Group. Excel makes a group called Group1. Click its label and type a better name, such as Under 30. Repeat for each band. This works for one-off reports but gets fiddly with many values.

Add a helper column. Put the band in the source data, then use that column in the pivot. With a small band table in G2:H5 (lower limits 0, 25, 35, 50 in G and labels in H), this returns each person's band:

=VLOOKUP(B2,$G$2:$H$5,2,TRUE)

TRUE asks for an approximate match: it finds the largest lower limit that is not above the age. The band table must be sorted from smallest to largest. The guide to VLOOKUP approximate match and the age group formula guide explain this pattern in detail. Because the band is now a normal column, it survives refreshes and works in any pivot.

Why does Excel say "Cannot group that selection"?#

This message means at least one item in the field is not a number. Excel can only group a field when every value is numeric. Check the source column for:

• Blank cells. Fill them in or remove those rows.
• Text, such as "N/A" or "unknown".
• Numbers stored as text, often from imports. They usually sit on the left of the cell. Convert them with Data > Text to Columns > Finish, or with VALUE.

After fixing the data, refresh the pivot (Alt+F5 on Windows, or PivotTable Analyze > Refresh) and try Group again.

Two more things can block grouping. A pivot built with Add this data to the Data Model may not offer numeric grouping, so use a helper column there. And pivots made from the same source share their grouping: group ages in one pivot and a second pivot on the same data shows the same groups. Grouping dates follows the same rules, as the guide to grouping dates in a pivot table shows.

To undo grouping at any time, right-click a group and choose Ungroup.

Check the groups with COUNTIFS#

COUNTIFS gives the same counts as a grouped pivot and updates as soon as the data changes, with no refresh. Use the number column twice, once for the lower limit and once for the upper:

=COUNTIFS(B2:B8,">=30",B2:B8,"<40")

This returns 3, matching the 30-39 row. For the total sales in that band, swap in SUMIFS: =SUMIFS(C2:C8,B2:B8,">=30",B2:B8,"<40") returns 14,900.

Using >= for the lower limit and < for the next group's start means every value lands in exactly one band, even decimals like 39.5. Build a column of lower limits and the formulas fill down into a complete frequency table. The COUNTIFS function guide lists every argument.

Practise it#

Grouping and counting by band show up in pivot interview tasks and real HR or sales reports. Practise pivot tables in a live spreadsheet, then count by range with the COUNTIFS and SUMIFS lesson. Each lesson checks your work instantly. Then try the exercise on this page to count the 30-39 band with COUNTIFS.