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

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 .



Dave Bruns

Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.   

Pivot table group by custom - 48Pivot table group by custom - 44Pivot table group by custom - 54Pivot table group by custom - 51