Home > Pivot > Layout > Grouping

How to Group Pivot Table Data

How to group data in an Excel pivot table. Examples for grouping dates, number and text fields. Fix grouping problems "Cannot group that selection"

Table of Contents

Video: Grouping Data

Automatic Date Grouping

Grouping Dates

Grouping by Date Adds Extra Items

Problems when Grouping Data

Grouping Numbers

Grouping Text Items

Ungrouping Text Items

Grouping in One Pivot Table Affects Another

Video: Grouping Data

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, and the sample file is available in the Download section.

Automatic Date Grouping

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.

Tip: See more details about pivot table date grouping, and learn why it can be helpful

Turn Off Automatic Date Grouping

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:

• On the Ribbon, click the File tab, then click Options
• At the left, click the Data category
• At the end of the Data options section, add a check mark to "Disable automatic grouping of Date/Time columns in PivotTables"
• Click OK to apply the new settings.

Undo the Date Grouping

For all versions (Excel 2016 and later), you can manually ungroup the dates, with one of these methods:

Keyboard Shortcut

• Add the Date field to the pivot table
• Immediately press Ctrl + Z, to undo the automatic date grouping
• The grouped fields are removed, and the original Date field remains in the pivot table

Ungroup Command

• Add the Date field to the pivot table
• At any time, right-click on any of the date field items or headings
• In the right-click menu, click Ungroup
• The grouped fields are removed, and the original Date field remains in the pivot table

Grouping Dates

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

Group Dates by Month and Year

To group the items in a Date field

1. Right-click on one of the dates in the pivot table.
2. In the popup menu, click Group

   

3. In the Grouping dialog box, select one or more options from the 'By' list.
4. To limit the dates that are grouped, you can set a Start and End date, by typing the dates in the 'Starting at' and 'Ending at' boxes
5. Click OK to close the dialog box.

   

Group Dates by Week

To group the items in a Date field by week

1. Right-click on one of the dates in the pivot table.
2. In the popup menu, click Group
3. In the Grouping dialog box, select Days from the 'By' list.
4. For 'Number of days', select 7
5. The week range is determined by the date in the 'Starting at' box, so adjust this if necessary. In the example below, January 1, 2012, was entered as the starting date.
6. Click OK

   

Group Dates by 4-Week Periods

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.

Group Dates by 4-Week Period

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:

• 4 weeks per period
• 7 days per week
• 7 x 4 = 28 Days per Period

Follow these steps to group the dates into 4 week periods:

• Right-click on of the dates, and click Group
• In the Grouping window, click on Days, to select it
• If any other grouping types are selected, click on them, to unselect them
• Set the number of days to 28
  • NOTE: To group dates into 1-week periods, use 7 as the number of days.

Using the Default Starting Date

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.

Fix the Starting Date

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:

• Right-click on one of the grouped date cells in the pivot table
• Click Group
• In the Grouping window, change the starting date – December 31, 2012
• NOTE: The Auto check box for “Starting at” is automatically cleared – leave it unchecked.
• Click OK

Now the periods are grouped correctly, with a Monday as the starting date for each period.

Grouping by Date Adds Extra Items

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.

Note: You can get the Excel file used in the video, in the Download section at the end of the page.

Grouping by Date Adds Extra Items

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.

Note: You can get the Excel file that shows the extra items problem, in the Download section at the end of the page.

Problem - Cannot Group That Selection

If you try to group a date or number field, you may see a Microsoft Excel error message, with a yellow warning label, that says, "Cannot group that selection."

There are 3 common causes for this pivot table grouping error message:

1. Pivot table was added to Data Model
2. Data source has some blank cells
3. Previously grouped items still in field list

See the details on these causes, and steps to fix them, below the screen shot.

1) Pivot Table Added Data Model

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.

Here are a couple of workarounds to fix this problem:

1. If you need to group the data, create another pivot table, based on the same data, but do NOT check the Add this data to the Data Model" option
2. Add a new column in the source data, and enter grouping categories in that column. Then, refresh the pivot table, and add the new field to the pivot table layout.

Blank Cells or Text

In a normal pivot table (not in the data model), the grouping problem usually occurs when the field contains records with

• a blank cell in a date/number field, or
• a text entry in a date/number field.

To fix the problem

• For blank cells, fill in the date/number (use a dummy date/number if necessary).
• If there is text in the date/number field, remove it.
• If numbers are being recognized as text, use one of the techniques to change text to real numbers. Then return to this pivot table tutorial, and try the grouping steps.

Previous Grouping

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.

To fix the problem, if this is the cause, follow these steps:

1. Check the pivot table field list, to see if there's a second copy of the date field, e.g. Date2.
2. If there is, add that field to the pivot table layout, in the row area, and ungroup it.
3. After that, you should be able to group the date field again.

Grouping Numbers

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.

Grouping Numbers

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

1. Right-click on one of the unit numbers in the pivot table.
2. In the popup menu, click Group
3. In the Grouping dialog box, enter 1 in the Starting At box
4. In this example, the highest number of units is 50, and you can type a higher number, if necessary.
5. Click OK, to apply the grouping

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.

Grouping Text Items

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.

Grouping Text Items

The Grouping dialog box isn't available if you try to group items in a text field. Instead, you can manually group the items.

1. Select the items that you want to group
2. Right-click on one of the selected items, and click Group

   

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.

To rename the group:

1. Click on the heading cells with the default name, e.g. Group1
2. Type a new name, such as Campaign A.

   

To add more items to a group:

1. Select all of the items in the group
2. Press the Ctrl key, and select the items that you want to add to the group
3. Right-click on one of the selected items, and click Group
4. NOTE: If the group was renamed, that name will be overwritten with a default name.

Ungrouping Text Items

After you manually group text items, you might want to ungroup some, or all, of the groups in a pivot field.

To ungroup a single group:

1. Right-click on an item in the group
2. Click Ungroup, and the group reverts to individual items.

   

To ungroup a single group:

1. Click at the top of a group heading, or click at the top of the pivot field, to select all of the items

   

2. Right-click on an item in the group
3. Click Ungroup, and all of the selected groups revert to individual items.

   

Grouping in One Pivot Table Affects Another

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.

This short video shows the problem, and how to fix it, and there are written steps below the video.

Create a New Pivot Cache

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:

1. Cut the second pivot table, and paste it into a new workbook.
2. Change the grouping of the second pivot table.
3. Cut the second pivot table from the new workbook, and paste it back into the original workbook.

Get the Sample Files

Pivot Table Grouping: Get the Food Sales sample file so you can try the Pivot Table grouping examples shown on this page. The zipped file is in xlsx format, and does not contain macros.

Extra Items: To follow along with the Extra Items video, download this sample file for the extra items in date grouping. The zipped file is in xlsx format, and does not contain macros.

More Tutorials

Old Items: If pivot table drop downs show outdated items, see the steps to clear old items in Pivot Table, and prevent them from reappearing.

Calculated Field Count: A pivot table calculated field always uses the SUM of other fields. See this workaround to use a count in Calculated Field formulas.

Calculated Item: Videos and written steps show how to create a calculated item in an Excel pivot table. See the features, restrictions, warnings.