How to Display Data as Percentage of Total in Pivot Table
This article was previously published under Q106359
This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.
In Microsoft Excel, you can display data in a PivotTable as apercentage of the data's total using the % Of Row and % Of Columncalculation types.
When you use a PivotTable, you can change the calculation type for adata field to display values of cells in the data area based on thevalues of other cells in the data area. For example, you can summarize"Sales" as a percentage of "Total Sales." For an example of how tosummarize 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 changethe calculation type for the data in a PivotTable that has alreadybeen 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 Aslist, you cannot display the data as a percent of the total. You mustuse % 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 pivottable data field, click the Index tab in Microsoft Excel 97 Help, type thefollowing text
and then double-click the selected text to go to the "Summarize andcalculate data in a PivotTable" topic.
Or, in versions of Microsoft Excel earlier than Excel 97, choose theSearch button in Help and type:
pivot tables, changing information in
XL97 pivot table XL
Article ID: 106359 - Last Review: 08/15/2003 15:13:50 - Revision: 1.1
Applies to Microsoft Excel 97 Standard Edition