Microsoft KB Archive/211949

= HOW TO: Use the GetPivotData Worksheet Function in Excel 2000 =

Article ID: 211949

Article Last Modified on 11/5/2003

-

APPLIES TO


 * Microsoft Excel 2000 Standard Edition

-



This article was previously published under Q211949



For a Microsoft Excel 97 version of this article, see 161538.

IN THIS TASK
SUMMARY
 * GetPivotData Function
 * How to Use the GetPivotData Function

REFERENCES



SUMMARY
This step-by-step article shows you how to use the GETPIVOTDATA function to retrieve data from a PivotTable. Microsoft Excel 2000 has a new worksheet function, GETPIVOTDATA, that allows you to work with PivotTable data outside of a PivotTable.

back to the top

GetPivotData Function
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 that 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 that you want to summarize.

back to the top

How to Use 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 then click PivotTable and PivotChart Report on the Data menu. In the PivotTable Wizard - Step 1 of 3 dialog box, select Microsoft Excel list or database if it is not selected, and then click Next. In the PivotTable Wizard - Step 2 of 3 dialog box, click Next. In the PivotTable Wizard - Step 3 of 3 dialog box, click Layout.</li> 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 OK.</li> In the PivotTable Wizard - Step 3 of 3 dialog box, click Existing Worksheet, click cell A10 on Sheet1 (this 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 in the "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>

back to the top

<div class="references_section">