Excel table (ListObject) row limit & RAM usage

[Carl Colijn]

Hi all,

(bit of a cross-post here with social.technet.microsoft.com and answers.microsoft.com since I'm getting desperate)

Using Excel 2010 32-bit here: does anyone know if there are troubles to be expected when filling Excel tables (the ListObject kind) with a big number of calculated columns and a big number of rows? Like about 70000 rows and 136 column, of which 44 contain data and the other 92 contain somewhat complex formulas? The resulting .xlsb is 72MB in size; not exactly too large for Excel.

This leads to Excel 2010 to not being able to open these files anymore once you save and close them. It tries to open them, but then (after a long while) tells the workbook contains unreadable content, tries to repair it (up to attempt #3), and often finally crashes doing so.

I've also ran into this problem about a year ago (see my StackOverflow post), and have then managed it by reducing my formula complexity; the form then worked OK up to 40000-ish rows, which was still unsatisfying but adequate. This time round however, the table needs to house more calculated columns while the row count also needs to go up.

Some observations:

• I've already ruled out that the workbook I start with is corrupt (I've re-created it a few times again from scratch already before).
• If you reduce the number of (calculated) columns or rows, the problem disappears.
• If you first completely close Excel and then open these big files directly from Explorer, Excel manages to open files with a somewhat bigger number of rows.
• If I create a 50000 row .xlsb file and make a copy of it in Explorer (thus identical files), opening the first one goes fine, but subsequently opening the copy in the same Excel gives the same problem. Opening the second one in a separate 2nd Excel process works OK (as expected).
• Files saved as .xlsb can have about 2x more rows in them before Excel croaks.
• When saving a working .xlsb version that is near it's row limit as an .xlsx, the generated .xlsx will be considered broken by Excel.

My conclusion is that Excel reaches an internal memory limit. It seems like Excel's loading routines just run out of memory converting the file into it's internal in-memory structure or such, and give up in the process, giving Excel the impression that the file is truncated or such. And .xlsx's obviously need more memory to decode than .xlsb's.

Doing some memory stress tests on my 8GB RAM PC, I see that while loading these files directly from Explorer with no Excel open, Excel's RAM usage steadily goes up, plateaus at a max, and then drops 20% again when loading is done. Some stats:

• 30000 rows: max 530MB, finally 420MB
• 50000 rows: max 890MB, finally 680MB
• 70000 rows: max 1200MB, finally 950MB

When loading a second 50000 row file while a first one is already open, Excel's ram usage goes up from 680MB to 970MB when Excel gives up. I.e. well below the 1200MB max while loading a 70000 row file, which works on my PC. And since I have the full 4GB ram limit available for 32-bit processes, you'd expect that Excel wouldn't just give up at about 1GB of ram usage...?

The problem is that this particular file needs to be processed by another Excel workbook, which splits the data in it up into several smaller files based on certain criteria. While opening the file directly from Explorer works, this is irrelevant once the file needs to be loaded by Excel from another workbook. I'm hesitant to rewrite the processing code to use a second Excel instance for the loading, since a lot of data inspection is needed in the process, and since the process is already painfully slow I do not want to add inter-process communication to further worsen it.

The nasty thing is also that I'm stuck with Excel 2010 32 bits with (I think) 4GB of RAM, since that is the type of hardware and software this solution needs to run on; my client can't just upgrade all his relevant PC's and Office installs for this process alone...

Any insight is really welcome! As well as a bug report to the Excel dev team

 

[JLGWhiz]

Hi all,

(bit of a cross-post here with social.technet.microsoft.com and answers.microsoft.com since I'm getting desperate)

Using Excel 2010 32-bit here: does anyone know if there are troubles to be expected when filling Excel tables (the ListObject kind) with a big number of calculated columns and a big number of rows? Like about 70000 rows and 136 column, of which 44 contain data and the other 92 contain somewhat complex formulas? The resulting .xlsb is 72MB in size; not exactly too large for Excel.

If it is not anwered here

https://support.office.com/en-us/ar...d-limits-CA36E2DC-1F09-4620-B726-67C00B05040F

I don't know where else to look.

 

[Carl Colijn]

Hi JLGWhiz,

Thanks for the pointer to that documentation - the last point on that page might be the most likely explanation of what's going on. The 32-bit version of Excel seems to only have an upper usable memory limit of 2GB, which is shared between Excel itself, all add-ins and the workbooks it has open. I.e. it can't even use the normal 3-4GB available to 32-bit applications. However, my tests show that Excel at it's peak uses somewhat more than 1GB and then already gives up, i.e. well below this limit...

I think the point about the data model taking 500-700MB of space is moot here; I'm using Excel 2010 and I believe data models are only available since Excel 2013?

Testing on a 64-bit version (which has no memory constraints like this) might shed some more light on this situation, but I'll have to see where/when I can arrange such a testing environment. And then it's likely that that isn't a route we could use anyway, since the entire installed base uses 32-bits Office, which is unlikely to change, and the processing tool I wrote to handle the sheet makes extensive use of VBA with calls to the Windows API (fixable) and external COM objects (probably irreplacable, thus unfixable)...

Thanks for the pointer though! At least I might have a clue now on what might be going on.

 

[RoryA]

Does it have to be a Table? My experience of large tables is that they are really slow and inefficient.

 

[Carl Colijn]

Hi Rory,

Considering the intended target audience, absolutely yes

The structured referencing tables provide, and the consistent column formula expansion, make it an ideal set-up for somewhat lesser Excel-versed people. I've had it happen too many times before that returned forms had their formulas applied inconsistently, or that mistakes were made due to A1 style references becoming unreadable/untraceable when more than 100 complexly interlinked columns are involved. Tables solve all of this nicely, plus they give me more fool-proof auto-expand lookup and summing functionality and easier banded row-based styling.

But if it's just too much of a performance drain and/or too sensitive when filled with large data sets, then I'm indeed starting to fear I'll have to give this up again. I'm however first considering and cataloging the impact a 64-bit conversion would give, since that might solve all issues (apart from the performance part, but that only raises it's head some of the time, since the table is split/sent/combined frequently and is most of the time quite small in size).

 

[Carl Colijn]

As a further follow-up on this: I just did a test with a converted ('un-tabled') version of the 50000-row file, and that one I can load 2 file copies of simultaneously; it's only at loading the 3rd that Excel again starts to complain about unreadable content and corruption (and at least Excel doesn't then crash, but can actually tell me something before giving up - another improvement). Not that that helps me anything, since all processing code relies on the data source being a table... Which again answers your earlier question: yes, tables make it far easier to handle your data, even in VBA (a simple for each over a table's rows and columns is quite nice, coupled with enlarging a 1-row sized 'template' table over a pre-filled data range to add all needed formulas to it in one consistent go).

 

[Carl Colijn]

And the final follow-up: testing it all on 64-bits Excel seemed to work; a few rewrites to some core parts of the code that processes the file to be 64-bit compliant and we're back in business Apart from the lag the extra overhead of tables gives, but since the largest of data files are only sporadically encountered, it is worth it to keep them for their added benefits.



Memory limitations were thus indeed the culprit. Irregardless of the Excel process seemingly having heaps of memory left (1.2GB max in use out of 2GB available), memory fragmentation and other factors as another reply in a crosspost of this thread hinted at probably made the situation worse enough to exhibit these symptoms.

Thanks to all for the ideas!