Pivot Table - Efficient Source Range

[Greg]

Hello,

I have 10 pivot tables that keying off the same range that has 63 columns
and about 10,000 - 15,000 rows (fluctuates daily).

What is more efficient for the source range in terms of recalculation
(refreshing pivots): having dynamic named range or using some safe range like
$A$1:$BK$16000?

Thank you,

 

[Nick Hodge]

Greg

I've not tested it but I would expect the $A$1 type addressing to be quicker
as the range is just another layer and if it is using
=OFFSET(...COUNTA(....)) type dynamic formula to find the end, it will be
slower still.

To speed it up you could have used the same PivotCache when building pivot
tables on the same data (You should get a dialog to this effect as you build
the tables)

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
web: www.nickhodge.co.uk

UK Excel User Conference In Cambridge
November 28th - December 1st 2007
Register Now @ http://www.exceluserconference.com/UKEUC.html

 

[Greg]

Thank you, i will go w/ a reference range then

in fact, Debra Dalgleish has a nifty code for index cache consolidation.
http://www.contextures.com/xlPivot11.html

I was just trying to get any edge possible.

--
______
Regards,
Greg

Greg

I've not tested it but I would expect the $A$1 type addressing to be quicker
as the range is just another layer and if it is using
=OFFSET(...COUNTA(....)) type dynamic formula to find the end, it will be
slower still.

To speed it up you could have used the same PivotCache when building pivot
tables on the same data (You should get a dialog to this effect as you build
the tables)

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
web: www.nickhodge.co.uk

UK Excel User Conference In Cambridge
November 28th - December 1st 2007
Register Now @ http://www.exceluserconference.com/UKEUC.html