We all know the power of Pivot Tables in Google Sheets. They allow us to analyze and summarize large sets of data quickly and efficiently. But did you know that you can go even further with Pivot Tables by using the GETPIVOTDATA function to extract multiple aggregated values and create arrays or ranges? In this article, we will explore how to unlock the potential of GETPIVOTDATA arrays in Google Sheets and how it can be used to generate multiple charts from a single Pivot Table.
Fictitious Data for Testing GETPIVOTDATA Array Formulas
Before we dive into the examples, let’s set the stage with some fictitious data that we can use to test our GETPIVOTDATA array formulas. For our examples, we have a dataset with three columns: Player, Game, and Score. This dataset is suitable for testing multiple row grouping and row & column grouping. We have two players (John and Emily) and two games (Formula Frenzy and Data Daze). Each player participated in each game twice, and their scores are recorded in the third column.
To see the sample data and all the formula examples in action, you can access the sample sheet here.
Pivot Table with Multiple Row Grouping and GETPIVOTDATA Array Formulas
Let’s start by exploring an example with multiple row grouping in a Pivot Table. To test the GETPIVOTDATA function with multiple values, the first step is to create a Pivot Table report. Follow these steps to create a Pivot Table from the provided data with two-row grouping:
- Assume the table is in the cell range A1:C9 in “Sheet1.”
- Move to cell A11.
- Click on Insert > Pivot Table.
- In the “Data range” field, enter the range A1:C9.
- Select “Existing sheet” since we are inserting the Pivot Table in the existing sheet that contains the source data.
- Enter A11 in the provided field.
- Click “Create.”
Upon completing the above steps, Sheets will insert the layout of the Pivot Table and open the Pivot Table Editor panel on the sidebar. Now, let’s explore the GETPIVOTDATA array formula to retrieve multiple values from this Pivot Table report.
Fetching Multiple Data Points from a Pivot Table
The GETPIVOTDATA function is designed to extract specific data from a Pivot Table but cannot directly return multiple values. However, we can combine GETPIVOTDATA with the MAP function to retrieve multiple results. Here’s the syntax of the GETPIVOTDATA function:
GETPIVOTDATA(value_name, any_pivot_table_cell, [original_column, ...], [pivot_item, ...])Here are the key points to keep in mind:
- The GETPIVOTDATA function extracts specific data from a Pivot Table.
- It cannot directly return multiple values.
- MAP and LAMBDA functions expand its capabilities to retrieve multiple results.
Example Scenario #1 (Row Grouping)
Let’s retrieve the average scores of Emily and John in the game “Formula Frenzy.” To achieve this, we create a table with the Player, Game, and AVERAGE of Score columns. Note that the GETPIVOTDATA function does not support specifying more than one pivot_item directly. Instead, we utilize the MAP function to iterate over each value in the array, enabling the retrieval of multiple results. Enter the following array formula in cell G2, which will spill to G3:
=MAP(E2:E3, LAMBDA(a, GETPIVOTDATA(G1, A11, E1, a, F1, F2)))This formula will dynamically populate the table with the average scores for both players in the game “Formula Frenzy.”
Example Scenario #2 (Row Grouping)
In this example, let’s retrieve both the “AVERAGE of Score” and “SUM of Score” in the array formula. Create the table and enter the following nested MAP formula in cell G2:
=MAP(G1:H1, LAMBDA(x, MAP(E2:E3, LAMBDA(a, GETPIVOTDATA(x, A11, E1, a, F1, F2)))))This formula iterates over the columns in G1:H1 (representing AVERAGE and SUM) and retrieves the specified value using GETPIVOTDATA for each player-game combination.
Pivot Table with Row & Column Grouping and GETPIVOTDATA Array Formulas
Now let’s explore an example with both row and column grouping in a Pivot Table. The Pivot Table layout has been altered, with the “Player” field grouped and the “Game” field pivoted. To retrieve the data, create a table in cell E1:H3 and enter the following GETPIVOTDATA array formula in cell F2:
=MAP(F1:G1, LAMBDA(c, MAP(E2:E3, LAMBDA(r, GETPIVOTDATA(A11, A11, "Player", r, "Game", c)))))This formula dynamically populates the table with average scores for each player-game combination, extracted from the Pivot Table based on the specified aggregation.
Example Scenario #2 (Row and Column Grouping)
For this scenario, we’ll use the same Pivot Table but with an additional aggregation, which is SUM. Create the table in cell E1:H3 and enter the following GETPIVOTDATA array formula in cell G2:
=MAP(G1:H1, LAMBDA(x, MAP(E2:E3, F2:F3, LAMBDA(a, b, GETPIVOTDATA(x, A11, "Player", a, "Game", b)))))This formula populates the table with both average scores and the sum of scores for each player-game combination, extracted from the Pivot Table based on the specified aggregation.
GETPIVOTDATA Array and Charting in Google Sheets
Utilizing the GETPIVOTDATA function to return an array value in Google Sheets offers specific advantages, especially when creating charts from a Pivot Table. Instead of excluding the TOTAL/GRAND TOTAL rows and columns, we can use the combination of GETPIVOTDATA and MAP to fetch the required values from the Pivot Table and facilitate chart plotting.
For example, you can use the resulting table from our previous example to create a column or bar chart. Simply select the data range and choose the appropriate chart type.
Additional Tips
When generating the resulting table, you can employ the UNIQUE function in the source data to dynamically obtain the original_column values. Explore additional functions like VSTACK, HSTACK, TOCOL, and TOROW as they can enhance flexibility when working with the resulting table.
To learn more about Pivot Tables and GETPIVOTDATA array formulas in Google Sheets, check out the following resources:
Unlock the full potential of GETPIVOTDATA arrays in Google Sheets and take your data analysis to the next level! For more informative articles on Google Sheets and other topics, visit Crawlan.com.
Image Source: https://updf.com/wp-content/uploads/2023/05/copy-the-google-sheets-data.jpg
