Microsoft KB Archive/106359

From BetaArchive Wiki
The printable version is no longer supported and may have rendering errors. Please update your browser bookmarks and please use the default browser print function instead.
Knowledge Base


How to Display Data as Percentage of Total in Pivot Table

Article ID: 106359

Article Last Modified on 8/15/2003



APPLIES TO

  • Microsoft Excel 97 Standard Edition



This article was previously published under Q106359

SUMMARY

In Microsoft Excel, you can display data in a PivotTable as a percentage of the data's total using the % Of Row and % Of Column calculation types.

MORE INFORMATION

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." For an example of how to summarize data as a percentage of the data's total, follow these steps:

  1. 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
  2. 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.
  3. In the Step 1 dialog box of the PivotTable Wizard, click "Microsoft Excel list or database" and then click Next.
  4. In the Step 2 dialog box, verify that $A$1:$C$10 is the Range, and then click Next.
  5. 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.
  6. Double-click the Sum of Sales field button in the Data area, to open the PivotTable Field dialog box.
  7. 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.)
  8. Click Finish.

The data is calculated as a percentage of the total data. To change the calculation type for the data in a PivotTable that has already been created, follow these steps:

  1. Select any one of the cells in the data area that contains the data you want to summarize.
  2. 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.
  3. 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 As list, you cannot display the data as a percent of the total. You must use % Of Row or % Of Column as described earlier.

REFERENCES

"User's Guide," version 5.0, pages 512-514

For more information about changing the calculation type for a pivot table data field, click the Index tab in Microsoft Excel 97 Help, type the following text

   pivottables, calculations
                



and then double-click the selected text to go to the "Summarize and calculate data in a PivotTable" topic.

Or, in versions of Microsoft Excel earlier than Excel 97, choose the Search button in Help and type:

pivot tables, changing information in



Additional query words: XL97 pivot table XL

Keywords: kbhowto KB106359