What is GetPivotData in Google Sheets?
The GETPIVOTDATA function in Google Sheets extracts data from a pivot table with some specific criteria. It is very helpful to analyze data and understanding this function helps master data retrieval with GETPIVOTDATA. The function uses a formula to pull data from a pivot table and we used specified fields and item names to extract values.
The function retrieves data without any changes to the structure of the pivot table. For example, in the table below, you can find the total sales generated by specific individuals for a product in a pivot table that summarizes sales data. To obtain the sales made by Ling, we write the following formula.
=GETPIVOTDATA(“Sales”,D1,”Name”,”Ling”)
You get the accurate result of $6,100.
Key Takeaways
- The GETPIVOTDATA function In Google Sheets allows you to extract specific data from a created pivot table.
- It helps retrieve certain values in a pivot table without manually looking up the data.
- The syntax of GETPIVOTDATA function in Google Sheets is as follows
- =GETPIVOTDATA(data_field, pivot_table, [field1, item1, field2, item2, …]).
- You can manually build your pivot table by customizing the options, Rows, Columns, Values, and Filters.
- When Google Sheets creates the pivot table, you get a Pivot table editor panel on the right.
Syntax
Pivot tables in Google Sheets are very easy to create and are powerful too. Let us look at the syntax of the Google Sheets.
It is as follows:
=GETPIVOTDATA(data_field, pivot_table, [field1, item1, field2, item2], …)
Arguments:
- data_field (required): The name of the field in the pivot table from which you want to extract data.
- pivot_table (required): A cell in the target pivot table, to identify the pivot table. It is usally the top left cell of the table.
- [field1, item1, field2, item2…] (optional): Describes the required data. You can add up to 126 pairs, in any order. Values other than numbers and dates should be enclosed in double quotes “”.
Why do we use a pivot table?
A pivot table is used to summarize the data, especially when we have large datasets. If your dataset is small, you can understand its content at a glance! However, when it comes to large datasets, it is difficult to analyze or understand the data due to the huge amount of information. It is where the pivot table comes into play.
When it comes to data sets with a large number of columns, each offering different information about that sale, as shown above, pivot tables are used. If you want to derive more insights, we must take the data a notch up. As shown below, we have used pivot table to summarize the sales details of each product. The pivot table allows you to filter the table in a fraction of the time with no human error.
How to Use GetPivotData in Google Sheets?
Setting up a pivot table in Google Sheets involves a series of steps, always ensure the data is organized accurately.
Here’s a step-by-step guide on creating a pivot table and using GETPIVOTDATA.
Step 1: As a beginning step, let us ensure that the dataset is arranged neatly in a table with column-wise headers. This helps create an accurate pivot table, as it is easy to categorize the data. If you’re working with some sales data, we can have the following columns Date, Region, Sales.
Step 2: Once we have the data ready, let us insert a pivot table. For this, select the required data range.
Go to Insert -> Pivot Table.
Step 3: A Create pivot table pop-up appears. Give the address of a New Sheet or the cell in the existing sheet where you want the pivot table to begin. Here, we give Sheet2!E1 as we want the pivot table to start here.
Press Create.
A new sheet will open, showing a blank pivot table editor on the right side. Drag and drop fields into the Rows, Columns, and Values sections according to how you want your data summarized.
Step 4: Now, your pivot table is set up.
Step 5: Let’s start using GETPIVOTDATA to extract the data you need. Enter the following data in cell E14.
=GETPIVOTDATA(“Sales”,E1,”Date”,”1-1-2025″,”Region”,”West”)
Here, we are looking for sales data on the date 1-1-2025 in the West region.
Step 6: You get the specific data of the total sales made on 1-1-2025 in the West region.
Examples
Pivot tables are very user-friendly and not too complicated once you understand the nuances of how to use them. Let us look at some interesting examples below on how to go about it.
Example #1
In this interesting example, we have some data about the scores of students in an examination. We construct a pivot table and extract some required values. Let’s look at how to do this.
Step 1: We have some data in the table as shown below. Let us create a pivot table for the same. To create a pivot table, select the required data range.
Go to Data -> Pivot Table.
Step 2: Add Student under Rows. Then check the Show totals box.
Step 3: Also add Maths and Physics under Rows.
Step 4: You get a Pivot table as shown below.
Here, your Pivot Table starts in cell F1. To extract the Physics marks of Davy, you can use the GETPIVOTDATA function:
=GETPIVOTDATA(“SUM of Physics”, F1, “Student”, “Davy”)
Press Enter.
Example #2 – Extract a column total from sales data
In this example, let us construct a pivot table shows the sales of some vegetables, the day it is sold and the grand total. Let us use the GETPIVOTDATA in Google Sheets function to extract the grand total sales value.
Step 1: We have some data in the table as shown below. Let us create a pivot table for the same. To create a pivot table, select the required data range.
Go to Data -> Pivot Table.
Step 2: Go to Rows. Click on Add and select Vegetable.
Check the option Show Totals. Also, under Columns, add Day, and under Values, add Sales.
Step 3: You get a Pivot table as shown below.
Our Pivot Table starts in cell E1. To extract the grand total of sales on Monday, you can use the GETPIVOTDATA function:
=GETPIVOTDATA(“Grand Total”, E1,”Day”, “Monday”)
Press Enter.
Example #3 – Extract a specific value from budget report
We have a budget report of an organization for its first quarter. Here, we have five columns representing the different departments, their sales in the months of January, February, and March. Let us look at how to extract the sales of the IT department in the month of February using the GETPIVOTDATA function.
Step 1: As seen in the earlier example, we construct a pivot table as shown below. Go to Data -> Pivot Table. Add Department under Rows and February under values.
Step 2: We get the pivot table, as shown below.
To extract the sales value of IT department in February, apply the following formula.
=GETPIVOTDATA(“SUM of February”, G1,”Department”, “IT”).
Here, we need to find the value for “Sum of February.”
Important Things to Note
- All the arguments used in the GETPIVOTDATA formula are not case-sensitive, except the field names.
- In the scenario of using a custom heading for a particular field in your pivot table, the same custom heading should be used in the GETPIVOTDATA formula instead of the original column name in the source table.
- If you edit any of the cells in the source table and if it presents in the pivot table, these updates will automatically be reflected in your pivot table. However, if you add new rows or columns, you have to manually update the pivot table.
- If the GETPIVOTDATA GETPIVOTDATA In Google Sheets function doesn’t find a matching field or item combination, it returns the #REF! error.
Frequently Asked Questions (FAQs)
There are thousands of ways to customize a pivot table in Google Sheets. First coming to the rows and columns, they help you build a two-dimensional data set based on which you can calculate your third-dimension values. We use Values to get the total values in our pivot table. The filter allows you to analyze only a specific subset of data.
A #REF! error in GETPIVOTDATA usually happens when the field name or item name we are referencing does not exist in the pivot table. It also occurs when the reference of the pivot table is invalid. Another reason is if the combination of fields and items doesn’t match any data in the Pivot Table.
We can use multiple fields and items in GETPIVOTDATA in Google Sheets to filter and obtain data. For example, to find the sales details, we can filter by region and by product.
Example:=GETPIVOTDATA(“Sales”, A1, “Product”, “Widget A”, “Region”, “East”)
Yes, you can use cell references for the fields and items in your formula. For example, the formula below, we can use references as shown.
Example:=GETPIVOTDATA(“Sales”, A1, “Product”, B1, “Region”, A1).
Download Template
This article must help understand GETPIVOTDATA in Google Sheets with its formulas and examples. You can download the template here to use it instantly.
Recommended Articles
Guide to What Is GETPIVOTDATA in Google Sheets. We learn its syntax & how to use it to extract data from a pivot table with examples. You can learn more from the following articles. –
Leave a Reply