Article ID: 214057
Article Last Modified on 9/27/2003
APPLIES TO
- Microsoft Excel 2000 Standard Edition
This article was previously published under Q214057
For a Microsoft Excel 97 and earlier version of this article, see 106359.
IN THIS TASK
SUMMARY
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.
back to the top
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.
back to the top
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.
NOTE: 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.
back to the top
REFERENCES
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.
back to the top
Additional query words: pivot table XL2000
Keywords: kbhowto kbhowtomaster KB214057
