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, and time fields, 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 in ranges of 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 

Watch a video

To see how grouping can be useful in a PivotTable report, see Video: Group items in a PivotTable report.

Top of Page Top of Page

Group numbers in numeric fields

  1. In the PivotTable report, do one of the following:
  • Click any number in the numeric field that you want to group, and then on the Options tab, in the Group group, click Group Field.

Excel Ribbon Image

  • Right-click any number in the numeric field that you want to group, and then click Group.
  1. In the Starting at box, enter the smallest number to group by.

This clears the Starting at check box. To quickly return to the default start number, select the check box.

  1. In the Ending at box, enter the highest number to group by.

This clears the Ending at check box. To quickly return to the default end number, select the check box.

 Note    The end number must be greater than the start number.

  1. In the By box, enter the number that represents the size of the interval included in each group.

Top of Page Top of Page

Group dates or times

  1. In the PivotTable report, do one of the following:
  • Click any date or time in the date or time field, and then on the Options tab, in the Group group, click Group Field.

Excel Ribbon Image

  • Right-click any number in the numeric field that you want to group, and then click Group.
  1. In the Starting at box, enter the first date or time to group by.

This clears the Starting at check box. Selecting the check box does not return to the default date or time.

  1. In the Ending at box, enter the last date or time to group by.

This clears the Ending at check box. Selecting the check box does not return to the default date or time.

 Note    The end date or time must be later than the start date or time.

  1. In the By box, click one or more date or time periods for the groups. Group by Months is selected by default, but you can click it to deselect it.

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

Date and time groups are labeled in the PivotTable report, such as Apr for dates in the month of April. To change the group label, click the label, press F2, and then type the name that you want.

Top of Page Top of Page

Group selected items

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

Excel Ribbon Image

Groups that are based on a selection are added to the Field List. 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. In the PivotTable report, they display group labels, such as Group1 for the first group you create. To change a group label to something more meaningful, click the label, press F2, and then type the name that you want.

 Tip   For a more compact PivotTable report, you might want to create another group 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.

Top of Page Top of Page

Ungroup grouped items

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

Excel Ribbon Image

For a numeric or date and time field, all groups for the field are ungrouped. For a group of selected items, 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 2010