<div>
Hi Ian,<br><br>Your current PC specs are good- i9 with 48GB ram should be more than enough for Excel worksheets.<br>Are you loading the Excel sheets from a network share or local PC?
</div>


<div>
Hi Ian,<br />
<br />
Maybe I misread, but I think that link only applies to 32-bit Excel.<br />
<br />
You indicated that you are running 64-bit Excel, so the limit of 3GB / 4GB should not be a factor for you.<br />
<br />
Alan.
</div>


<div>
Hi Lan,<br><br>It should be done with regedit for expand capacity excel limit. <br>Macros using?&nbsp;
</div>


<div>
If you had crashes that corrupted large excel files, you probably have hardware problem, like bad RAM, failing HD, filesystem problems, not enough disk-space for temporary saves etc. Also overheating etc can cause problems. First I suggest cleaning out any dust from the PC, so that it gets cooled properly. Then run the memory test utility. Check the smart status of your HD. Run a chkdsk /f on all the NTFS partitions to fix any filesystem problems. Do a disk cleanup.
</div>


<div>
An Excel File of 3.2GB is big. Aren't you using Excel as if it was a Database? If this is it, I would strongly consider moving data from Excel to a Database.
</div>


<div>
Amir, &nbsp;I had been doing both storing data in Excel and also getting data from SQL server.<br>Alan, You are right that shows Excel 32 bit. Mine is 64 bit as have used on two different computers Office Pro 2019<br>Joames, Not using any macros. Happy to hear more about expanding via regedit if you have time.<br>Rindi, I have worked with large Excel memory zapping formulas for a couple of decades or more using the latest technology as shown above plus latest Excel versions and upgraded to 64 bit when it was first launched. I have always<br>had slow opening and saving of Excel workbooks and had to ensure my workbooks were less than 32,000 rows and around 75 columns of formulas to ensure w/book wouldn't crash. Even with brand new squeaky clean components.<br>I wondered if 64GB Ram would help ? But somehow I doubt it.&nbsp;
</div>


<div>
Strivoli, I am using SQL as a database and importing only the data to Excel that I want for the regressions.<br>BTW the data shows 3.2GB in the Task manager only but the files are far less in real size maybe around 300 MB. I can't check right now as my computer is being rebuilt..
</div>


<div>
As @strivoli mentioned, best to move this size data to a database, then as you have database problems, open questions about each individual database problem.<br />
<br />
Likely far easier to solve this in a database than a spreadsheet.<br />
<br />
If you must continue to use this size spreadsheet, your machine has sufficient memory, if you're using 64bit Excel.<br />
<br />
With 32bits... you can only address 4G of memory, so your entire Excel code along with the internal representation of your spreadsheet would likely have to fit into 4G.<br />
<br />
If 32bit/4G is problem, what you'd likely see is Excel start loading your spreadsheet... run for a long time... then just die, on first &gt;4G address reference.
</div>


<div>
BTW I am using a service to speed up the formulas and maximise efficiency but still unsatisfactory. Have any of the responders had any experience crunching big numbers using Excel ?
</div>


<div>
Hi David, I only use 64bit Office pro previously 2016 then 2019.<br>There is a big difference in what is stored data and what the size is during the running of a regression<br>I cannot believe Microsoft's claim that it is limitless (64 bit)
</div>


<div>
Have you considered doing the regression in SQL?<br />
<a href="https://mode.com/blog/how-to-do-linear-regression-in-sql/" rel="ugc">https://mode.com/blog/how-to-do-linear-regression-in-sql/</a>
</div>


<div>
Hi Roy, Yes that is one solution I have tried before. It does help.<br>I already split up my data to ensure it doesn't crash. Ideally I would like to work with 1 GB files<br>But I guess it would show up at around 10GB in Task Manager if indeed it would open which<br>I am absolutely certain it wouldn't.<br>If I increased Ram to 128GB would that help ?&nbsp;
</div>


<div>
Is there only data in the workbook?
</div>


<div>
Andy, I have on a couple of occasions but from a quick look it didn't seem very popular.<br>I will have a look at the link and have another go :)<br><br>I'm disappointed that Microsoft make such statements which either are not true or they purposely hide the fact that<br>once data is in memory for calculation that it can cause issues. Such as what I have reported above and which I have experienced for around two decades.
</div>


<div>
Mostly formulas Roy accessing data from either same sheet or another sheet in the same workbook.
</div>


<div>
As I'm in the throes of rebuilding my computer with possibly a new motherboard and testing other components,<br>I wondered if I increased the RAM from 48GB to 128GB would it help to load and save and exit large workbooks quicker ?<br>While greatly reducing the risk of a crash ?<br>Currently my workbooks exceed 220MB and can crash on occasions without warning.<br>I'm taking the view that if Microsoft are correct in saying that using 64bit Excel is limitless then it is subject to the computer resources hence my decision to increase the Ram. Although we said earlier 48GB should be enough. But maybe extra would help.<br>I would appreciate your thoughts.<br>Thanks<br>Ian
</div>


