How to Group or Ungroup Data in a PivotTable

Pivot Table Grouping is a feature that enables us to organize data into specific categories or ranges. It allows you to group data based on various factors, such as dates, numbers, or custom-defined intervals, making it easier to analyze and draw insights from the data. We will explore Pivot table grouping and how it can help you gain deeper insights and make informed decisions from your data.

What are Pivot Tables

A Pivot Table is a data processing tool used in spreadsheet software like Microsoft Excel, Google Sheets, or other data analysis tools. It allows the creation of a summary of large datasets by grouping, aggregating, and analyzing data dynamically. Pivot tables are mainly helpful when dealing with huge amounts of data, as they simplify the process of generating meaningful reports without the need for complex formulas or programming.

What is Grouping Data in the Pivot Table

Grouping data in Pivot Table refers to a powerful data organization feature in Excel, allowing users to effortlessly categorize information based on various criteria such as dates, numbers, and text fields. For instance, you can group order dates by year and month or create convenient score bands when working with test results, facilitating more in-depth performance analysis. The process of pivot table grouping is user-friendly and intuitive, involving the manual selection of specific text items within a pivot table field and then grouping these chosen items to promptly generate subtotals for a particular subset of data in your pivot table. This functionality proves invaluable when focusing on specific categories within your dataset, enhancing the precision and efficiency of data analysis.

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. 

Automatic Date Grouping in Excel

Automatic date grouping is a convenient and time-saving feature that is available in Excel 2016 and later versions when working with Pivot tables. When you add a date field to a Pivot table, Excel automatically groups the dates based on the selected time period, such as by year, month, or quarter. This makes it easier to analyze and summarize time-based data effectively.

You can turn off this feature in the latest versions of Excel. This is an application-level setting, and will affect all your Excel workbooks.

Note: In Excel 2019 and Excel 365, you have the ability to modify an Excel Option setting that allows you to enable or disable the date grouping feature.

How to 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.

How to Undo the Date Grouping

To undo the data grouping for all Excel 2016 and later, you need to manually ungroup the dates with the below method:

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.

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.

How to Group Dates in Pivot Tables in Excel By Month and Year

Step 1: Create a Pivot Table

Pivot Table

Step 2: Right-Click a value and select a group.

Select Group

Step 3: Select the Time Period Days and Months and Click Ok

Select time period

Step 4: Preview Pivor Data

The Pivot data table is grouped based on Month and Date.

How to Group Dates in Pivot Tables in Excel by Week

By following the procedures listed below, you 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

Group Dates by Week

How to Remove Extra Items in Grouping Dates

When you group a date field in an Excel Pivot table, you may notice that new items are added, for all the dates before the start date and after the end date. These extra items are denoted with “<” or”>” symbols, such as “<15/05/2023” and “>12/12/2023,” and are based on the first and last dates in the source data.

You can not directly remove these extra items from the Pivot table. However, there are alternative approaches to address this issue:

Customize Captions

Although you cannot remove the extra items, you can modify their captions to replace the text with space or more suitable descriptions. This way, you can minimize the visual impact of the extra items in your Pivot table report.

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 you to avoid the display of extra items while still gaining valuable insights from your data.

Note: In Excel 2013, you can use the Timeline Filter, to quickly filter by year, quarter, month, or day.

Grouping Numbers

Number Grouping in Pivot tables is a powerful feature that helps you to create a more concise and organized summary of numerical data. Instead of listing numbers individually in a row field, you 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

How 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: Preview the selected data will be vivble under Group 1

Step 5: Rename the Group 1

How To Rename the Group in Pivot Table

You can also rename the group according to you. Follow the below steps to rename the Group

Step 1: Click the Group Heading

Step 2: Edit and Type a new Name

How To Add More Items to a Group in Pivot Table

This is a useful feature when you 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

How to Ungroup Text Items in Pivot Table

After manually grouping the text items in a Pivot field, there might be instances where you need to ungroup certain data or all of them. Excel provides easy methods to accomplish both tasks efficiently.

How to Ungroup a Single Group

Step 1: Right-click on any item within the desired group.

Step 2: Click on “Ungroup”

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

FAQs

How do I delete unwanted data from a pivot table?

To remove undesired data from a pivot table, initiate the process by right-clicking on any cell within the pivot table. Next, navigate to the PivotTable options and access the Data tab within the PivotTable Options dialog box. Within the Retain Items section, opt for ‘None’ from the available dropdown list.

How do I remove items from a pivot table without values?

1. Click the arrow to the right of a row or column heading in the pivot table.
2. Click to uncheck the (blank) check box.
3. Click OK.

What is the shortcut for group and ungroup in Excel?

In Excel Shift + Alt + Right Arrow, you can quickly group rows in Excel, and the shortcut key Shift + Alt + Left Arrow can be used to ungroup rows.