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

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

____________

You may also like...

8 Responses

  1. Ezequiel says:

    Hi, Note tha this only works if the Pivot table and the database referenced by the Name, are in the same sheet.

    Regards!

  2. Ezequiel says:

    May I retract on what I’ve said? It finally worked after taking a look to your code… My apologizes.

    Kind regards, Ezequiel

  3. Will says:

    Also note that it doesn’t work unless you delete all your slicers.

  4. Alexandre Meunier says:

    How could it work without deleting slicers?

  5. Shun Ma says:

    Found this solution to be very helpful for a workbook that contains 35 worksheets and 105 pivot table all drawn from the same data set (lots different views of the data needed). However, I seem to have run across some limitation either in Excel or possibly insufficient internal memory (2Gb currently). When I attempt to use the macro on the original file, only 72 of the 105 pivot tables get updated, the remaining pivot tables have the original data source. It also causes the display to behave unusually and I am unable to save the file.

    As an experiment I split the workbook, so that about have the pivot tables was in part 1 and the other half in part 2. Both halves contained the data set. When I applied the macro to the split workbooks, all the pivot tables are updated and I don’t have the problem with the display or saving the file.

    The original file had slicers for some of the pivot tables, but I removed those before first attempting to apply the macro to the entire file.

  6. Theo says:

    Works perfectly!!!

    Would really appreciate having comments for each major step explaining what the code is actually doing.

    Many thanks anyhow!

  7. Josh says:

    This doesn’t work for me. The window pops up and the new sheet is in the background, but there are no listed connections. All my connection are to a sql database if that matters. For example 10.xx.xx.xx Gravic TempReporting

  8. Snay says:

    New to macros, trying to make this work for my file. My data source is another workbook…how do i make this work where the data source is external?

Leave a Reply to Shun Ma Cancel reply

Your email address will not be published. Required fields are marked *