Microsoft KB Archive/142153

= XL: How to Use Grouping for Fields in a PivotTable =

Article ID: 142153

Article Last Modified on 8/17/2005

-

APPLIES TO


 * Microsoft Excel 97 Standard Edition
 * Microsoft Excel 95 Standard Edition
 * Microsoft Excel 5.0 Standard Edition
 * Microsoft Excel 5.0c
 * Microsoft Excel 5.0 Standard Edition
 * Microsoft Excel 5.0 for Macintosh
 * Microsoft Excel 5.0a for Macintosh

-



This article was previously published under Q142153





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.



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.   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  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. In step 1 of the PivotTable Wizard, click Microsoft Excel List or Database, and click Next. In step 2 of the PivotTable Wizard, type A1:C10, and click Next. 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. In step 4 of the PivotTable Wizard, select where to place the PivotTable. To do this in Microsoft Excel 7.0, type E1 in the PivotTable Starting Cell box, and click Finish. In Microsoft Excel 97, click Existing Worksheet; type E1, and click Finish.</li> Select cell F1. On the Data menu, point to Group And Outline, and then click Group.</li> In the By box, click to clear Months, and select Days. Select 7 for the Number Of Days, and click OK.</li></ol>

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. <ol>  In a new worksheet, type the following data: <pre class="fixed_text">     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 </li> 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.</li> In step 1 of the PivotTable Wizard, select the Microsoft Excel List Or Database option, and then click Next.</li> In step 2 of the PivotTable Wizard, type A1:B10, and click Next.</li> 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.</li> In step 4 of the PivotTable Wizard, select where to place the PivotTable. To do this in Microsoft Excel 7.0, type E1 in the PivotTable Starting Cell box, and click Finish. In Microsoft Excel 97, click Existing Worksheet; type E1, and click Finish.</li> Select cell F1. On the Data menu, point to Group And Outline, and then click Group.</li> In the Starting At box, type 0. In the Ending At box, type 100. In the By box, type 10, and then click OK.</li></ol>

<div class="references_section">

Microsoft Excel 98 Macintosh Edition
For more information about grouping items in a Pivot Table, follow these steps: <ol> On the Help menu, click Contents and Index. If you are using a version of the Macintosh operating system that is earlier than 8.0, click Contents and Index on the Balloon Help menu.</li> In Excel 98 Macintosh Edition Help, click Index, and then type the following text:

Grouping PivotTables

</li> Click Show Topics. Click the Group and ungroupdata in a PivotTable topic, and then click Go To.</li></ol>

If you cannot find the information that you want, ask the Office Assistant.

Microsoft Excel 97
For more information about grouping items in a Pivot Table, click Contents and Index on the Help menu, click the Index tab in Excel 97 Help, 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. If you are unable to find the information you need, ask the Office Assistant.

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.00a 5.00c 8.00

Keywords: kbhowto kbualink97 KB142153

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.