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



The information in this article applies to:

  • Microsoft Excel 2000

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:
  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

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

Modification Type:MajorLast Reviewed:9/27/2003
Keywords:kbhowto kbHOWTOmaster KB214057