Microsoft KB Archive/161538

= XL97: How to Use the GetPivotData Worksheet Function =

Article ID: 161538

Article Last Modified on 10/22/2000

-

APPLIES TO


 * Microsoft Excel 97 Standard Edition

-



This article was previously published under Q161538





SUMMARY
Microsoft Excel 97 has a new worksheet function, GetPivotData, that allows you to work with PivotTable data outside of a PivotTable.

This article contains an example that uses the GetPivotTable function to retrieve data from a PivotTable.



MORE INFORMATION
The GetPivotData function allows you to retrieve summary data from a PivotTable, provided that the data is visible in the PivotTable.

The syntax for the GetPivotData function is as follows: =GETPIVOTDATA(pivot_table, name) where the "pivot_table" argument is a reference to a cell in the PivotTable you want to analyze. The "pivot_table" argument can also be a range of cells in the PivotTable, a name for the range that contains the PivotTable, or a label stored in a cell above the PivotTable. The "name" argument is a text string that is enclosed in quotation marks and describes the data you want to summarize.

Using the GetPivotData Function
 Save and close any open workbooks, and then create a new workbook.  Type the following data in Sheet1:       A1: Name    B1: Sales    C1: Region A2: bob    B2: 1        C2: east A3: sue    B3: 2        C3: west A4: bob    B4: 3        C4: west A5: mary   B5: 4        C5: west A6: sue    B6: 5        C6: north A7: bob    B7: 6        C7: south A8: sue    B8: 7        C8: east  Select A1:C8, and click PivotTable Report on the Data menu. In the PivotTable Wizard - Step 1 of 4 dialog box, select "Microsoft Excel list or database" if it is not selected, and then click Next. In the PivotTable Wizard - Step 2 of 4 dialog box, click Next. In the PivotTable Wizard - Step 3 of 4 dialog box, drag the Name button into the ROW field, drag the Sales button into the DATA field, and drag the Region button into the COLUMN field. Click Next.</li> In the PivotTable Wizard - Step 4 of 4 dialog box, click Existing Worksheet, click cell A10 on Sheet1 (this step places the reference Sheet1!$A$10 in the RefEdit box), and then click Finish.</li> Select cell A10 (this step selects the entire PivotTable), point to Name on the Insert menu, and then click Define.</li> In the Define Name dialog box, type PT1 in the Names In Workbook box, and then click OK.

This step defines the range for the PivotTable as PT1 .</li>  In cell E1, type the following formula: =GETPIVOTDATA(PT1,"bob east") The result is a value of 1, because the total for "bob" in the "east" region is 1. </li>  In cell E2, type the following formula: =GETPIVOTDATA(PT1,"west") The result is a value of 9, because the total of "west" region is 9. </li>  In cell E3, type the following formula: =GETPIVOTDATA(PT1, "sum of sales") The result is a value of 28, because the total of all the sales in all regions is 28. </li></ol>

<div class="references_section">