# HOW TO: Index Data in a PivotTable in Excel 2000

This article has been archived. It is offered "as is" and will no longer be updated.
For a Microsoft Excel 98 and earlier version of this article, see 151515.

##### 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.

back to the top

### Create Sample Worksheet and PivotTables

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. To create the first PivotTable, select any cell that contains data, and then click PivotTable and PivotChart Report on the Data menu.
3. In the PivotTable Wizard, click Next.
4. Confirm that the Range setting is \$A\$1:\$C\$16. If it is, click Next. If it is not, drag to select cells A1:C16, and then click Next.
5. Click Existing Worksheet. Click cell E1, and then click Finish.
6. Drag the button that is labeled Fruit to the box that is labeled Drop Row Fields Here. Drag the button that is labeled State to the box that is labeled Drop Column Fields Here, and drag the button that is labeled Sales to the box that is labeled Drop Data Items Here.

When you are finished, the first PivotTable is in cells E1:I8.
7. Right-click in the data field, and then click Field Settings on the shortcut menu.
8. In the PivotTable Field dialog box, click Options. In the Show data as list, click % of total, and then click OK.
9. To create the second PivotTable, repeat steps 2 through 6.

NOTE: In step 4, when Excel asks whether you want your new report to be based on the same information as your existing report, click No. In step 5, click cell E10 to place the new PivotTable.

When you are finished, the second PivotTable is in cells E10 to I17.
10. After you create the second PivotTable, right-click in the data field, and then click Field Settings on the shortcut menu.
11. In the PivotTable Field dialog box, click Options. In the Show data as list, click Index, and then click OK.
back to the top

### Analysis of the Results

#### Example 1

If you compare the sales of bananas in Texas and California, the figures from the first PivotTable (Percentage of Sales data) 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 second PivotTable (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.916 in cell F13).

If you examine the tables more closely, you discover that Texas had zero kiwi sales (versus California's 12% kiwi sales), which makes other fruits (including bananas) more important to the overall sales amounts in Texas.

back to the top

#### Example 2

If you compare the sales of bananas in California to the sales of kiwi in Pennsylvania, the figures from the first PivotTable (Percentage of Sales data) show that significantly more bananas were sold in California (8.01% in cell F4) than kiwis in Pennsylvania (6.94% in cell G6).

However, by looking at the second PivotTable (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 (.916 in cell F13).

In a case like this, it can be difficult to tell why bananas, although a larger dollar amount, are not as big a portion of California's overall sales as kiwis are in Pennsylvania. One explanation could be that Pennsylvania kiwis, a moderately selling item, are high cost, therefore making them more valuable overall.

back to the top
##### REFERENCES
For more information about how to create PivotTables, click Microsoft Excel Help on the Help menu, type create a pivottable report in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

back to the top
XL2000 indexing summing totalling percent percents comparison comparing compare totals compares indexes indexed table tabl
Properties

Article ID: 213922 - Last Review: 12/05/2015 12:24:09 - Revision: 3.0

Microsoft Excel 2000 Standard Edition

• kbnosurvey kbarchive kbhowtomaster kbualink97 KB213922