Group PivotTable Data

In professional reporting, raw data is often too granular (e.g., thousands of individual transaction dates or unique price points). Grouping allows you to "bucket" this data into logical categories—like Months, Quarters, or Price Ranges—without modifying your original source table.

Part 1: Date and Time Grouping

This is the most common grouping task. Excel can automatically nest time periods to show trends over years or months.

1. Right-click any Date value in the PivotTable Row or Column labels.
2. Select Group...
3. In the dialog box, select one or more intervals (e.g., Months and Years).
4. Click OK.

Part 2: Numerical Grouping (Binning)

When dealing with currency or quantities, you can create "bins" to see distribution (e.g., how many sales fell between $0-$500 vs. $500-$1000).

1. Right-click a Numeric value in the Row labels.
2. Select Group...
3. Set the parameters:
   • Starting at: The lowest value to include.
   • Ending at: The highest value to include.
   • By: The size of the increment (e.g., 100 to group by hundreds).

Part 3: Manual (Text) Grouping

If you need to group items that don't have a numerical or chronological relationship (e.g., grouping "France" and "Germany" into a custom group called "Europe"):

1. Hold Ctrl and click to select the specific items in the PivotTable.
2. Right-click one of the selected items and select Group.
3. Excel creates a new field (e.g., "Country2").
4. Click the "Group 1" label and type a new name (e.g., "Europe") directly into the cell to rename it.

Part 4: Ungrouping and Troubleshooting

• To Ungroup: Right-click the grouped cell and select Ungroup.
• "Cannot group that selection" Error: This usually happens if your source data contains blank cells or text in a column that is supposed to be all Dates or all Numbers. You must clean the source data first.

Technical Checklist for the Exam

Official Resources:

• Microsoft Support - Group or ungroup data in a PivotTable