The other day I ran into a workbook containing a circular reference between a pivot table and a regular table. The table based calculations on the pivot table, which were then used in a summarizing calculated field in the pivot table, or something like that. This seemed like a dicey approach, so those fields and formulas are with us no more. But it did get me wondering about pivot table circular references.
Content Advisory: This post contains material that will be useless to most, if not all, viewers. On the other hand, it’s mostly pictures.
Below is one of those interactive embedded workbooks I love so well. It has a dead simple example of a circular reference involving a pivot table and a table. The pivot table is based on the data cell, which in turn has a formula to multiply the pivot cell times two. Right-click somewhere in the pivot and hit “refresh” to see it in action.
Then I wondered if you can take it one step further and base a pivot table on itself. Turns out you can:
So there it sits, a self-referencing pivot table. Sit is about all it does though. You can’t change it:
And if you Refresh it, it disappears, because the original source column name has been overwritten. For example, the “Data” field above became “Sum of Data.”
I fooled around with calculated fields, but Excel doesn’t let you chain those back to themselves. And now I understand of why you can’t give a pivot table’s Value field the same name as its underlying data field. (For example if you remove the “Sum Of ” part Excel squawks at you.) If you could, I think you could make a calculated field that doubled itself.
If anybody comes up with a more interesting example or use of pivot table circular references, please share.