XL: How to Index Data in a PivotTable

ID: Q151515

The information in this article applies to:

Microsoft Excel for Windows, versions 5.0, 5.0c
Microsoft Excel for Windows 95, version 7.0
Microsoft Excel 97 for Windows
Microsoft Excel for the Macintosh, versions 5.0, 5.0a
Microsoft Excel 98 Macintosh Edition

SUMMARY

The PivotTable INDEX Function provides a weighted average by using the following formula:

   ((value in cell) x (Grand Total)) / ((Grand Row Total) x
      (Grand Column Total))

The Index option for "Show data As" in the Pivot Field dialog box of the PivotTable Wizard can be very useful in determining the relative importance of values in the Row Field to values in the Column Field.

This article show you how to create two PivotTables, one that displays Percentage of Total sales, and another that displays an Index of sales. It will then demonstrate the advantage of using the INDEX Function by showing the differences between Sales Volume as a percentage of total and Sales Volume as an index.

MORE INFORMATION

To set up a worksheet with sample data and create two PivotTables from the worksheet data, follow these steps:

 1. In a worksheet, 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, the button
    labeled State to the box labeled COLUMN, and the button labeled Sales
    to the box labeled DATA.

 7. In the Data Field, double-click Sum Of Sales to bring up the Pivot
    Table Field dialog box.

 8. Click Options and click "% of Total" under Show Data As.
    Click OK in the Pivot Table Field dialog box.

 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 first PivotTable in cells E10 to I17.

Analyzing the Results

Example 1:

Comparing 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, by looking at the Index data, one can see that bananas are much more important to the Texas market (1.14 in cell H13) than they are to the California market (1.06 in cell F13)

Example 2:

Comparing the sales of bananas in California (8.01% in cell F4) to kiwi in Pennsylvania (6.94% in cell G6), the Percentage of Total figures show that significantly more bananas were sold than kiwi in the respective states.

However, by looking at the Index data, one can see that kiwi is nearly as important to the Pennsylvania market (1.06 in cell G15) as bananas are to the California market (1.06 in cell F13), weighing in the total sales of course.

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

Additional query words: 5.00 5.00a 5.00c 7.00 8.00 97 XL97 XL98

Keywords          : kbualink97 xlpivot 
Version           : WINDOWS:5.0,5.0c,7.0,97; MACINTOSH:5.0,5.0a,98
Platform          : MACINTOSH WINDOWS
Issue type        : kbhowto kbinfo

Last Reviewed: January 8, 1999