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

[McMasher]

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,
Rob

 

[McMasher]

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.