You are currently offline, waiting for your internet to reconnect

HOW TO: Display Data as Percentage of the Total in a PivotTable in 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 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 summarizeSales 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
pivot table XL2000
Properties

Article ID: 214057 - Last Review: 09/27/2003 14:08:22 - Revision: 3.1

  • Microsoft Excel 2000 Standard Edition
  • kbhowto kbhowtomaster KB214057
Feedback