Pivot Table Circular References

Pivot Table Circular References

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:

Create self referencing pivot 1

Create self referencing pivot 2

Create self referencing pivot 3

Create self referencing pivot 4

So there it sits, a self-referencing pivot table. Sit is about all it does though. You can’t change it:

alt=

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.

3 thoughts on “Pivot Table Circular References

  1. Pingback: Excel Roundup 20140519 | Contextures Blog

  2. I just realised I had done something horrible. I unintentionally created a series of circular links between 3 tables and excel didn’t detect it as an error because there was a pivot table with a report filter in between.
    After days of working with the file and multiple versions of it, excel started corrupting the data today and this, on only two lines. One in which some kind of multiplication has occurred (a number in the hundred of billions suddenly appeared), the other showed a #ref error.
    I really wonder why the rest of the data is not affected.

Speak Your Mind

Your email address will not be published. Required fields are marked *

To post code, do this: <code> your vba here </code>