Group or ungroup data in a PivotTable report

Grouping data in a PivotTable can help you show a subset of data you want to analyze. For example, you may want to group an unwieldy list of dates or times (date and time fields in the PivotTable) into quarters and months, like this:

Dates that are grouped by months and quarters

To group fields, do this:

  1. In the PivotTable, right-click any numeric or date and time field, and click Group.

  2. In the Starting at and Ending at box, enter this (as needed):

    Grouping dialog box

    • The smallest and largest number to group numeric fields.

    • The first and last date or time you want to group by.

      The entry in the Ending at box should be larger or later than the entry in the Starting at box.

  3. In the By box, do this:

    • For numeric fields, enter the number that represents the interval for each group.

    • For date or time fields, click one or more date or time periods for the groups.

      You can click additional time periods to group by. For example, you can group by Months and Weeks. Group items by weeks first, making sure Days is the only time period selected. In the Number of days box, click 7, and then click Months.

Tip:  Date and time groups are clearly labeled in the PivotTable; for example, as Apr, May, Jun for months. To change a group label, click it, press F2, and type the name you want.

For more information about grouping, see About grouping data in a PivotTable at the end of this article.

Group selected items

You can also select specific items and group them, like this:

Selected names that are grouped

  1. In the PivotTable, select two or more items you want to group together, holding down Ctrl or Shift while you click them.

  2. Right-click what you selected, and click Group.

When you group selected items, you create a new field based on the field you are grouping. For example when you group a field called SalesPerson, you create a new field called SalesPerson1. This field is added in the field section of the Field List, and you can use it like any other field. In the PivotTable, you’ll see a group label, like Group1 for the first group you create. To change a group label to something more meaningful, click it, press F2, and type the name you want.

Tip: For a more compact PivotTable, you might want to create groups for all the other ungrouped items in the field.

Note: For fields that are organized in levels, you can only group items that all have the same next-level item. For example, if the field has levels Country and City, you can't group cities from different countries.

Ungroup grouped data

To remove grouping, right-click any item in the grouped data, and click Ungroup.

If you ungroup numeric or date and time fields, all grouping for that field is removed. If you ungroup a group of selected items, only the selected items are ungrouped. The group field won’t be removed from the Field List until all groups for the field are ungrouped. For example, suppose you have four cities in the City field: Boston, New York, Los Angeles, and Seattle. You group them so that New York and Boston are in one group you name Atlantic, and Los Angeles and Seattle are in a group you name Pacific. A new field, City2, appears in the Fields area and is placed in the Rows area of the Fields List.

As shown here, the City2 field is based on the City field, and is placed in the Rows area to group the selected cities.

City2, based on the City field, is used in the Rows area for grouping

As shown below, the four cities are arranged under the new groups, Atlantic and Pacific.

Custom Atlantic and Pacific groups are based on selected cities

About grouping data in a PivotTable

When you group data in a PivotTable, be aware that:

  • You can’t group items for Online Analytical Processing (OLAP) source data that doesn't support the CREATE SESSION CUBE statement.

  • You can’t add a calculated item to an already grouped field. You first need to ungroup the items, add the calculated item, and then regroup the items.

  • You can’t use the Convert to Formulas command (PivotTable Tools > Analyze> OLAP Tools) when you have one or more grouped items. Ungroup the grouped items before using this command.

  • You can’t create slicers for OLAP hierarchies with grouped fields.

More about PivotTables

Share Facebook Facebook Twitter Twitter Email Email

Was this information helpful?

Great! Any other feedback?

How can we improve it?

Thank you for your feedback!