Repeating a pivot table on a monthly basis

Hi Danielle,

Thank you for your question to the forum.

The Best Approach is to use a “Master Pivot File” and Refresh.
Instead of recreating your pivot table each time, you can set it up once and simply refresh it with new data.

Option 1 — Keep One Workbook and Replace the Data

Create your pivot table once using your current dataset.
Keep the raw data in a sheet (e.g. Data).
Do this each month:

Paste/replace the new data into the same sheet with identical column headers and structure.
Go to your pivot table → click anywhere in it → click Refresh.



Option 2 — Turn Your Data into a Table

Select your data → press Ctrl + T (make it an Excel Table).
Build your pivot table from that Table.
Do this each month:

Replace or paste new rows into the table.
Hit Refresh on the pivot.

This ensures your pivot automatically includes new rows and there is no need to adjust ranges

Hope this helps

Kind regards
Martin Sutherland
(IT Trainer)