Formulas with pivot table data

Wait! Don’t run away! I know those terms—formulas with pivot table data—can seem intimidating to some, but hang in there a moment while we cover what we’re really doing there.

Let’s begin with a simple formula: 2+2=4. If this were in Excel, we might have 2 entered in column A, row 1 and also in column B row 1. So, if we type an = sign in column C, row 1, then click on A1, type a + sign, click on B1 and press Enter, we’ll get 4. The formula we would have just created would be =A1+B1.

While what we create here might seem a little more complicated, it’s really not.

Now, pivot tables. Don’t be afraid of the term. It’s just a custom table writer that quickly sums up or aggregates the numbers we ask it to based on some criteria. So, for example, total up all the purchases by customer type for a particular account type. Beginning with a well-built list (one row per record, data arranged in columns with column titles, no blank columns, no completely blank rows), we click in one cell with data in it.

Then, on the Insert tab, click on the PivotTable button. It’s the first one on the left. Now, just pick the columns (fields) you need and use the layout area to put things where you want them on the report.

While it’s true that there is a lot more to both formulas and pivot tables, this will suffice to cover our topic: formulas with pivot table data.

Pivot table references in formulas

When we look at the pivot table created by these selections, we notice that we get totals for each Customer Type at the end of the row.

If we wanted to create the total of customer types CONTRACT and MONTHLY, using our 2+2 example above, we might click into C11 and type an = sign, then click in C5, type a +, then click in C7. But, we’d likely get this instead of =C5+C7:

=GETPIVOTDATA(“Purchases”, $A$3,“CustType”,“CONTRACT”)
+GETPIVOTDATA(“Purchases”,
$A$3,“Cust Type”,“MONTHLY”)

Wow! Right? So what does all that mean? Well, like a graph, the number in C5 is an aggregate total of Purchases for the MONTHLY customer type and whatever Account Type we selected in the pivot table. Let’s look at that reference:

“Purchases”,$A$3,“CustType”,
“CONTRACT”

If we consider that A3 or $A$3 contains all the information about this pivot table, then this makes sense. The good news (if we can get past the size of the data reference) is that even if we sort this pivot table in another way, we will still get that result. This comes in handy when you want to use these GetPivotData references on other worksheets.

If you want to see another Customer Type, you could just easily change the formula. So, for example, if I have =GETPIVOTDATA(“Purchases”,$A$3,“CustType”,“CONTRACT”) with a value of $73,791, then changed “CONTRACT” to “LOC-OND”, I’d get $64,357. I would not need to go back and click on that cell in the pivot table; instead, I just change the text in the statement.

Whether you’re thinking of using this for dashboards or to create factors for calculations on other worksheets, it pays to get past the size of the GetPivotData references to get durable formulas.

If you decide that you just wish pivot table references behaved like regular cell references, they can! On the Analyze (or Options in Excel 2010) Pivot Table Tools contextual tab, on the left look for the Options button, click the small dropdown arrow next to this button and uncheck Generate GetPivotData.