• Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint
Share this Page URL
Help

4. Hacking PivotTables > 49. Efficiently Pivot Another Workbook’s Data

Efficiently Pivot Another Workbook’s Data

Use data residing in another workbook as the source for your PivotTable.

When creating a PivotTable in Excel, you have lots of options for your data source. By far the easiest and most powerful approach is to use data that resides within the same workbook. Unfortunately, for whatever reason, this is not always possible or feasible. Perhaps the data that resides in another workbook is entered daily, for instance, and the users entering the data should not see the PivotTable.

Using a dynamic named range will greatly decrease the refresh time needed for your PivotTable to update. As you cannot reference a dynamic named range from another workbook, this also means you prevented the PivotTable from referencing perhaps thousands of blank rows and causing the file size to increase substantially. This way, you can pull in data from another workbook, and then base your PivotTable on the data in the same workbook rather than referencing it externally.


PREVIEW

                                                                          

Not a subscriber?

Start A Free Trial


  
  • Creative Edge
  • Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint