Microsoft KB Archive/106359
Article ID: 106359
Article Last Modified on 8/15/2003
- Microsoft Excel 97 Standard Edition
This article was previously published under Q106359
In Microsoft Excel, you can display data in a PivotTable as a percentage of the data's total using the % Of Row and % Of Column calculation types.
When you use a PivotTable, you can change the calculation type for a data field to display values of cells in the data area based on the values of other cells in the data area. For example, you can summarize "Sales" as a percentage of "Total Sales." For an example of how to summarize data as a percentage of the data's total, follow these steps:
Open a new worksheet and type the following data:
A1: Date B1: Employee C1: Sales A2: 1/3/97 B2: 111 C2: 1000 A3: 1/3/97 B3: 333 C3: 1500 A4: 1/10/97 B4: 111 C4: 2000 A5: 1/10/97 B5: 222 C5: 2200 A6: 1/10/97 B6: 333 C6: 2500 A7: 1/17/97 B7: 111 C7: 2500 A8: 1/17/97 B8: 222 C8: 3000 A9: 1/17/97 B9: 333 C9: 3500 A10: 1/24/97 B10: 222 C10: 1500
- In versions of Microsoft Excel earlier than Excel 97, click PivotTable on the Data menu. Or, in Excel 97, click PivotTable Report on the Data menu.
- In the Step 1 dialog box of the PivotTable Wizard, click "Microsoft Excel list or database" and then click Next.
- In the Step 2 dialog box, verify that $A$1:$C$10 is the Range, and then click Next.
- Drag the Date field button to the Row area, drag the Employee field button to the Column area, and drag the Sales field button to the Data area.
- Double-click the Sum of Sales field button in the Data area, to open the PivotTable Field dialog box.
- Click Options and in the Show Data As list, click % Of Row, and then click OK. (If your data headings were in columns instead of rows, you would click % Of Column.)
- Click Finish.
The data is calculated as a percentage of the total data. To change the calculation type for the data in a PivotTable that has already been created, follow these steps:
- Select any one of the cells in the data area that contains the data you want to summarize.
- In versions of Microsoft Excel earlier than Excel 97, click PivotTable Field on the Data menu. Or, in Excel 97, right-click the field and then click Field on the shortcut menu.
- Click Options, and in the Show Data As list, click to select the calculation type you want.
NOTE: When you use the % Of calculation type in the Show Data As list, you cannot display the data as a percent of the total. You must use % Of Row or % Of Column as described earlier.
"User's Guide," version 5.0, pages 512-514
For more information about changing the calculation type for a pivot table data field, click the Index tab in Microsoft Excel 97 Help, type the following text
and then double-click the selected text to go to the "Summarize and calculate data in a PivotTable" topic.
Or, in versions of Microsoft Excel earlier than Excel 97, choose the Search button in Help and type:
pivot tables, changing information in
Additional query words: XL97 pivot table XL
Keywords: kbhowto KB106359