12. Pivot Table Data in Formulas

Overview

You might be tempted to insert Pivot Table data into formulas but be careful, this action is has many pitfalls to be wary of.

Summary

  1. Formulas Using a Pivot Table Range (00:03)

    Including data from a Pivot Table in a formula is generally a bad idea. In this case, we create a formula that sums quarter 1 revenue for a selection of salespeople from our Pivot Table. 

    The formula performs its calculation correctly. However, if we adjust the Pivot Table, say by expanding the quarters to view monthly data, then the result of the formula changes. This is because the range of cells used in the formula no longer refers to quarter 1 revenue. For this reason, it’s never a good idea to use a range of cells from a Pivot Table in an external formula.

  2. Formulas Using a Pivot Table Cell (01:18)

    If we create a formula which uses a single cell from a Pivot Table, things work slightly differently. In this case, the formula won’t use a hard-coded cell reference. Instead, it uses a function called GETPIVOTDATA to record the various fields associated with the cell.

    However, this approach is still problematic. Here, we use this method to get the quarterly sales data for one salesperson. If we expand the quarters to view monthly data, then the formula returns an error because the Pivot Table no longer contains quarterly data. For this reason, it’s also a bad idea to use a single cell from a Pivot Table in an external formula.

     

Transcript

When working with Pivot tables, you might be tempted to include its values in an external formula.

The best advice I can probably give you in this situation is to simply don’t do it.

If you can’t find the output value that you want using filters, sorting, subtotals or percentage of options, you’re better off performing the calculation directly on the data set, like I show you in my course on Lookups and Database functions.

Let’s see why with a simple example.

Let’s say I’d like to add a Large Q1 sales of the first five Sales people.

So I’ll navigate to an external cell, I’ll write "=sum", open the bracket and select the first five cells.

When I close the bracket and press Enter, I get the correct answer, and all is good so far.

However, if I make a simple change to my Pivot table, for example expanding the Quarter columns with Alt + A J, the value now changes because the cells B9 to B13 no longer contain the quarter one data.

This is obviously very dangerous and can cause immediate errors, particularly if you give this spreadsheet to a colleague who’s not familiar with this limitation of Pivot tables.

Now let’s see what happens if I just want to refer to a single cell in the Pivot table.

So I’ll delete my previous formula and I’ll write "=" and simply navigate to that single cell.

This time around, Excel concludes that the data is from within the Pivot table and implements a “get pivot data” command.

Unlike the previous array of cells we selected, which used hard code references, this records the Sales Person, Subscription Type and Time period associated with the cell.

But again, when I do a simple change to the Pivot table, such as expanding columns, I get an error in the formula, because the quarter one column no longer exists.

Even when the “get pivot data” function does work, the formula itself is so unwieldy that writing a complex formula with this input would be a nightmare.

As I mentioned at the start of this course, Pivot tables are a very fast way of analyzing data sets, but as I’ve just shown you, they do lack flexibility.

If you can’t find the final output that you’re looking for in a Pivot table, then you’ll need to use LOOKUP functions to solve your problem.

Excel Excel for Business Analytics Learning Plan
Data Analysis
Pivot Tables

Contents

My Notes

You can take notes as you view lessons.

Sign in or start a free trial to avail of this feature.

Free Trial

Download our training resources while you learn.

Sign in or start a free trial to avail of this feature.

Free Trial