XL: How to Index Data in a PivotTable

• 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))

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 the Options button and choose % of Total from the Show Data As section. 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 the Options button and choose Index from the Show Data As section. Click OK in the Pivot Table Field dialog box.

13. Click Next.

14. In Microsoft Excel 97 or Microsoft Excel 98 Macintosh Edition, click the Existing worksheet button, 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

Microsoft Excel 7.0

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

   Pivot Tables

   Pivot Tables