How to Group Pivot Table Data

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.

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.

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.

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.

Download the sample file for the extra items in date grouping. The zipped file is in xlsx format, and does not contain macros.

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.

Download the sample file for the extra items in date grouping. The zipped file is in xlsx format, and does not contain macros.

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.

   

Problems when Grouping Data

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.

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.

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.

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

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.

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.

Download the Sample File

Download the zipped sample file for this tutorial

Pivot Table Tools

To save time when building, formatting and modifying your pivot tables, use the tools in my Pivot Power Premium add-in. With just a few clicks, you can:

• copy the formatting from one pivot table, and apply it to another pivot table.
• change all the values from Count to Sum
• remove the "Sum of" from all the headings

and much more!

More Tutorials

Clear Old Items in Pivot Table

Calculated Field - Count

Calculated Items

Summary Functions