Excel PivotTable Group

Group Data in a PivotTable

Grouping combines many small items into a few bigger ones.

For example, you can group days into years and quarters, or order sizes into ranges like 1-5 and 6-10.

Start with a PivotTable that has Sum of Sales in Values, formatted with a thousands separator, and nothing in Rows or Columns. See Create a PivotTable and PivotTable Values.

Group Dates

The data has orders on 731 different days. One row per day would be far too long to read.

Let's group the dates into years and quarters:

1. Drag Date to the Rows area
2. Right-click any date in the PivotTable and choose Group. You can also click PivotTable Analyze > Group Field
3. The Grouping dialog opens. Under By, click Quarters and Years so that both are selected, and click any other selected item to turn it off
4. Click OK

Each year shows its total, with the four quarters below it.

Sales grew from 2024 to 2025, and the third quarter (Qtr3) was the best quarter in both years.

Click the minus button next to a year to collapse it, and the plus button to expand it again.

Months

Select Months in the Grouping dialog as well, and each quarter gets its months below it: Jan, Feb, Mar and so on.

For example, December 2025 was the best month of 2025, with 634,000 in sales.

Group Numbers

You can also group numbers into ranges. Let's see how big the orders are.

1. Remove Date from the Rows area
2. Drag Quantity to the Rows area. Drag it, because checking the box would put it in Values
3. Drag OrderID to the Values area and change it to Count (see PivotTable Values)

The PivotTable now has one row for each quantity, from 1 to 30. Group them in steps of 5:

4. Right-click any quantity in the PivotTable and choose Group
5. Set Starting at to 1, Ending at to 30 and By to 5
6. Click OK

Most orders are small: 1,041 of the 2,740 orders had 5 items or fewer.

But the orders with 6 to 10 items brought in the most sales.

Group Selected Items

You can also make your own groups by picking items.

Say you want to compare Kanto with the two other regions together. Remove Quantity and Count of OrderID, and drag Region to Rows:

1. Click Hoenn (A4)
2. Hold down Ctrl and click Johto (A5)
3. Click PivotTable Analyze > Group Selection. You can also right-click and choose Group

Excel adds a new field, Region2, to the Rows area.

Hoenn and Johto are now in a group called Group1. Kanto was not selected, so it gets a group of its own, also called Kanto.

Give the group a better name: click A4, type Hoenn and Johto, and press Enter.

Then right-click the group and choose Expand/Collapse > Collapse Entire Field to hide the regions inside the groups:

Ungroup

To remove a group, click it and choose PivotTable Analyze > Ungroup. You can also right-click it and choose Ungroup.

For grouped dates or numbers, right-click any of the groups and choose Ungroup. This removes the grouping from the whole field.