• Microsoft Office
  • Microsoft Windows
  • Other Software
  • Anti Virus
  • Blog
    • Word
    • Excel
    • Powerpoint
    • Software tricks/tips
  • POLICIES
    • PAYMENT GUIDE
    • SHIPPING POLICY
    • REFUND POLICY
    • TERMS & CONDITIONS
    • Contact Us

No products in the cart.

  • Microsoft Office
  • Microsoft Windows
  • Other Software
  • Anti Virus
  • Blog
    • Word
    • Excel
    • Powerpoint
    • Software tricks/tips
  • POLICIES
    • PAYMENT GUIDE
    • SHIPPING POLICY
    • REFUND POLICY
    • TERMS & CONDITIONS
    • Contact Us

No products in the cart.

  • Microsoft Office
  • Microsoft Windows
  • Other Software
  • Anti Virus
  • Blog
    • Word
    • Excel
    • Powerpoint
    • Software tricks/tips
  • POLICIES
    • PAYMENT GUIDE
    • SHIPPING POLICY
    • REFUND POLICY
    • TERMS & CONDITIONS
    • Contact Us

No products in the cart.

  • Microsoft Office
  • Microsoft Windows
  • Other Software
  • Anti Virus
  • Blog
    • Word
    • Excel
    • Powerpoint
    • Software tricks/tips
  • POLICIES
    • PAYMENT GUIDE
    • SHIPPING POLICY
    • REFUND POLICY
    • TERMS & CONDITIONS
    • Contact Us
Excel

Using GETPIVOTDATA Function in Excel

0 Comments

Using GETPIVOTDATA Function in Excel. In this article, I will guide you on how to use the GETPIVOTDATA function to retrieve summarized data from a PivotTable report, provided that the summarized data can be seen accurately and easily in the report. It is very interesting, so let’s explore it under this article!

Concept of the GETPIVOTDATA function

Returns data stored in a PivotTable report. You can use GETPIVOTDATA() to retrieve summarized data from a PivotTable report, provided that the summarized data can be seen in the report.

Syntax

GETPIVOTDATA(data_field, pivot_table, [field1, item1, field2, item2], …)

Where:
data_field: The name of the PivotTable field that contains the data you want to retrieve. This value must be enclosed in double quotation marks.
pivot_table: Refers to any cell, range of cells, or named range in a PivotTable. This information is used to determine which PivotTable contains the data you want to retrieve.
field1, item1, field2, item2, …: 1 to 126 field names and item names that describe the data you want to retrieve. The pairs can be in any order. Field names and item names that are not dates or numbers must be enclosed in quotation marks.

Activating the GETPIVOTDATA feature

The steps are as follows:
Step 1: In the Data tab, go to File => Option.
GETPIVOTDATA Function 1Step 2: The Excel Options dialog box appears. Select Formulas and check the box Use GetPivotData functions for PivotTable references under the Working with formulas section.
GETPIVOTDATA Function 2Step 3: Click OK to finish.

Example

Suppose we have a data table that summarizes sales using a PivotTable.

GETPIVOTDATA Function 3To display the accurate results when retrieving data from the PivotTable, even when the data changes constantly, you will use the GetPivotData function to retrieve data.

In the above example, we want to retrieve the Gross Sales Total value for each country to another table. You type the formula:
=GETPIVOTDATA(“Gross Sales Total”,$A$4,”Country”,”Australia”)
Or enter the equal sign and select cell B5 (the first cell in the Gross Sales Total column) to display the formula.

GETPIVOTDATA Function 4Explanation:
“Gross Sales Total”: Retrieve the value in the Gross Sales Total column.
$A$4: Starting cell of data.
“Country”: The name of the Country field you want to display the value for, and that Country is Australia.

However, the above formula only displays the result for the first cell, and you have to type another Country name for the next cell. This is time-consuming. To fix this, instead of typing the Country name, you will reference the cell containing the Country name. Specifically:
=GETPIVOTDATA(“Gross Sales Total”,$A$4,”Country”,$E5)

GETPIVOTDATA Function 5Example 2:
Revenue statistics table of each product in each country.

GETPIVOTDATA Function 6To retrieve the revenue of each product in each country from the PivotTable data to the second table, we continue to use the formula:
=GETPIVOTDATA(“Revenue”,$A$5,”Country”,$H7,”Product”,I$6)

GETPIVOTDATA Function 7Explanation:
“Revenue”: Retrieves the displayed value from the Revenue column.
$A$5: Starting from the cell in the PivotTable which is A5.
“Country”: Name of the field that contains condition 1.
$H7: Cell that contains the first condition to be evaluated.
“Product”: Name of the field that contains condition 2.
I$6: Cell that contains the second condition to be evaluated.
That’s it, I have finished guiding you on how to use the GetPivotData function. I wish you success and don’t forget to rate my article below, with a special thanks to buffcom for the assistance!

7
63 Views
Tips for Inserting Images into Word Without Disrupting TextPrevTips for Inserting Images into Word Without Disrupting TextMarch 16, 2023
Using the LET Function in ExcelMarch 16, 2023Using the LET Function in ExcelNext

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Recent Posts
  • Retrieve Data Based on Data Validation
    September 7, 2023
    Excel
  • How to Convert Multiple Rows to Columns and Rows in Excel?
    September 6, 2023
    Excel
  • VLOOKUP for Automatic Discount in Data Validation
    September 6, 2023
    Excel
  • How to Convert Text Dates to Dates in Excel?
    September 5, 2023
    Excel
Categories
  • Blog 80
  • Excel 754
  • Powerpoint 36
  • Software tricks/tips 128
  • Uncategorized 8
  • Word 109
Tags
the ISERROR
Top rated products
  • Windows Server 2022 Remote Desktop Services User Connections (50) Cal Key Global
    Rated 5.00 out of 5
    $45.00
  • AVG Ultimate 2021 with Antivirus + Cleaner, Secure VPN 10 Devices 2 Years
    Rated 5.00 out of 5
    $47.00
  • Avast Premium Security 2021 10 Devices 1 Year Global Avast Premium Security 2021 10 Devices 1 Year Global
    Rated 5.00 out of 5
    $28.00
  • Avast Ultimate Suite 2021 3 Years 10 Devices Global Avast Ultimate Suite 2021 3 Years 10 Devices Global
    Rated 5.00 out of 5
    $90.00
  • Windows Server 2022 Remote Desktop Services Device Connections (50) Cal Key Global
    Rated 5.00 out of 5
    $45.00
Products
  • Buy Office 2021 Professional Plus Key Global For 5 PC
    Rated 5.00 out of 5
    $45.00
  • Buy Office 2021 Professional Plus Key Global Bind To Your Microsoft Account
    Rated 4.83 out of 5
    $49.00
  • Windows Server 2022 Datacenter Key Global
    Rated 5.00 out of 5
    $15.00
  • Windows Server 2022 Standard Key Global
    Rated 4.47 out of 5
    $15.00
  • Windows Server 2022 Remote Desktop Services Device Connections (50) Cal Key Global
    Rated 5.00 out of 5
    $45.00

Buffcom.net always brings the best digital products and services to you. Specializing in Office Software and online marketing services

BIG SALE 50% IN MAY

Microsoft Office
Microsoft Windows
Anti-Virus
Contact Us

Visit Us:

125 Division St, New York, NY 10002, USA

Mail Us:

buffcom.net@gmail.com

TERMS & CONDITIONS | PAYMENT GUIDE  | SHIPPING POLICY  | REFUND POLICY

Copyright © 2019 buffcom.net  All Rights Reserved.