How much RAM do you need for large spreadsheets?

[NH12345]

Hi, lately I've had to work with some huge spreadsheets, usually about 200,000-300,000 rows and 300 or 400 columns. Many times when I have to run formulas, even a simple VLOOKUP or COUNTIF, the program freezes and sometimes crashes and I sometimes get an out of memory error message. Would getting a computer with more RAM solve this problem? If so, how much RAM do you need to calculate through huge spreadsheets like this? Or does it have more to do with the CPU or other factors? I have tried some other things like saving the workbook as an xlsb file, but nothing really seems to help. Right now I've resorted to breaking the file up into several smaller files to run formulas, but it's a real pain, and it still takes a long time. I've tried working with these files on a computer with 12 GB of RAM and another with 16 GB of RAM, and that doesn't seem to be enough.

Thanks in advance for any information.

 

[ACoolDude]

Open Windows task manager while your file is open. On the Processes tab, if Excel is near 2GB of memory, then your best best is the 64Bit version of Excel. Excel 32Bit can only handle 2GB no matter how much RAM the PC has

 

[ACoolDude]

Also, you may want to look at optimizing your lookups


http://www.exceluser.com/blog/727/excels-fastest-lookup-methods-the-tested-results.html

has some good information on that. Sorting your source data can mean 1000X faster results.

 

[NH12345]

Thank you very much for the link and the other information. If I were to get the 64 bit version of Excel, would 12 or 16 GB of RAM then be enough to work with really large spreadsheets? Or would I need even more?

 

[Mathman]

I haven't noticed a huge increase in speed with more RAM. In fact I was surprised to see how little excel really needed. I have workbooks that are 100,000KB plus in size, I tested the RAM use by increasing the file sizes all the way up to over 1,000,000KB and at best I didn't even push 8Gigs.


last year I spent a huge amount of time trying to speed up some files that would take 6-8hrs to run a macro and populate on an i5 @2.6Ghz. The first thing I did was drop some money on a new computer hoping this would help. I got a i7-7700K overclocked it to just shy of 5Ghz with 32 gigs of ram and updated to excel 64bit. The result was that rather that 6-8hrs to populate it took 2.5hrs. That seems like a great improvement but I was pretty disappointed with it.


After looking around about optimizing excel sheets I stumbled onto a program called Fast Excel by Charles Williams. If you have no idea who this is look it up, well worth it. I bought his software and what a humbling experience. After the initial learning curve I started to dissect every part of my sheets and again these are massive sheets with tens of millions of various formulaes. I must have spent 4-5 months reworking everything but in the end I got my sheet to run from 2.5hrs down to 21seconds and shrunk the file size in half.


Before you spend money on a new computer buy the software and start working out your sheet to optimize it. from what you said odds are it's the sheet that needs to be fixed better. Such things as type of formulaes, unnecessary formulaes that can be re-arranged and also the dependency tree to temporary folders building up to the point it slows things down to even unused formats ect.


I would say that once you know you pushed reorganising your workbook then look at a potential upgrade. Unfortunately no amount of RAM can make up for a weak designed sheet.


Hope this helps. ​

MM

 

[NH12345]

Thank you very much for the information. The set up of the spreadsheets and some of the formulas I'm using might very well be a large part of the problem. I looked up Fast Excel and it looks really good. I might give it a try.

 

[Fazza]

Before you spend money on a new computer buy the software and start working out your sheet to optimize it. from what you said odds are it's the sheet that needs to be fixed better. Such things as type of formulaes, unnecessary formulaes that can be re-arranged and also the dependency tree to temporary folders building up to the point it slows things down to even unused formats ect.

I would say that once you know you pushed reorganising your workbook then look at a potential upgrade. Unfortunately no amount of RAM can make up for a weak designed sheet.
​

​

Absolutely. There are endless examples on the forum of, as you say, weakly designed spreadsheets. Well done on the tremendous gains you've made on your files.

 

[ttohu11]

Not true all.... big excels can consume large ram and it helps to have a 16GB machine with the 64 bit excel.
see my ramusage with a 250k+ rows spreadsheet.