<div>
I have received a new Dell workstation and it hasn't made a blind bit of difference re speed. Still slow loading and calculating large Excel files despite having Dual Processors Xeon Silver, 64GB Ram, 1TB SSD. It still causes the workbook with a size greater than 280MB to crash. Not to mention the continuous "not responding" messages.&nbsp;<blockquote>Excel's claim &nbsp; "64-bit environment imposes no hard limits on file size. Workbook size is limited only by available memory and system resources"............. &nbsp;Are they talking about system memory or Excel memory ? If it is the latter then surely that statement is false otherwise why would a workbook crash ?</blockquote>I am using Windows 10 64Bit and Excel 2019 64 Bit.<br><br>I have just about given up with Excel. They are limited in handling file sizes greater than 200MB.Increasing my system resources was a waste of money and a whole lot of time.<br>I am aware there are tricks to speed things up like using xlsb format but I would have preferred not to change the format.<br>I've spent years trying to solve this Excel speed issue and I am disappointed to say the very least.<br>Thank you guys for putting up with my moans.
</div>


<div>
I wish you could share the workbook or a dummy one so I could look into it for you.
</div>


retired

Ian Bell

<div>
Yes no problem Amir. I can send it via Wetransfer. I would need an email address to send it too.<br>It is too big to post here.
</div>


<div>
ok got it saved. you can delete that post&nbsp;<br>Thanks
</div>


<div>
You're welcome- will test this once I receive it.
</div>


<div>
I've concluded there is no solution to this vexing problem. Excel claim 64bit is limitless and that it depends on resources of machine.<br>Well after buying a workstation with Xeon Silver dual processors last week it does not change things it still maxes out when 5GB is shown in Excel memory in taskbar and is very slow to load and save and exit when using files around 200MB +.<br>Of course I can do stuff to the files like make them smaller and split the number of sheets to two or more workbooks but<br>I didn't want to do this as already I am working with 40 workbooks and that is already too many and to double the size to 80 is out of the question. I can also change the formulas and/or save the files to xlsb but if I do want to go ahead using Excel I may have to do that.<br>From my perspective Excel's claim is not correct and I have to seriously consider moving to another method to run regression. However I did like using the Solver add on.<br>Thanks all for your input and as usual greatly appreciated.<br>Ian<br><br><br>.<br>&nbsp;
</div>


<div>
<div class="content wysiwyg-content fr-view">
Hi,<br>Can someone please enlighten me on the memory capacity for Excel 2019 64bit ?<br>The info on the Excel site says<br>"64-bit environment imposes no hard limits on file size. Workbook size is limited only by available memory and system resources."<br>However if I exceed 3.2GB as shown in Task Manager I run the risk of a crash hence corrupting the open workbook/s.<br>I use an i9 processor and 48GB DDR4 Ram<br>Any help much appreciated on buying a new computer (Preferably Dell) solely for doing regressions using Excel Solver.<br>Many thanks<br>Ian<br><br>edit: &nbsp;I found this link that explains it now doubles 64bit capacity from 2GB to 4 GB.<br><a href="https://docs.microsoft.com/en-US/office/troubleshoot/excel/laa-capability-change" rel="ugc">https://docs.microsoft.com/en-US/office/troubleshoot/excel/laa-capability-change</a><br>Are there any spreadsheets around with much greater capacity?<br><br>
</div>
</div>



Hi,
Can someone please enlighten me on the memory capacity for Excel 2019 64bit ?
The info on the Excel site says
"64-bit environment imposes no hard limits on file size. Workbook size is limited only by available memory and system resources."
However if I exceed 3.2GB as shown in Task Manager I run the risk of a crash hence corrupting the open workbook/s.
I use an i9 processor and 48GB DDR4 Ram
Any help much appreciated on buying a new computer (Preferably Dell) solely for doing regressions using Excel Solver.
Many thanks
Ian
edit:  I found this link that explains it now doubles 64bit capacity from 2GB to 4 GB.
https://docs.microsoft.com/en-US/office/troubleshoot/excel/laa-capability-change
Are there any spreadsheets around with much greater capacity?

Excel memory capacity

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.

Microsoft Excel

Storage devices include any device used for storing and retrieving digital information. Hard disk drives (HDDs) use one or more rigid ("hard") rapidly rotating disks (platters) coated with magnetic material. Data is accessed in a random-access manner, meaning that individual blocks of data can be stored or retrieved in any order and not only sequentially. The primary competing technology for secondary storage is flash memory in the form of solid-state drives (SSDs), but HDDs remain the dominant medium for secondary storage due to advantages in price per bit and per-device recording capacity. CD-RW (Compact Disc-ReWritable) is a digital optical disc storage format that allows information to be stored outside the mechanical HDD.

Storage Hardware

Microsoft Office is an integrated suite of applications that includes Outlook, Word, Excel, Access, PowerPoint, Visio and InfoPath, along with a number of tools to assist in making the individual components work together. Coding within and between the projects is done in Visual Basic for Applications, known as VBA.