Ed has attended:
Excel VBA Introduction course
Excel VBA Intermediate course
Excel Advanced course
Access Introduction course
Effective Communication Skills course
Pivot Table won't refresh - not enough resources
Hi,
I've built a pivot table off a query table that is refreshed based on a given date range. There are 4 columns in the table. A refresh will bring in around 300k rows per year.
For a date range from 31/12/2006 - 31/12/2009, this returns around 900k lines of data and the pivot table can be refreshed with no issues. This is the case for any date range after around 2005.
The issue arises if I choose a date range pre 2005 - say 31/12/1999 - 31/12/2001. This returns much less data - more like 350k rows so I would expect the pivot table to refresh. It doesn't and instead I get an error saying there isn't enough resource. After some testing it seems the max number of rows that will pivot for this date range is around 200k.
Do you have any ideas as to what could be causing this difference in behavior?
Thanks,
Ed
RE: Pivot Table won't refresh - not enough resources
Hi Ed,
Thank you for the forum question.
When we create pivot tables Excel creates pivot cache. Excel takes all the records and put them in the memory. In the pivot cache Excel can store 1048576 records. Excel do not empty the pivot cache before you delete the pivot table. You have more records in the source than you can have in a normal pivot table cache.
You can use Power Pivot to create your pivot table. Power Pivot has no limit of records.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
