So, I filled in several rows and columns with data, created a pivot table out of that. I then went, added more rows of data (no new columns), and then tried to refresh the pivot table and it did not account for the new data. I went back to the original sheet, and when I go to Data → Pivot Table → Insert or Edit, it automatically selects the original cells, excluding the new ones. How can I remedy this dynamically? I know I can just extend my selection down by several hundred rows and create a table off of that, but that doesn’t seem like the best method. And, eventually I’ll probably run out of blank space anyhow.
Insert new rows This updates references in formulas, named ranges, database ranges, charts, conditional formatting, validation and pivot tables.
2 Likes
Right-click on your pivot table and select Properties.
Expand the Source and Destination to see Source > Selection type in the end row number instead of the previous one. In image below enter 36 to replace the 21
there are a million rows and 16 thousand columns before you have to think about expanding the number using experimental options
1 Like
THANK YOU! This is what I was hoping for. But yeah, I know running out of rows in the entire sheet is not a concern for the near future, I meant manually dragging the selection down to add more space to my selection. Dragging down several thousand rows, while a functional solution, didn’t strike me as the best one.
The PivotTable dialog follows the non-sensical MS-habit to hide next to everything useful.
Show the additional information (Options, Source and Destination), and you can enter the actual range you want to be evaluated. Entered there it will not be collapsed automatically.
You can do this editing the PT properties.
In many cases an absolute named range may be a good choice.
Also named ranges update on the inserttion of rows inside their row-range (see @Villeroy 's suggestion).
1 Like
That is because MS converts, especially Excel users, are overwhelmed by too much adequate information and specifically want things simple. Even if that means to complicate things.
1 Like