XL: How to Use Grouping for Fields in a PivotTable

ID: Q142153

The information in this article applies to:

Microsoft Excel 97 for Windows
Microsoft Excel for Windows 95, version 7.0
Microsoft Excel for Windows, versions 5.0, 5.0c
Microsoft Excel for Windows NT, version 5.0
Microsoft Excel for the Macintosh, versions 5.0, 5.0a

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 will be 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 wizard. To do this in Microsoft Excel 7.0, click

   PivotTable on the Data menu. In Microsoft Excel 97, click PivotTable
   Report on the Data menu.

3. In step 1 of the PivotTable Wizard, click Microsoft Excel

   List or Database, and click Next.

4. In step 2 of the PivotTable Wizard, type "A1:C10" (without the

   quotation marks), and click Next.

5. In step 3 of the PivotTable Wizard, 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 Next.

6. In step 4 of the PivotTable Wizard, select where to place the

   PivotTable. To do this in Microsoft Excel 7.0, type "E1" (without the
   quotation marks) in the PivotTable Starting Cell box, and click Finish.
   In Microsoft Excel 97, click Existing Worksheet; type "E1" (without
   the quotation marks), and click Finish.

7. Select cell F1. On the Data menu, point to Group And Outline,

   and then click Group.

8. In the By box, click to clear Months, and select 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 wizard. To do this in Microsoft Excel 97, on the

   Data menu, click PivotTable Report. In Microsoft Excel 7.0, on the
   Data menu, click PivotTable.

3. In step 1 of the PivotTable Wizard, select the Microsoft Excel

   List Or Database option, and then click Next.

4. In step 2 of the PivotTable Wizard, type "A1:B10" (without the

   quotation marks), and click Next.

5. In step 3 of the PivotTable Wizard, drag the Sales field to the

   Column area, and drag the Employee field to the Data area, and then
   click Next.

6. In step 4 of the PivotTable Wizard, select where to place the

   PivotTable. To do this in Microsoft Excel 7.0, type "E1"(without the
   quotation marks) in the PivotTable Starting Cell box, and click Finish.
   In Microsoft Excel 97, click Existing Worksheet; type "E1" (without the
   quotation marks), and click Finish.

7. Select cell F1. On the Data menu, point to Group And Outline, and then

   click Group.

8. In the Starting At box, type "0" (without the quotation marks). In the

   Ending At box, type "100" (without the quotation marks). In the By box,
   type "10" (without the quotation marks), and then click OK.

REFERENCES

Microsoft Excel 98 Macintosh Edition

For more information about Grouping items in a Pivot Table, click Contents and Index in Microsoft Excel 98 Macintosh Edition Help, click the Index tab and type the following text

   Grouping PivotTables

and then double-click the selected text to go to the "Group and ungroup data in a PivotTable" topic.

Microsoft Excel 97

For more information about Grouping items in a Pivot Table, click Contents and Index in Microsoft Excel 97 Help, click the Index tab and type the following text

   Grouping Data, in PivotTables

and then double-click the selected text to go to the "Group and ungroup data in a PivotTable" topic.

Microsoft Excel 7.0

For more information about Grouping items in a Pivot Table, click the Index tab in Microsoft Excel 7.0 Help, type the following text

   Grouping PivotTable Data

and then double-click the selected text to go to the "Group and ungroup data in a PivotTable" topic.

Microsoft Excel 5.0

"Microsoft Excel User's Guide," version 5.0, Chapter 25, "Grouping Items in a Pivot Table Field"

Additional query words: 5.00 5.00a 5.00c 7.00 8.00 97

Keywords          : kbualink97 kbdta xlwiz xlpivot 
Version           : WINDOWS:5.0,5.0c,7.0,97; MACINTOSH:5.0,5.0a
Platform          : MACINTOSH WINDOWS
Issue type        : kbhowto

Last Reviewed: January 7, 1999