Show All

Group items in a PivotTable or PivotChart field

1. If the field is a page field, check the page field settings, and then move it temporarily to the row or column area.

   How?

   For a PivotChart report, work in the associated PivotTable report.

   1. Double-click the page field.
   2. Click Advanced.
   3. Make sure the Retrieve external data for all page field items option is either selected or unavailable.
   4. Click OK twice.
   5. Drag the page field to the row or column area.

2. Do one of the following:

   Group numeric items

   1. Right-click the field with the numeric items, point to Group and Show Details on the shortcut menu, and then click Group.
   2. In the Starting at box, enter the first item to group.
   3. In the Ending at box, enter the last item to group.
   4. In the By box, type the number of items that you want in each group.

   Group dates or times

   1. Right-click the field with the dates or times, point to Group and Show Details on the shortcut menu, and then click Group.
   2. 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.
   3. 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 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, if you want.

   Group selected items

   1. Select the items to group, either by clicking and dragging, or by holding down CTRL or SHIFT while you click.

      For a PivotChart report, select the items in the associated PivotTable report.

   2. Right-click the selected items, point to Group and Show Details on the shortcut menu, and then click Group.

   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.

   Ungroup items

   • Right-click the group, point to Group and Show Details on the shortcut menu, and then click Ungroup.

     In a numeric or date/time field, right-click any group; Excel then ungroups all groups for the field.

3. If the field was formerly a page field, drag it back to the page area.

Notes

• Grouping numeric items, dates, and times is unavailable for some types of source data.

• When you group or ungroup items in a PivotChart report or its associated PivotTable report, some chart formatting may be lost.