How to Index Data in a PivotTable

Article translations Article translations
Article ID: 151515 - View products that this article applies to.
This article was previously published under Q151515
Expand all | Collapse all

On This Page

SUMMARY

The INDEX function in a PivotTable calculates a weighted average by using the following formula:
   (value in cell) x (Grand Total)) / ((Grand Row Total) x
      (Grand Column Total))
				
When you use PivotTables to compare the relative importance of your row data versus your column data, it can be extremely helpful to display your data as an Index. For example, this article demonstrates that if you display data in the Index format instead of a "Percentage of Total" format, you can often reveal valuable comparisons that you might not otherwise see.

The first section of this article describes how to create two PivotTables: one that displays sales figures as a Percentage of Total and another that displays sales figures as an Index. The second section of this article ("Analysis of the Results"), explains the advantages of being able to compare data displayed both ways.

MORE INFORMATION

To create the sample worksheet and PivotTables, follow these steps:

  1. Create a new worksheet, and enter the following values:
           A1: Fruit       B1: State   C1: Sales
           A2: Bananas     B2: CA      C2: $800,107
           A3: Pears       B3: TX      C3: $547,236
           A4: Kiwi        B4: CA      C4: $669,076
           A5: Apples      B5: CA      C5: $622,236
           A6: Cherries    B6: CA      C6: $656,097
           A7: Pears       B7: CA      C7: $674,218
           A8: Cherries    B8: PA      C8: $697,711
           A9: Kiwi        B9: CA      C9: $550,637
          A10: Bananas    B10: PA     C10: $602,124
          A11: Apples     B11: PA     C11: $669,916
          A12: Kiwi       B12: PA     C12: $693,306
          A13: Apples     B13: TX     C13: $589,278
          A14: Pears      B14: PA     C14: $739,241
          A15: Cherries   B15: TX     C15: $682,213
          A16: Bananas    B16: TX     C16: $791,944
  2. On the View menu, click Toolbars. In Microsoft Excel 97 or Microsoft Excel 98 Macintosh Edition, click PivotTable. In Microsoft Excel 5.0 or 7.0, click to select the Query And Pivot box, and then click OK.

    This step causes the Query and Pivot or PivotTable toolbar to appear.
  3. To create the % of Total PivotTable, select any cell within the data in step 1 and click the PivotTable button (the first button) on the Query And Pivot Toolbar.
  4. In the PivotTable Wizard, select "Microsoft Excel List or Database", and then click Next.
  5. Under Range, confirm the range to be $A$1:$C$16, and then click Next.
  6. Drag the button labeled Fruit to the box labeled ROW. Drag the button labeled State to the box labeled COLUMN, and drag the button labeled Sales to the box labeled DATA.
  7. In the Data Field, double-click Sum Of Sales.
  8. In the Pivot Table Field dialog box, click Options. Under Show Data As, click "% of Total," and then click OK.
  9. Click Next.
  10. In Microsoft Excel 97 or Microsoft Excel 98 Macintosh Edition, click the Existing worksheet button, click in worksheet cell E1, and then click Finish.

    In Excel 5.0 or 7.0, with the pointer in PivotTable Starting Cell, click in worksheet cell E1, and then click Finish.

    This step creates the first PivotTable in cells E1 to I8.
  11. To create the Index PivotTable, repeat steps 3 through 7.
  12. Click Options and click Index under Show Data As. Click OK in the Pivot Table Field dialog box.
  13. Click Next.
  14. In Microsoft Excel 97 or Microsoft Excel 98 Macintosh Edition, click Existing Worksheet, click in worksheet cell E10, and then click Finish.

    In Microsoft Excel 5.0 or 7.0, with the pointer in PivotTable Starting Cell, click in worksheet cell E10, and then click Finish.

    This step creates the second PivotTable in cells E10 to I17.

Analysis of the Results

Example 1:
If you compare the sales of bananas across the states, the Percentage of Total figures show that slightly more bananas were sold in California (8.01% in cell F4) than were sold in Texas (7.93% in cell H4).

However, if you look at the Index data, you can see that bananas are much more important to the Texas market (1.38 in cell H13) than they are to the California market (0.92 in cell F13)
Example 2:
If you compare the sales of bananas in California to the sales of kiwi in Pennsylvania, the Percentage of Total figures show that significantly more bananas were sold in California (8.01% in cell F4) than kiwis in Pennsylvania (6.94% in cell G6).

However, if you look at the Index data, you can see that kiwis are more important to the Pennsylvania market (1.06 in cell G15) than bananas are to the California market (0.92 in cell F13).

REFERENCES

Microsoft Excel 97

For more information about creating PivotTables, click the Index tab in Microsoft Excel Help or MS Excel Help, type the following text
PivotTables, creating
and then double-click the selected text to go to the "Create a PivotTable" topic.

Microsoft Excel 7.0

For more information about PivotTables in Microsoft Excel 7.0, click Answer Wizard on the Help menu and type:
Pivot Tables

Microsoft Excel 5.0

For more information about PivotTables in Microsoft Excel 5.0, click the Search button in Help and type:
Pivot Tables

Properties

Article ID: 151515 - Last Review: August 17, 2005 - Revision: 1.3
APPLIES TO
  • Microsoft Excel 97 Standard Edition
  • Microsoft Excel 95 Standard Edition
  • Microsoft Excel 5.0 Standard Edition
  • Microsoft Excel 98 for Macintosh
Keywords: 
kbhowto kbinfo kbualink97 KB151515
Retired KB Content Disclaimer
This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com