Group items in a PivotTable report

In the row and column label areas of a PivotTable report, you can group the items in a field in a custom way. Grouping the data can help you to isolate a subset of data that satisfies your specific needs, and that cannot be easily grouped in other ways, such as sorting and filtering.

What do you want to do?


Learn about grouping items

To isolate a subset of items for more refined analysis of your data, you can group numeric, date, time, and even a selection of specific items. The following are common examples of grouping items:

Example of grouping by date items    

Creating a date group

Callout 1 Before grouping, the orders for each salesperson are listed by date.
Callout 2 After grouping, the orders are grouped and summarized by month for easier analysis and display.

Example of grouping by numeric items    

Creating a numeric group

Callout 1 Before grouping, the orders for each salesperson are listed by the order number.
Callout 2 After grouping, the orders are grouped and summarized by every 100 orders for easier analysis and display.

Example of grouping by selected items    

Creating a selection group

Callout 1 Before grouping, the salespersons in each country are listed.
Callout 2 After grouping, the salespersons are now grouped by the top two in sales and the bottom in sales in each country for easier analysis and display.

When you group items by selection, you create a new field based on the field whose items you are grouping. For example when you group the SalesPerson field by specific names, you create a new field SalesPerson1 that is added to the PivotTable Field List. You can work with this new field in many ways; for example, move it to different areas of the PivotTable report, rearrange it with other fields in an area, rename the field by using the Field Settings dialog box, and filter the field it is based on by using the new group name and values.

 Notes 

Top of Page Top of Page

Group numeric items

  1. Select the numeric field in the PivotTable report that you want to group.
  2. On the Options tab, in the Group group, click Group Field.
  3. In the Starting at box, enter the first item to group.
  4. In the Ending at box, enter the last item to group.
  5. In the By box, type a number that represents the interval included in each group.

Top of Page Top of Page

Group dates or times

  1. Select the date or time field in the PivotTable report that you want to group.
  2. On the Options tab, in the Group group, click Group Field.
  3. Enter the first date or time to group in the Starting at box, and enter the last date or time to group in the Ending at box.
  4. In the By box, click one or more time periods for the groups.

To group items by weeks, click Days in the By box, make sure that Days is the only time period selected, and then click 7 in the Number of days box. You can then click additional time periods to group by, such as Month.

Top of Page Top of Page

Group selected items

  1. Select two or more items in the PivotTable report that you want to group, either by clicking and dragging, or by holding down CTRL or SHIFT while you click.
  2. On the Options tab, in the Group group, click Group Selection.

 Tip   You might find it easier to either create another group for all the other items in the field or remove the field the group is based on so that you can create a more compact report.

 Note   For fields 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.

Top of Page Top of Page

Rename a default group name

When you group items, Excel 2007 provides a default group name such as Group1 for selected items, or Apr for dates in the month of April. To rename the group to something more meaningful, do the following:

  1. Select the group name.
  2. Press F2.
  3. Enter a new group name.

Top of Page Top of Page

Ungroup items

  1. Select the group of items that you want to ungroup.
  2. On the Options tab, in the Group group, click Ungroup.

For a numeric or date and time field, all groups for the field are ungrouped. For a group selection, only the selected items are ungrouped, and the group field is not removed from the PivotTable Field List until all groups for the field are removed.

Top of Page Top of Page

 
 
Applies to:
Excel 2007