This step-by-step article shows you how to display data as percentage of the total in a PivotTable in Microsoft Excel 2000.
In Excel 2000, you can use the using the % of row
and % of column
calculation types to display data in a PivotTable as a percentage of the data's total.
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.
Display Data as Percentage in New PivotTable
To summarize data as a percentage of the data's total, follow these steps:
- Start Excel, and then open a new worksheet.
- Create the following worksheet:
A1: Date B1: Employee C1: Sales
A2: 1/3/00 B2: 111 C2: 1000
A3: 1/3/00 B3: 333 C3: 1500
A4: 1/10/00 B4: 111 C4: 2000
A5: 1/10/00 B5: 222 C5: 2200
A6: 1/10/00 B6: 333 C6: 2500
A7: 1/17/00 B7: 111 C7: 2500
A8: 1/17/00 B8: 222 C8: 3000
A9: 1/17/00 B9: 333 C9: 3500
A10: 1/24/00 B10: 222 C10: 1500
- On the Data menu, click PivotTable and PivotChart Report.
- In the step 1 of the wizard, click Microsoft Excel list or database, and then click Next.
- In step 2 of the wizard, verify that $A$1:$C$10 is in the Range box, and then click Next.
- Click Layout.
- Drag the Date field button to the Row area, drag the Employee field button to the Column area, and then drag the Sales field button to the Data area.
- Double-click the Sum of Sales field button in the Data area.
The PivotTable Field dialog box appears.
- Click Options.
- In the Show data as list, click % of row, and then click OK twice. (If your data headings are in columns instead of rows, click % of column.)
- In step 3 of the wizard, click Finish.
The data is calculated as a percentage of the total data.
Display Data as Percentage in Existing PivotTable
To change the calculation type for the data in a PivotTable that has already been created, follow these steps:
- In the PivotTable, right-click any one of the cells in the data area that contains the data that you want to summarize.
- Click Field Settings on the shortcut menu.
- Click Options.
- In the Show data as box, click the calculation type that you want, and then click OK.
: If you use the % of
calculation type in the Show data as
box, you cannot display the data as a percent of the total. You must use % of row
or % of column
For more information about how to use PivotTables, click Microsoft Excel Help
on the Help
menu, type about pivottable reports: interactive data analysis
in the Office Assistant or the Answer Wizard, and then click Search
to view the topic.
Article ID: 214057 - Last Review: September 27, 2003 - Revision: 3.1
- Microsoft Excel 2000 Standard Edition
|kbhowto kbhowtomaster KB214057|