XL98: How to Use the GetPivotData Worksheet Function (180371)


The information in this article applies to:

  • Microsoft Excel 98 Macintosh Edition
This article was previously published under Q180371

SUMMARY

Microsoft Excel 98 Macintosh Edition includes 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)

Note that 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

To see an example that uses the GetPivotData function, follow these steps:
  1. Save and close any open workbooks, and then create a new workbook.
  2. 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
  3. Select A1:C8 and click PivotTable Report on the Data menu.
  4. In the "PivotTable Wizard - Step 1 of 4" dialog box, click "Microsoft Excel list or database" if it is not selected, and then click Next.
  5. In the "PivotTable Wizard - Step 2 of 4" dialog box, click Next.
  6. 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. Then, click Next.
  7. 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.
  8. Select cell A10 (this step selects the entire PivotTable), point to Name on the Insert menu, and then click Define.
  9. 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.
  10. 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.
  11. 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.
  12. 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.

REFERENCES

For more information about the GETPIVOTDATA Worksheet Function, click the Index button in Microsoft Excel Help, type the following text

getpivotdata

click Show Topics, and then click Go To to view the "GETPIVOTDATA" topic.
Modification Type:MajorLast Reviewed:9/11/2002
Keywords:kbualink97 KB180371
©2002 Microsoft Corporation. All rights reserved.