Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Pointing PivotTables to Different Data.
Written by Allen Wyatt (last updated July 29, 2022)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365
Joe uses PivotTables a lot at his company. In creating the PivotTables they would use one workbook for the PivotTables that were generated based on source data in a second workbook. In working with the PivotTables they needed a way to quickly "re-point" their pivot cache to a different data source. Unfortunately they found that re-pointing one PivotTable to a different source workbook only created a second pivot cache, thereby causing a much larger PivotTable workbook and a slower response time in Excel. They needed to actually re-point each PivotTable to the new data source before the old pivot cache would disappear, and some of the workbooks had over 50 PivotTables!
They found a solution, however, that allowed them to very easily make the switch over to the new data source. Assume, for the sake of this tip, that File1 is the workbook containing the PivotTables, File2 is the current data source workbook, and File3 is the new data source workbook. Further, all the PivotTables in File1 share the same pivot cache which, in turn, points to the data in File2. These are the steps they took to switch everything over so File1 finally pointed to File3:
That's all there is to it; File3 is now the new data source for the PivotTables in File1. As well, any fields that do not exist in File3 will also be removed from the PivotTable reports when you next open File1.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (8267) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365. You can find a version of this tip for the older menu interface of Excel here: Pointing PivotTables to Different Data.
Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel 2013 Data Analysis and Business Modeling today!
If you expect your PivotCharts to update automatically when you update a PivotTables, you may want to alter, slightly, ...
Discover MoreWhen you create a PivotTable, it can have a name. You may want this name to appear within the PivotTable itself. There is ...
Discover MoreCreate a complex PivotTable and you may find that your system slows to a crawl. The reason for this may be due to the way ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2022-07-29 09:11:51
Joan Koskela
If having the data in a workbook with a generic name is an option, that would also help. To keep the "old" data, just copy the generically named data workbook with a new name (e.g., 'Monthly Data' to '01 Jan Data'). [If you do this as a Save As, be sure the target workbook is not open unless you want to save a version of the target workbook that still opens the old data!] Then save the new data as 'Monthly Data' and the target workbook will pull in the new data.
This process also works for updating links when just pulling data from one workbook to another directly.
2022-07-29 05:33:55
KV
The assumption here is that if the data structure in File3 is different from File2, it won't affect the pivot table layouts in File1 significantly.
That is, if there are fields missing in File3 their absence will not disrupt the multiple pivot tables in File1.
2018-09-04 08:37:08
Nadine Claessens
Hello Nigel, can't you solve this problem bij using a slicer coupled to the different PT? This avoids having to re-point each PT.....
2018-08-29 09:13:04
Nigel Drinkwater
This is really useful. However, I have a similar issue which can't be resolved in the same manner. I have a quarterly report for which I am provided a data file and I use the copy/move menu to copy 5 worksheets with various pivot tables on them to the new data file. I then have to re-point each pivot table to the data worksheet in the new file. I store the pivots and the data together as I often need to refer back to them and don't fancy doubling the number of files by having a Pivot file and a Data file for each quarter.
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2023 Sharon Parq Associates, Inc.
Please Note:
This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here:
Comments