What is the Most Resource Efficient way to LINK a pivottable to a data set?


Board Regular
Mar 8, 2013
The problem: I have a very large Table in excel (795,000 lines across 100 columns). I have linked a pivot table in a separate workbook to this data set in three different ways and I am unsure what the "best" way is. The best way would be a way that allows the pivot table to filter quickly while also taking up as little computer resources as possible as I have some members on my team working with 4GB of RAM and a 32 bit computer.

The three ways I have tried are:
  1. insert a pivot table within the same workbook as the data set, then move it to a new workbook.
  2. define a name in a new workbook that links to the data table in a separate workbook.
  3. insert a pivot table in a new workbook and choose the "use an external data source" option and select the data table that is in another workbook.

I have reformatted this massive data set into a report dashboard for my sales team in which they can utilize slicers to filter through the data and create the specific reporting that they need.

I am certified in Microsoft Access and if my entire team had MS Access I would utilize that but I am one of three people that has it and my boss has been burned by unfinished Access projects in prior jobs so she won't let me run wild with it.

I don't yet have a full understanding of Crystal Reports however that would be my end goal and where I want to end up with the reporting, but at the time being Excel is what I have.

Any help is greatly Appreciated,


Board Regular
Mar 8, 2013
Just an FYI the way I decided to go is option 1.

The only limitation to this is if you change the name of the root workbook in which the data resides, you have to make sure your dashboard is open at the same time.

My dashboard has a separate sheet for all of my slicers, and multiple pivot tables all working off of one pivot cache. and all of this is linked to a table object within the root workbook.

Forum statistics

Latest member

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...