Pivot tables have a built-in feature to group numbers into buckets at a given interval. In the example shown, a pivot table is used to group a list of 300 names into age brackets separated by 10 years. This numeric grouping is fully automatic.

Fields

The source data contains three fields: Name, Age, and Group. Only Name and Age are used in the pivot table as shown:

Pivot table group by age field list - 1

Age is used as a Row field. After Age has been added to the pivot table, it has been grouped as below:

Grouping configuration for age - 2

Starting and ending value are automatically entered based on the source data. The “by” is set to 10 years, but can be customized as needed.

The pivot table maintains age grouping when fields are added or reconfigured. For example, when the Group field is added as a Column field, the pivot table below is created:

With Group field added as a Column field - 3

Steps

  1. Create a pivot table
  2. Add Age as a Row field
  3. Add Name as a Value field
  4. Group Age into buckets of 10 years

Pivot tables have a built-in feature to allow manual grouping. In the example shown, a pivot table is used to group colors into two groups: Original and New. Notice these groups do not appear anywhere in the source data.

Fields

The source data contains three fields: Date , Sales , and Color . A fourth field, Group is created by the grouping process:

Pivot table group by custom - field list - 4

Before grouping, the original pivot table looks like this:

Pivot table group by custom - original pivot table - 5

Manual grouping is done by selecting the cells that make up a group. The control key must be held down to allow non-contiguous selections.

With cells selected, right-click and select Group from the menu:

Select cells, then right-click and select Group - 6

Repeat the process with the second group of items, Gold and Silver. The final two groups are named “Original” and “New”.

Excel will name the grouping field “Color2”. In the example, this field has been renamed “Group”:

<img loading=“lazy” src=“https://exceljet.net/sites/default/files/styles/original_with_watermark/public/images/pivot/inline/pivot%20table%20group%20by%20custom%20rename%20grouping%20field.png?itok=bF_uS2eV" onerror=“this.onerror=null;this.src=‘https://blogger.googleusercontent.com/img/a/AVvXsEhe7F7TRXHtjiKvHb5vS7DmnxvpHiDyoYyYvm1nHB3Qp2_w3BnM6A2eq4v7FYxCC9bfZt3a9vIMtAYEKUiaDQbHMg-ViyGmRIj39MLp0bGFfgfYw1Dc9q_H-T0wiTm3l0Uq42dETrN9eC8aGJ9_IORZsxST1AcLR7np1koOfcc7tnHa4S8Mwz_xD9d0=s16000';" alt=“Grouping field renamed from “Color2” to “Group” - 7”>

In addition, the grouping field is configured to insert a blank like after each new group:

Grouping field set to insert blank line - 8

Helper column alternative

As an alternative to manual grouping, you can add a helper column to the source data, and use a formula to assign groups . Once you have the grouping labels in the helper column, add the field directly to the pivot table as a row or column field .

Steps

  1. Create a pivot table
  2. Drag the Color field to the Rows area
  3. Drag the Sales field to the Values area
  4. Group items manually Select items Right-click and Group Name group as desired Repeat for each separate group
  5. Rename grouping field (Color2) to Group (or as desired)