Microsoft KB Archive/214057

= HOW TO: Display Data as Percentage of the Total in a PivotTable in Excel 2000 =

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
 * Display Data as Percentage in New PivotTable
 * Display Data as Percentage in Existing PivotTable

REFERENCES



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.</li> 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.</li> Double-click the Sum of Sales field button in the Data area.

The PivotTable Field dialog box appears.</li> Click Options.</li> 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.)</li> In step 3 of the wizard, click Finish.</li></ol>

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:
 * 1) In the PivotTable, right-click any one of the cells in the data area that contains the data that you want to summarize.
 * 2) Click Field Settings on the shortcut menu.
 * 3) Click Options.
 * 4) 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

<div class="references_section">