Change Data Source for All Pivot Tables

If you want to change the data source for a single Excel Pivot Table, you can use a command on the Ribbon.

• 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.

____________