12. Pivot Table Data in Formulas
You might be tempted to insert Pivot Table data into formulas but be careful, this action is has many pitfalls to be wary of.
Don't use Pivot Table data in formulas
- Using Pivot Table cells in a formula is fraught with danger
- The formula can break or change after you make a change to the Pivot Table
- If you can't find the desired output from within a Pivot Table, use lookup functions instead
ALT + A, J: Expand entire field
ALT + A, H: Collapse entire field
SHIFT + →: Select adjacent cell
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.