Forum Discussion
Pivot table does not autofill formula-only copies the value
JEP23 When you reference a cell inside a pivot table, Excel creates a formula with GETPIVOTDATA and hard-codes some of the references as text. Not very smart perhaps, but that's how it is. The picture below is an example of what happens and how you can fix it.
In I3 I entered = and then clicked on G3, and then copied it down. Note that it returns a formula saying to take the value from the "ref" column from the pivot table that starts in $F$2 where the row label (in this example called "list" equals "a". So you get 5 everywhere.
Change the last bit to reference cell F3 (which contains "a") makes it a bit more dynamic. Drag it down (J2 and below) and the number change correctly.
Obviously, you can avoid all of this if you reference G3 directly (i.e. =G3 in I3), but sometimes it's handy to use the GETPIVOTDATA function.
- JEP23Feb 15, 2024Copper Contributor
Thank you so much for explaining this Riny!
I'm still getting the hang of Pivot Tables and the complexities of Excel. I appreciate your time and detailed response.