Change Data Source for All Pivot Tables

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

datasourcechange02

  • 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

datasourcechange03

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

datasourcechange01

____________