If you want to change the data source for a single Excel Pivot Table, you can use a command on the Ribbon. If you want to change data source for all pivot tables in a workbook, you can use a macro, instead of making the changes manually.
Change Data Source One Pivot Table
Follow these steps, to change the data source for a single pivot table.
- Select a cell in the pivot table that you want to change
- On the Ribbon, under PivotTable Tools, click the Options tab
- Click the upper part of the Change Data Source command
- When the Change PivotTable Data Source dialog box opens, press the F3 key on the keyboard, to open the Paste Name window.
- Click on the named range that you want to use, and click OK
- Click OK to close the Change PivotTable Data Source dialog box.
Change All the Pivot Tables in the Workbook
If you have several pivot tables in a workbook, and want to change all of them to a new data source, you can use a macro, instead of making the changes manually.
I’ve added a new page on the Contextures website – Excel Pivot Table Data Source – with sample code to update all the pivot tables.
The macro adds a sheet to your active workbook, showing a list of the file’s named ranges. Then, the macro prompts you to enter one of those names, as the new data source for all the pivot tables.