Part 1: Introduce
💡 GETPIVOTDATA Function in Microsoft Excel
Definition
The GETPIVOTDATA function in Excel is used to query a Pivot Table and retrieve specific data based on the structure of the Pivot Table, much like using a database function.
Purpose
The primary purpose of the GETPIVOTDATA function is to extract summary data from a pivot table report.
Syntax & Arguments
GETPIVOTDATA(data_field, pivot_table, [field1, item1, field2, item2], ...) Explain the Arguments in the function
data_field: This is the name of the values field for which you want to get the data. It’s required.pivot_table: This is any cell reference or range within the Pivot Table. It’s also needed.field1, item1, field2, item2, ...: These optional field/item pairs specify the data you want to retrieve.
Return value
This function returns the requested data if the references are valid. If the connections are invalid or the data is not found, it will produce a #REF! error.
Remarks
This function is automatically created when you reference a value cell in a pivot table and can be turned off by disabling the ‘Generate GetPivotData’ option under PivotTable Options.
Part 2: Examples of GETPIVOTDATA Function in Microsoft Excel
Example 1: Retrieve Total Sales for a Specific Product
Purpose of Example: To retrieve the total sales for a specific product.
Data table:
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Product | Region | Quarter | Sales | Total Sales for A1 |
| 2 | A1 | East | Q1 | $20,000 | =GETPIVOTDATA(“Sales”, A1) |
| 3 | A1 | West | Q1 | $30,000 | |
| 4 | A1 | East | Q2 | $25,000 |
Result table:
| E | |
|---|---|
| 2 | $20,000 |
How to use the formula: To retrieve the total sales for product A1, reference the cell in the Pivot Table where this data is (A1) and specify the ‘Sales’ data_field.
Example 2: Find Out the Sales of a Product in a Specific Region
Purpose of Example: To find out the sales of product A1 in the East region.
Data table:
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Product | Region | Quarter | Sales | Sales for A1 in East Region |
| 2 | A1 | East | Q1 | $20,000 | =GETPIVOTDATA(“Sales”, A1, “Region”, “East”) |
| 3 | A1 | West | Q1 | $30,000 | |
| 4 | A1 | East | Q2 | $25,000 |
Result table:
| E | |
|---|---|
| 2 | $45,000 |
How to use the formula: To find out the sales of product A1 in the East region, you need to specify the ‘Region’ and ‘East’ in the function.
Example 3: Calculate Quarterly Sales of a Product in a Specific Region
Purpose of Example: To calculate the Q1 sales of product A1 in the East region.
Data table:
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Product | Region | Quarter | Sales | Q1 Sales for A1 in East Region |
| 2 | A1 | East | Q1 | $20,000 | =GETPIVOTDATA(“Sales”, A1, “Region”, “East”, “Quarter”, “Q1”) |
| 3 | A1 | West | Q1 | $30,000 | |
| 4 | A1 | East | Q2 | $25,000 |
Result table:
| E | |
|---|---|
| 2 | $20,000 |
How to use the formula: To calculate the Q1 sales of product A1 in the East region, you need to specify both the ‘Region’, ‘East’, ‘Quarter’, and ‘Q1’ in the function.
Example 4: Using GETPIVOTDATA with the IF Function
Purpose of Example: To determine if the sales of product A1 in the East region exceeded a specific target.
Data table:
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Product | Region | Quarter | Sales | Target | Sales vs. Target |
| 2 | A1 | East | Q1 | $20,000 | $18,000 | =IF(GETPIVOTDATA(“Sales”, A1, “Region”, “East”) > E2, “Exceeded”, “Not Exceeded”) |
| 3 | A1 | West | Q1 | $30,000 | $25,000 | |
| 4 | A1 | East | Q2 | $25,000 | $22,000 |
Result table:
| F | |
|---|---|
| 2 | Exceeded |
How to use the formula: To determine if the sales exceeded the target, we used the IF function, which checks if the value from the GETPIVOTDATA function (sales for A1 in East) is greater than the target. If it is, it returns “Exceeded”; if not, it replaces “Not Exceeded”.
Example 5: Using GETPIVOTDATA with the SUM Function
Purpose of Example: To calculate the total sales of product A1 for both East and West regions in Q1.
Data table:
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Product | Region | Quarter | Sales | Total Sales in Q1 |
| 2 | A1 | East | Q1 | $20,000 | =SUM(GETPIVOTDATA(“Sales”, A1, “Region”, “East”, “Quarter”, “Q1”), GETPIVOTDATA(“Sales”, A1, “Region”, “West”, “Quarter”, “Q1”)) |
| 3 | A1 | West | Q1 | $30,000 | |
| 4 | A1 | East | Q2 | $25,000 |
Result table:
| E | |
|---|---|
| 2 | $50,000 |
How to use the formula: To get the total sales in Q1 for both East and West regions, we use the SUM function to add up the sales data from the East and West regions for the A1 product in Q1.
Example 6: Using GETPIVOTDATA with the VLOOKUP Function
Purpose of Example: To find the sales of a product based on the user’s input.
Data table:
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Product | Region | Quarter | Sales | User Input | Sales Lookup |
| 2 | A1 | East | Q1 | $20,000 | A1 | =GETPIVOTDATA(“Sales”, VLOOKUP(E2, A2:D4, 1, FALSE)) |
| 3 | A1 | West | Q1 | $30,000 | ||
| 4 | A1 | East | Q2 | $25,000 |
Result table:
| F | |
|---|---|
| 2 | $20,000 |
How to use the formula: We use VLOOKUP to find the reference to the Pivot Table based on the user’s input, then we use this reference in the GETPIVOTDATA function to find the corresponding sales.
Example 7: Using GETPIVOTDATA with the COUNT Function
Purpose of Example: To count how many quarters sales of product A1 exceeded a specific target in the East region.
Data table:
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Product | Region | Quarter | Sales | Target | Quarters Exceeding Target |
| 2 | A1 | East | Q1 | $20,000 | $18,000 | =COUNT(IF(GETPIVOTDATA(“Sales”, A1, “Region”, “East”) > E2, 1, “”)) |
| 3 | A1 | East | Q2 | $30,000 | $25,000 | |
| 4 | A1 | East | Q3 | $25,000 | $22,000 |
Result table:
| F | |
|---|---|
| 2 | 2 |
How to use the formula: We use the IF function to return a value of 1 each time the sales in the East region for A1 exceed the target. The COUNT function is then used to count these occurrences.
Example 8: Using GETPIVOTDATA with the MAX Function
Purpose of Example: To find the highest sales quarter of product A1 in the East region.
Data table:
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Product | Region | Quarter | Sales | Max Sales Quarter |
| 2 | A1 | East | Q1 | $20,000 | =MAX(GETPIVOTDATA(“Sales”, A1, “Region”, “East”, “Quarter”, “Q1”), GETPIVOTDATA(“Sales”, A1, “Region”, “East”, “Quarter”, “Q2”), GETPIVOTDATA(“Sales”, A1, “Region”, “East”, “Quarter”, “Q3”)) |
| 3 | A1 | East | Q2 | $30,000 | |
| 4 | A1 | East | Q3 | $25,000 |
Result table:
| E | |
|---|---|
| 2 | $30,000 |
How to use the formula: We use the MAX function to find the maximum value among sales of product A1 in the East region across Q1, Q2, and Q3.
Example 9: Using GETPIVOTDATA with the MIN Function
Purpose of Example: To find the lowest sales quarter of product A1 in the East region.
Data table:
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Product | Region | Quarter | Sales | Min Sales Quarter |
| 2 | A1 | East | Q1 | $20,000 | =MIN(GETPIVOTDATA(“Sales”, A1, “Region”, “East”, “Quarter”, “Q1”), GETPIVOTDATA(“Sales”, A1, “Region”, “East”, “Quarter”, “Q2”), GETPIVOTDATA(“Sales”, A1, “Region”, “East”, “Quarter”, “Q3”)) |
| 3 | A1 | East | Q2 | $30,000 | |
| 4 | A1 | East | Q3 | $25,000 |
Result table:
| E | |
|---|---|
| 2 | $20,000 |
How to use the formula: We use the MIN function to find the minimum value among sales of product A1 in the East region across Q1, Q2, and Q3.
Example 10: Using GETPIVOTDATA with the AVERAGE Function
Purpose of Example: To find the average sales of product A1 in the East region across all quarters.
Data table:
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Product | Region | Quarter | Sales | Average Sales |
| 2 | A1 | East | Q1 | $20,000 | =AVERAGE(GETPIVOTDATA(“Sales”, A1, “Region”, “East”, “Quarter”, “Q1”), GETPIVOTDATA(“Sales”, A1, “Region”, “East”, “Quarter”, “Q2”), GETPIVOTDATA(“Sales”, A1, “Region”, “East”, “Quarter”, “Q3”)) |
| 3 | A1 | East | Q2 | $30,000 | |
| 4 | A1 | East | Q3 | $25,000 |
Result table:
| E | |
|---|---|
| 2 | $25,000 |
How to use the formula: We use the AVERAGE function to find the average of the sales of product A1 in the East region across Q1, Q2, and Q3.
Part 3: Tips and tricks
✨ You can use the GETPIVOTDATA function to create dynamic references to pivot tables, which can be particularly useful if the structure of your pivot table changes.
💡 It’s important to note that the GETPIVOTDATA function is case-sensitive. Ensure the text you use matches precisely with the text in the Pivot Table.