Group or ungroup data in a PivotTable

Grouping data in a PivotTable can assist you with displaying a subset of data to analyse. For instance, you might want to group a monumental list of dates or times (date and time fields in the PivotTable) into quarters and months, similar to this image.

Note: The time grouping feature is new in Excel 2016. With time grouping, relationships across time-related fields are instantly identified and grouped together once you insert rows of time fields to your PivotTables. After being grouped together, you can drag the group to your Pivot Table and commence your analysis.

Dates that are grouped by months and quarters

Group data

  1. In the PivotTable, right-click a value and press Group.
  1. In the Grouping box, click Starting at and Ending at checkboxes, and change the values, if required.
  1. Below By, choose a time period. For numerical fields, type a number that elaborates the interval for every group.
  1. Press OK.

Group selected items

  1. Hold Ctrl and pick two or greater values.
  1. Right-click and choose Group.

Name a group

  1. Press the group.
  1. Click Analyse > Field Settings.
  1. Edit the Custom Name to anything you want and press OK.

Ungroup grouped data

  1. Right-click any item that is in the group.
  1. Click Ungroup.

Need more help?

You can always ask an expert in the Excel Tech Community, get support in the Answers community, or suggest a new feature or improvement on Excel User Voice.

See Also

Create a PivotTable to analyse worksheet data

Leave a Reply

%d bloggers like this: