Excel: how to adjust range in pivot table automatically?

Question

I set up a pivot table and the range is: Sheet!$A$1:$R$1466

When adding new data I have to adjust row number manually. There must be a way to tell excel to do it itself? Thank you.

Answer 1

First, turn your data table into an Excel Table object: Select any cell in the source range, then click Insert > Table or keyboard shortcut Ctrl-T. You will see the marching ants around your data and a prompt whether the first row contains headers. It typically does.

Now, with a cell in the table still selected, insert a pivot table and it will refer to the table by its name, not to the range by its cell addresses.

When table rows are added or removed, you can simply refresh the pivot table. It will still refer to the table by name and include all its rows.

If you want to connect an existing pivot table to the new Excel Table object, edit the pivot table data source and enter the table name after a = sign, like =Table1.

Tables have many more benefits, but one of the best is how nicely they play with pivots.