Pivot Table Group By Custom

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:

Before grouping, the original pivot table looks like this:

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:

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” - 4”>

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

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)

To group a pivot table by day of week (e.g. Mon, Tue, Wed, etc.) you can add a helper column to the source data with a formula to extract the weekday name, then use the helper to group data in the pivot table. In the example shown, the pivot table is configured to display sales by weekday. Note that Excel automatically sorts standard weekday names in a natural order, instead of alphabetically.

Pivot Table Fields

In the pivot table shown, there are four fields in use: Date, Area, Sales, and Day. Three of these fields are used to create the pivot table shown: Area is a Row field, Day is a Column field, and Sales is a Value field, as seen below.

When the Sales field is first added as a Value field, it is automatically named “Sum of Sales”. In the example, it has been renamed “Sales “, with a trailing space to prevent Excel from complaining that the name is already in use.

<img loading=“lazy” src=“https://exceljet.net/sites/default/files/images/pivot/inline/pivot%20table%20group%20by%20day%20of%20week%20value%20field%20settings.png" onerror=“this.onerror=null;this.src=‘https://blogger.googleusercontent.com/img/a/AVvXsEhe7F7TRXHtjiKvHb5vS7DmnxvpHiDyoYyYvm1nHB3Qp2_w3BnM6A2eq4v7FYxCC9bfZt3a9vIMtAYEKUiaDQbHMg-ViyGmRIj39MLp0bGFfgfYw1Dc9q_H-T0wiTm3l0Uq42dETrN9eC8aGJ9_IORZsxST1AcLR7np1koOfcc7tnHa4S8Mwz_xD9d0=s16000';" alt=“Sales field renamed to “Sales " - 7”>

Helper Formula

The formula used in E5, copied down, is:

=TEXT(B5,"ddd")

This formula uses the TEXT function and a custom number format to display an abbreviated day of week.

Steps

1. Add helper column with the formula above as shown
2. Create a pivot table
3. Drag Day to the Columns area
4. Drag Area to the Rows area
5. Drag Sales to the Values area Rename to “Sales " (note trailing space)