How to group data in an Excel pivot table. Examples for grouping dates, number and text fields. How to troubleshoot grouping problems and avoid problems.
In a pivot table, you can group dates, number and text fields. For example, group order dates by year and month, or group test scores in bands of 10. You can manually select text items in a pivot table field, and group the selected items. This lets you quickly see subtotals for a specific set of items in your pivot table.
This video shows how to group dates by month and years, and how to group text items manually. There are written steps below the video.
In Excel 2016, and later versions, dates are automatically grouped, when you add a date field to a pivot table. You can undo the grouping in Excel 2016, and turn that feature off in later versions.
In Excel 2019 and Excel 365, you can change an Excel Option setting, to turn this date grouping feature on or off
NOTE: This is an application-level setting, and will affect all your Excel workbooks.
To turn the automatic date grouping feature off:
For all versions (Excel 2016 and later), you can manually ungroup the dates, with one of these methods:
In a pivot table, there are several built-in options for grouping dates - Seconds, Minutes, Hours, Days, Months, Quarters, and Years.
You can select one or more of those options, to create the type of date grouping that you need. The examples below show how to group dates:
-- By Month and Year
-- By Week
-- By 4-Week Period
To group the items in a Date field
To group the items in a Date field by week
Watch this video to see how to group the date field in 4-week periods, and set the starting date. There are written instructions below the video.
In this example, out company has 13 sales periods each year, with 4 weeks in each period. In the pivot table Grouping dialog box, here’s no option for grouping by Weeks. Instead, we can use the Days option .
With a bit of simple arithmetic, we can calculate the number of days per period:
Follow these steps to group the dates into 4 week periods:
As a starting date, Excel automatically selects the first date in your source data. In the the screen shot above, you can see that January 1, 2013 is the starting date in this source data.
If I check in Outlook, that date falls on a Tuesday.
If I click OK in the Grouping window, Excel will create periods that start on a Tuesday. In the grouped dates shown below, the first period goes from Tuesday, January 1, 2013, to Monday, January 28, 2013.
Instead of using the default starting date, you can enter a different date, to get the starting date you need.
In this example, we’ll change the starting date to December 31, 2012. That date falls on a Monday, so this forces all the groupings to start on a Monday, which matches the company’s sales periods.
To fix the starting date:
Now the periods are grouped correctly, with a Monday as the starting date for each period.
Watch this short video to see how to hide the text on the extra items that are created when you group a date field, or use a timeline in Excel 2013, to filter by a date period. There are written instructions below the video.
Download the sample file for the extra items in date grouping. The zipped file is in xlsx format, and does not contain macros.
When a date field is grouped, new items are added, for all the dates before the start date and after the end date. These items start with a "<" or a ">" symbol, such as "<1/3/2013" and ">12/30/2014", and are based on the first and last date in the source data.
There is no way to remove these extra items, but you could change the captions, to replace the text with space characters. Or, add new fields in the source data, to calculate the year and year/month, for each date, and use the new fields in the pivot table, instead of grouping.
In Excel 2013, you can use the Timeline filter, to quickly filter by year, quarter, month or day.
Download the sample file for the extra items in date grouping. The zipped file is in xlsx format, and does not contain macros.
Use grouping to create a concise summary, instead of showing a long list of individual numbers. This video shows you the steps, and the written instructions are below the video.
Instead of listing numbers individually in a Row field, you can group them, to create a concise summary of the data.
In this example, the original pivot table shows the units sold, per product. In column C, it shows the number of orders where that quantity was sold.
To summarize the orders, group the units by tens. So, instead of 3 and 9 appearing in separate rows, they'll be summarized in the grouping 1-10.
To group the numbers
In the pivot table, the Units now appear in groups -- 1 - 10, 11 - 20, etc.
To make a more concise layout, you could move the grouped units field to the Columns area, as headings.
The Grouping dialog box isn't available if you try to group items in a text field. Instead, you can manually group the items.
Watch this video to see the steps for creating groups from text items. Then, rename a group, or add more items. Later, you can ungroup one or more of the groups. There are written instructions below the video.
The Grouping dialog box isn't available if you try to group items in a text field. Instead, you can manually group the items.
This creates a new field in the field list, with the original field name and a number, such as City2
The new field is positioned above the original field in the pivot table layout, and the grouped items have a generic name, such as Group1.
After you manually group text items, you might want to ungroup some, or all, of the groups in a pivot field.
If you try to group a date or number field, you may see an error message that says, "Cannot group that selection." This might be caused by the Data Model, or cells that are blank, or previously grouped items. See the details below.
When you created the pivot table, if you checked the box to "Add this data to the Data Model", you won't be able to group the items. Instead of a normal pivot table, it is an OLAP-based Power Pivot, and the grouping feature is not available.
In a normal pivot table (not in the data model), the grouping problem usually occurs when the field contains records with
To fix the problem
If you don't have blank cells or text in the date column, there may be a grouped field left over from the previous time that you grouped the data.
If you create two pivot tables based on the same Excel Table in Excel 2007, when you change the grouping in one pivot table, the same grouping appears in the other pivot table.
Because you created the two pivot tables from the same source data, by default they use the same pivot cache, the grouped items are the same in both tables.
To use different grouping in each pivot table, you'll need to create a separate pivot cache for each pivot table.
Use the following method, suggested in the Excel newsgroups by Dave Peterson.
To create a separate pivot cache for the second pivot table:
Get the zipped sample file for this tutorial
Clear Old Items in Pivot Table
Last updated: July 8, 2021 7:25 PM