How to use grouping for fields in a PivotTable in Excel (214259)


The information in this article applies to:

  • Microsoft Excel 2000
  • Microsoft Excel 2002
This article was previously published under Q214259
For a Microsoft Excel 97 and earlier version of this article, see 142153.
For a Microsoft Excel 98 version of this article, see 193428.

SUMMARY

In Microsoft Excel, you can group a field that is a date, a time, or a number. With date and time pivot fields, data in the table can be grouped into specific periods. With numeric pivot fields, data in the table can be grouped into evenly incremented groups, such as by tens or hundreds.

MORE INFORMATION

Grouping with Dates/Times

The steps below demonstrate how you can use grouping for a PivotTable field that is a date. In this example, weekly (seven-day) sales totals are displayed for each employee.
  1. In a new worksheet, type the following data:
          A1: Date           B1: Employee     C1: Amount
      A2: 1/2/95         B2: 111          C2: 2
      A3: 1/5/95         B3: 333          C3: 3.89
      A4: 1/6/95         B4: 333          C4: 1.78
      A5: 1/9/95         B5: 444          C5: 7.65
      A6: 1/11/95        B6: 222          C6: 4.6
      A7: 1/13/95        B7: 111          C7: 1.3
      A8: 1/20/95        B8: 444          C8: 3.65
      A9: 1/21/95        B9: 333          C9: 3.98
      A10: 1/30/95       B10: 222         C10: 6.9
  2. Start the PivotTable and PivotChart wizard. To do this, click PivotTable and PivotChart Report on the Data menu.
  3. In step 1 of the PivotTable and PivotChart Wizard, click Microsoft Excel list or database, and make sure that PivotTable is selected. Click Next.
  4. In step 2 of the PivotTable and PivotChart Wizard, type A1:C10, and click Next.
  5. In step 3 of the PivotTable and PivotChart Wizard, click Layout.

    The PivotTable and PivotChart Wizard - Layout dialog box appears.
  6. Drag the Date field to the COLUMN area, the Employee field to the ROW area, and the Amount field to the DATA area, and then click OK.
  7. Select where to place the PivotTable. To do this, click Existing Worksheet; type E1, and click Finish.
  8. Select cell F1. On the Data menu, point to Group and Outline, and then click Group.

    The Grouping dialog box appears.
  9. In the By box, click to clear Months, and then click Days. Select 7 for the Number of days, and click OK.

Grouping with Numbers

The steps below demonstrate how you can use grouping for a PivotTable field that is a number. This example displays a count of the employees for sales between 0 and 100, in groups of 10.
  1. In a new worksheet, type the following data:
          A1: Employee     B1: Sales
      A2: Brown        B2: 81.45
      A3: Doe          B3: 99.66
      A4: Smith        B4: 89.88
      A5: Ward         B5: 86.96
      A6: Grady        B6: 78.37
      A7: Turner       B7: 24.16
      A8: Williams     B8: 79.17
      A9: Earnhardt    B9: 44.35
     A10: Ford        B10: 25.40
  2. Start the PivotTable and PivotChart Wizard. To do this, click PivotTable and PivotChart Report on the Data menu.
  3. In step 1 of the PivotTable and PivotChart Wizard, select the Microsoft Excel list or database option, and make sure PivotTable is selected. Click Next.
  4. In step 2 of the PivotTable and PivotChart Wizard, type A1:B10, and click Next.
  5. In step 3 of the PivotTable Wizard, click Layout. The PivotTable and PivotChart Wizard - Layout dialog box appears.
  6. Drag the Sales field to the Column area, and drag the Employee field to the Data area, and then click OK.
  7. Select where to place the PivotTable. To do this, click Existing Worksheet; type E1, and click Finish.
  8. Select cell F1. On the Data menu, point to Group and Outline, and then click Group.

    The Grouping dialog box appears.
  9. In the Starting at box, type 0. In the Ending at box, type 100. In the By box, type 10, and then click OK.

REFERENCES

For more information about grouping items in a Pivot Table, click Microsoft Excel Help on the Help menu, type Group or ungroup dates or times in a PivotTable or PivotChart field in the Office Assistant or the Answer Wizard, and then click Search to view the topic.
Modification Type:MinorLast Reviewed:10/4/2004
Keywords:kbPivotTable kbhowto kbualink97 KB214259
©2004 Microsoft Corporation. All rights reserved.