When Excel runs out of steam

[mikecro]

New job - large amounts of data with options to process in Access and/or Excel. Both 2010. Both 32-bit. PowerPivot not an option.

One of my workbooks consistently has issues when is goes over about 2.7 million calculated cells. And about as many data cells and a couple of pivots.

The symptom is Excel saying it is out of resources. If you say continue with no Undo it still fails and you get some screen corruption.

It is less than the 1M row limit but quite a few (365+) columns

Questions:

1. Anyone seen anything similar on 32 bit?
2. Are your results consistent? - between 2.7-3M calculated cells
3. Anything to up that limit that is not already implied (eg 64 bit)

Meanwhile the work on Access continues

Mikecro

 

[NeonRedSharpie]

I think once you reach 3 million calculated cells you need to ask yourself what's the point of all those calculated cells, and why are they being stored in one place. Why not add calculated fields to a pivot table (if they're simple math functions) and that will help with your run time. As it sounds, this is an access/sql project to be sure.