Summarizing Excel PivotTables with GETPIVOTDATA Function.

          Have you ever tried to select a cell within Excel’s PivotTable to create a regular link, only to realize that such formula cannot be easily copied over? You might be generating GetPivotData function without realizing it. As this Microsoft’s help page tells us: GETPIVOTDATA function “returns data stored in a PivotTable report. You can use GETPIVOTDATA to retrieve summary data from a PivotTable report, provided the summary data is visible in the report.” Main takeaways are the following: 1) GETPIVOTDATA is a summary function, and 2) it only works with visible PivotTable data. It’s syntax includes: Data_field – required field referencing data field of interest; Pivot_table – reference to any cell or range of cells withing a PivotTable report; and optional Field/Item combination, with text values enclosed in quotations. While, GetPivotData feature is activated by default, you can easily turn it off by selecting the Options menu on the ANALYZE Ribbon, and checking off “Generate GetPivotData” selection:

          To demonstrate an example of using this function, we can use City Of Chicago salary data provided via city’s data portal. After pivoting this table, we can summarize already summarized PivotTable data. To get total salary expense for the Fire department, we will create the following formula, which could also subtotal detailed records if visible.

=GETPIVOTDATA(“Salary Spend”,B8,”Department”, “FIRE”), where “Salary Expense” is the name of the field, which will use to retrieve data of interest, B8 is a cell within our PivotTable, “Department”, “FIRE” specifies our condition – Fire department. Plugging in this formula into our workbook, we yield $422,413,695 salary expense. Omitting condition from this formula: =GETPIVOTDATA(“Salary Expense”,B8) would give us grand total for all salaries paid – $2,429,980,941

          Similarly, to get employee count for the Aviation department, we would use this formula:
=GETPIVOTDATA(“Employee Count”,B21,”Department”, H12), where H12 refers to the name of the department.

          To use a standard Excel cell link, we can easily type cell reference in our formula. This method would allow us to correctly calculate average salary by department, as well as it’s variance from the mean. You can follow along using workbook provided.

          Please leave a comment and tell us how YOU use GETPIVOTDATA function.