Is it possible to make the range in a pivot table automatically adjust as new transactions are added?
Normally, removing the row number from the bottom right range in a calculation makes the selection dynamic but this doesn’t seem to work for pivot tables.
e.g. Range = A2:A would reference the entire A column starting at row 2. As rows are added, they are included in the calculation
Is there a way to make ranges used in Google Sheets pivot tables dynamic so that the range being evaluated automatically expands as new transactions are added?
UPDATE: For now, it doesn’t look like a range can be made dynamic in Sheets pivot tables but I temporarily solved the problem by adding 1,000 rows to the Transactions tab and expanding the range manually in the pivot table to include some of the blank rows.
This added a blank row to the pivot table that I filtered out by adding a filter > Filter on Condition > Is Not Empty. This excludes rows that are empty but in the range.
This isn’t a permanent solution. Eventually, I’ll add enough new transactions to fill, and then exceed, the 1,000 blank rows that I added. At that point, I’ll have to add another 1,000 rows and update the range in the pivot table.
Thanks for the reply. For some reason, when I remove the row reference in the range for my pivot table, Sheets doesn’t accept the change.
I just tested it again. From the Transactions tab, I created a new Pivot table (insert >Pivot Table), Range A1:V. (no row reference) ENTER
A new pivot table tab is created with a range of A1:V3130. The row reference is added. Editing the range to A1:V is not accepted. It reverts back to A1:V3130.
I’m using MAC OS 13.0 and Chrome Version 106.0.5249.61.
It’s a mystery. I use dynamic ranges all the time in formulas which I why I was (am) surprised that I can’t in Pivot tables.
I wonder if others in the Community are seeing the same thing and why it would work for you and not for me.
If the range is defined at A1:U, Sheets automatically appends the last row number to the last column reference. In my case, the last row is 3000 so the reference became A1:U3000 which isn’t dynamic.
If I defined the range as A:U, (change A1 to A) Sheets accept this as a dynamic range for the Pivot Table.