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.

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


4

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:

  1. Rename the File3 to something else, such as File3Real.
  2. Open File1 in Excel.
  3. Open File2 in Excel.
  4. With File2 displayed, press F12. Excel displays the Save As dialog box.
  5. Save the file using the File3 name. Since File1 is also open, Excel automatically repoints the pivot cache (and hence all the PivotTables in File1) to File3.
  6. Close File3.
  7. Save and Close File1.
  8. Outside of Excel, delete the File3 workbook created in step 5.
  9. Rename the File3Real workbook with the name 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.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Lines that Don't Change When You Type

Create a form in Word and you will invariably be faced with the need to places fill-in-the-blank lines in the document. ...

Discover More

Noting Changes at the Left of the Text

The Track Changes feature allows you to easily see where changes have been made in a document. Resolve those changes, and ...

Discover More

Paragraph Numbers in Headers or Footers

If your documents routinely use numbered paragraphs, you may want to place the number of the page's first paragraph in ...

Discover More

Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!

More ExcelTips (ribbon)

Editing PivotTables without Underlying Data

If you ever try to edit a PivotTable and get an error that tells you that the "underlying data was not included," it can ...

Discover More

Rows in a PivotTable

PivotTables are used to analyze huge amounts of data. The number of rows used in a PivotTable depends on the type of ...

Discover More

Turning Off Automatic Sorting in PivotTables

Tired of your data being sorted when you create a PivotTable? Excel allows you to specify how the sorting should occur by ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] (all 7 characters, in the sequence shown) in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 2 + 8?

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.


This Site

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.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.