HOW TO: Display Data as Percentage of the Total in a PivotTable in Excel 2000 (214057)

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:

1. Start Excel, and then open a new worksheet.
2. 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
   					

3. On the Data menu, click PivotTable and PivotChart Report.
4. In the step 1 of the wizard, click Microsoft Excel list or database, and then click Next.
5. In step 2 of the wizard, verify that $A$1:$C$10 is in the Range box, and then click Next.
6. Click Layout.
7. 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.
8. Double-click the Sum of Sales field button in the Data area.
   
   The PivotTable Field dialog box appears.
9. Click Options.
10. 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.)
11. 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:

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