How to Group in Pivot Table? ( 2 Easy Methods)
This guide on how to group in Pivot table is suitable for Excel 2016, 2019, 2021, and Excel for Microsoft 365. versions
Pivot Tables are the number 1 tool in Excel for analyzing and presenting large amounts of data. However, to do this effectively we need to ensure we organize data into manageable subsets. The grouping and ungrouping features of Pivot Tables allow us to do this with ease.
If you have been using Pivot Tables for a while, may already have noticed the auto-grouping features, particularly when working with time and date fields. You may not be as familiar with creating your own custom groups to break data up into manageable groups to make your Pivot Table easier to read and interpret.
In this guide, understand how automatic grouping works and create your own groups to categorize PivotTable data.
You’ll learn:
- How to Group Dates in Pivot Table?
- How to Group in Pivot Table Using Custom Groups?
- How to Ungroup Data in Pivot Table?
How to Group Dates in Pivot Table?
Automatic Grouping
Let’s first take a look at the automatic grouping that occurs when we use the date field in a Pivot Table. In this example, we will be using sales data to build a PivotTable. Note that we have one ‘Date’ field in the dataset. This data is also already in a table.
- From the Table Design tab, in the Tools group, click Summarize with PivotTable.
- Select New Worksheet and click OK.
- Drag and drop the ‘Product’ field into ‘Rows’.
- Drag and drop the ‘Date’ field into ‘Rows’ above ‘Product’.
Excel looks at the date in the dataset and establishes if it can break it down into smaller groups. We now have two additional fields: Quarter’s and Year’s in addition to the ‘Date’ field. We can use these fields independently.
NOTE: To immediately undo automatic grouping, press Ctrl+Z.
If we only want to see the data summarized by ‘Year’, we could remove the ‘Date’ and ‘Quarters’ fields and just leave ‘Year’.
Related:
4 Best Methods to Lock Cells in Excel
How to Add a Watermark in Excel? 2 Easy Methods
How to Make Excel Track Changes in a Workbook? 4 Easy Tips
Group Field
We can control how Excel automatically groups fields. It might be that we don’t want it to automatically split the dates into different subsets. We can control this using Group Field.
- Click in the PivotTable on a cell that contains a date.
- From the PivotTable Analyze tab, in the Group group, click Group.
- Select Group Field from the menu.
In the Grouping dialog box, we can see the start and end dates of our sales data and how the data will be grouped when we drag and drop the field to the PivotTable. By default, my date field is grouped by Month, Quarters, and Years.
We don’t have to use this default grouping. Simply de-select the highlighted groups to remove them.
- Click OK.
The Pivot Table now displays the data organized by ‘Month’ only.
Group Field – By Range
We can group fields by specific ranges. For example, maybe your PivotTable contains ages and you want to group them into 10-year buckets. Or maybe you have dates that you want to group into 28-day buckets. We can do this using Group Field.
- Click in the PivotTable on a cell that contains a date.
- From the PivotTable Analyze tab, in the Group group, click Group.
- Select Group Field from the menu.
- Select By Days.
- Use the scroll arrows and adjust the number of days to ‘28’.
- Click OK.
The Pivot data is now summarized by 28-day buckets.
Also Read:
How to Protect Cells in Excel Workbooks —5 Easy Ways
How to Indent in Excel? 3 Easy Methods
How to Add Subscript in Excel? (6 Best Methods)
Disabling Automatic Grouping
Automatic grouping can be disabled in Excel Options. Disabling grouping ensures that Excel will not automatically create groups based on the field selected.
- From the File tab, click Options.
- Go to the Data page.
- In the Data options section, place a tick next to the Disable automatic grouping of Date/Time columns in PivotTables.
How to Group in Pivot Table Using Custom Groups?
We can create our own custom groups to categorize data and make it easier to analyze and interpret.
In this example, we need to create groups to specify the range that the product belongs to.
- Select the items that belong to the first group.
- From the PivotTable Analyze tab, in the Group group, click Group.
- Select Group Selection from the menu.
The group will be given a default name of ‘Group 1’. We can rename this.
- Press the F2 key to edit the cell.
- Rename the group to ‘Premium’.
- Select the items that belong to the second group.
- From the PivotTable Analyze tab, in the Group group, click Group.
- Select Group Selection from the menu.
The group will be given a default name of ‘Group 2’. We can rename this.
- Press the F2 key to edit the cell.
- Rename the group to ‘Luxury’.
- Select the items that belong to the third group.
- From the PivotTable Analyze tab, in the Group group, click Group.
- Select Group Selection from the menu.
The group will be given a default name of ‘Group 3’. We can rename this.
- Press the F2 key to edit the cell.
- Rename the group to ‘Standard’.
Notice that the grouping will be applied to all corresponding items in the Pivot Table.
Renaming Custom Groups
Custom groups are effectively a field and can be used as such in the PivotTable. Custom groups are given a default name based on the field they relate to, in this case, ‘Product2’. As this grouping represents the range that the product belongs to, it would make more sense to rename this field so it’s easier to identify.
- Click the drop-down arrow next to the field.
- Select Field Settings from the menu.
- Name the field ‘Range’.
- Click OK.
We can use this field to summarize Pivot Table data by range. This makes grouping an extremely useful tool as we are now summarizing using a field that doesn’t exist in the original data source.
How to Ungroup Data in Pivot Table?
We can easily ungroup, group data by following these steps:
- Select the grouped data.
- From the PivotTable Analyze tab, in the Group group, click Group.
- Select Ungroup from the menu.
- Or, press the keyboard shortcut Shift+Alt+Left.
This process needs to be repeated for each group we created.
Suggested Reads:
How to Autofit Excel Cells? 3 Best Methods
How to Extract an Excel Substring? – 6 Best Methods
How to Shade Every Other Row in Excel? (5 Best Methods)
Let’s Wrap Up
In this guide, we looked at in detail how to group data in a pivot table using various methods. We hope you found this helpful. Please visit our free resources section to get more advanced Excel tips for free.
Want to learn more about Excel? Click here to access our advanced Excel courses with in-depth training modules. You can train your entire team in Excel and other business software, for a low one-time monthly fee here.
Deborah Ashby
Deborah Ashby is a TAP Accredited IT Trainer, specializing in the design, delivery, and facilitation of Microsoft courses both online and in the classroom. She has over 11 years of IT Training Experience and 24 years in the IT Industry. To date, she's trained over 10,000 people in the UK and overseas at companies such as HMRC, the Metropolitan Police, Parliament, SKY, Microsoft, Kew Gardens, Norton Rose Fulbright LLP. She's a qualified MOS Master for 2010, 2013, and 2016 editions of Microsoft Office and is COLF and TAP Accredited and a member of The British Learning Institute.
