GETPIVOTDATA formula: an easy guide

The GETPIVOTDATA function is a tool used in spreadsheet applications that allows users to extract specific data from a pivot table, making data analysis and reporting more accurate and dynamic. In this article, we will find out what GETPIVOTDATA is, its purpose, and how it works.

What is GETPIVOTDATA?

The GETPIVOTDATA function is designed to extract data from a pivot table based on specified field and item names. Using the function, you can reference precise data points from a pivot table, facilitating detailed reports and complex calculations that dynamically update as the underlying data or pivot table structure changes. Here is an easy example.

Let’s imagine, that you have at your disposal a huge amount of data about the export of a product that your company sells in all countries of the world.

Now, you are asked to write a report on sales that take place in a specific country. Starting from the pivot table containing all the information, the GETPIVOTDATA function allows you to extract only those data that you need to insert in the report, and, if necessary, to isolate them in a separate worksheet. In this way, the attention will only be focused on the necessary information.

The advantage of the function is that it will automatically update the extracted data when the one in the original table changes, thus saving you time and effort.

To summarize, here are the reasons why you should use the GETPIVOTDATA function:

Precise Data Extraction: Pivot tables summarize data, but it’s hard to pinpoint specific data within them. GETPIVOTDATA lets you pull out exact data points based on criteria (e.g., extracting the total sales for a specific product in a specific region).

Dynamic Reporting: For dashboards or reports using specific data from a pivot table, GETPIVOTDATA ensures the data is accurate and updates automatically if the pivot table changes.

Complex Calculations: If you need to do more calculations or analyses with pivot table data, GETPIVOTDATA helps you extract the necessary data to use in other formulas, making your calculations more dynamic.

Automation and Consistency: GETPIVOTDATA keeps your data retrieval consistent. If the pivot table layout changes (like moving fields or adding new data), GETPIVOTDATA still gets the correct data, unlike manual cell references that might cause errors.

How to use GETPIVOTDATA

We have explained what this function is for, now let’s see in practice how to use it with the help of ONLYOFFICE Spreadsheet Editor.

The formula has the following syntax:

GETPIVOTDATA(data_field, pivot_table, [field], [item], …)

Now, let’s analyze the arguments of the formula:

data_field sets the name of the pivot table field that contains the required data. The argument needs to be in quotes.

pivot_table is used to determine which pivot table contains the required data. It can refer to any cell, range of cells, or named range of cells inside the pivot table.

field/item are pairs of field names and item names that may be used to describe the required data.

Example of use

We will analyze an easy example that will help you understand how to use the function.

First of all, you should extract the data you need to use in a pivot table. If you don’t know how to create one, we suggest you read this article.

As you can see from the table, we decided to isolate the sales data of the North and South regions in the pivot table.

Now, with the help of the GETPIVOTDATA function, we will return the sales data of olive oil in the North region.

We will use the above formula and adapt it to our needs:

We used the formula =GETPIVOTDATA(“Sum of North”,$A$9,”Product”,”Olive Oil”), and this is how we set it up:

“Sum of North” is the field in the pivot table that contains the required data.

$A$9 is a random cell inside the pivot table. This only functions as a reference, and in our case, we can choose any cell in the range that goes from A9 to C14.

“Product” is the field name for the row labels in the original table.

“Olive Oil” is the item we chose within the “Product” field.

We have obtained the result we have indicated in the formula. But now let us see what happens if the data in the original table changes.

We have replaced the value 7000 with the value 15000 in the original table. Now all we have to do is right-click on a random point in the pivot table and select Refresh.

In this way, the value was updated both in the pivot table and in the cell where we used the GETPIVOTDATA formula.

As we have said, the function is particularly useful if you want to write a report in which only some specific and selected data are shown. So what should we do if we want to report the result on another sheet of the same document?

It is very easy, simply add a new sheet in the document, or open an existing one. Select the cell you want the result to be shown in and repeat the formula as before, but specify the sheet of origin before the cell reference. In our case, the pivot table is in sheet1.

=GETPIVOTDATA(“Sum of North”,Sheet1!$A$9,”Product”,”Olive Oil”)

And here you have the result in the required sheet.

Now that you know how to use this formula, analyzing your data and writing reports will become much easier and faster. Try it for yourself!

If you need a powerful spreadsheet editor, you can create your ONLYOFFICE Docs account for free right now and test the capabilities of all the other editors within the suite.

START ONLINE      GET DESKTOP APP