Grouping in Excel PivotTables helps organize raw data into meaningful categories, making analysis quicker and more insightful. You can group dates, numbers, or text into logical ranges such as months, value intervals, or custom categories to simplify large datasets and focus on key trends.
Types Of Grouping Pivot Table Data in Excel
In the Pivot data table, data can be grouped based on Dates, Numbers, and Text values.
- Grouping by Date: We can group dates by months or years, months by years, etc.
- Grouping by Numbers: We can group numbers in a particular range band together.
- Grouping by Text: For text values, we need to group manually, otherwise, it will throw an error.
Grouping and Ungrouping Dates
Automatic Date Grouping
In Excel 2016 and later (including Office 365), adding a date field to a PivotTable automatically groups it by year, quarter, or month. This simplifies time-based analysis but can be disabled.
Turn Off Automatic Date Grouping
Follow the below steps to turn off the automatic date grouping feature:
Step 1: Click on the File Tab in the Ribbon, and then click on Options.
Step 2: Click the Data category displayed at the left.
Step 3: At the end of the Data options sections, add a checkmark to Disable Automatic grouping of date/time columns in Pivot tables.
Step 4: Click OK to apply the new settings.
Ungroup Automatic Date Grouping
To ungroup the data grouping for all Excel 2016 and later, we need to manually ungroup the dates with the below method:
1. Keyboard Shortcut
Follow the given steps to undo the date grouping through the Keyboard shortcut.
Step 1: Add the Date field to the Pivot table.
Step 2: Then press Ctrl+ Z, to undo the automatic date grouping.
Step 3: The grouped fields are removed, and the original Date field remains in the Pivot table.
2. Ungroup Command
Follow the below steps to Undo the Date Grouping by Ungroup Command
Step 1: Add the Date field to the Pivot Table.
Step 2: At any time, right-click on any of the date field items or headings.
Step 3: In the right-click menu, click Ungroup
Step 4: The grouped Fields are removed, and the original date field remains in the Pivot table.
Group Dates by Month and Year
Step 1: Create a Pivot Table
Step 2: Right-Click a value and select a group.
Step 3: Select the Time Period Days and Months and Click Ok
Step 4: Preview Pivot Data
The data is grouped by month and year in the PivotTable.
Group Dates by Week
By following the procedures listed below, we can arrange the entries in a Date field by week
Step 1: Use the Right-click on Dates in Pivot Table
Step 2: Select Group from the Pop-Up Menu
Step 3: Select Days from the 'By' list
Step 4: Select 7 in Number of Days
Step 5: Adjust week Range if Required
Step 6: Click OK
Manage Extra Date Items
When grouping dates in a PivotTable, extra items may appear before and after the data range, shown with “<” or “>” symbols. These are based on the minimum and maximum dates in the source data.
We cannot remove these items directly, but they can be managed using alternative methods.
1. Customize Captions
Although we cannot remove the extra items, we can modify their captions to replace the text with space or more suitable descriptions. This way, we can minimize the visual impact of the extra items in our Pivot table report.
2. Utilize Additional Fields
An effective workaround is to create new fields in the source data to calculate the year and year/month for each date. This approach can help we to avoid the display of extra items while still gaining valuable insights from our data.
Note: In Excel 2013, we can use the Timeline Filter, to quickly filter by year, quarter, month, or day.
Grouping Numbers
Number Grouping in Pivot tables is a poourful feature that helps we to create a more concise and organized summary of numerical data. Instead of listing numbers individually in a row field, we have the option to group them together based on specific intervals or ranges, making it easier to analyze the data.
Follow the below steps, to group data by numbers:
Step 1: Create a Pivot Table
Step 2: Right-click on a cell and select the group
Step 3: In the dialogue box, under by select the interval of groups
Step 4: Click OK, pivot table data is grouped in intervals of 5
Steps to Group Text Items in Pivot Table
It is not possible to group text data using the above method in Excel. It will throw an error if we try to do so. So, we have to select data manually that we want to group, and then group it.
Step 1: Create a Pivot Table.
Step 2: Select the Data Manually
Step 3: Right Click on Data and Select Group
Step 4: The selected data will now appear under Group 1 in the Pivot Table.
Step 5: Rename the Group 1
Steps To Rename the Group in Pivot Table
Follow the below steps to rename the Group
Step 1: Click the Group Heading.
Step 2: Edit and Type a new Name.
Steps To Add More Items to a Group in Pivot Table
This is a useful feature when we need to add some data after grouping. Follow the below steps:
Step 1: Select All Data in the Group.
Step 2: Press Ctrl Key and Select data to be added in the Group.
Step 3: Right-click on the selected data and Select Group.
Steps to Ungroup Text Items in Pivot Table
After manually grouping the text items in a Pivot field, there might be instances where we need to ungroup certain data or all of them. Excel provides easy methods to accomplish both tasks efficiently.
Steps to Ungroup a Single Group
Step 1: Right-click on any item within the desired group.
Step 2: Click on Ungroup.
Steps to Ungroup All Groups in Pivot Table
Step 1: Click the Group Heading.
Step 2: Right-click on any items within the Selected groups.
Step 3: Click on Ungroup.