Pivot Table Data Range

I was able to create a pivot table from Help for my Monthly Spending by Category. I’d like to be able to have the data range automatically update to get new transactions but cannot figure out if this is possible. Is there a way to put a formula in there or tell it to update that sheet and always grab all of the Transactions sheet for the Pivot Table?


If you define the data range with an range like Transactions!B1:F ( notice the column F doesnt have an ending Row value) it will automatically add additional records to the pivot. In google sheets this happens automatically, I think in Excel you have to refresh the Pivot or set the pivot to update upon open.

Once you add this value in, it populates an ending Row that is pretty far away from your data source. For me it was a row value of over 6300 when I have only a little over 1000 rows in my data.
I am not sure if it auto expands if you get close, but if it doesn’t you could always go bac in and redefine the range again.

I hope that answers your question