Forum Discussion
Pivot table does not autofill formula-only copies the value
With a data set that I have in raw form and also in a table, I am able to plug in a formula and then apply the drag and autofill function. However, when I create a pivot table with this data and try the same formula, the drag and autofill function does not work with the pivot table, it merely copies the same number into the rows below. I have checked the box for "Enable fill handle and drag-and-drop", but it still doesn't work with Pivot Tables. Why could it be that the formula drag and autofill does not work in a pivot table, but does work with the raw data or with data in a table, and how can this be remediated if at all with pivot tables? Thanks!
2 Replies
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.
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.
