The information in this article applies to:
- Microsoft Excel for Windows, version 5.0
- Microsoft Excel for the Macintosh, version 5.0
- Microsoft Excel for Windows 95, version 7.0
- Microsoft Excel 97 for Windows
SUMMARY
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.
MORE INFORMATION
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.
REFERENCES
"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
pivottables, calculations
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
|