• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Excel (2013) out of resources

B

brianmock

Member
Recently switched to Excel 2013 (32-bit) by it department
I now constantly get Excel ran out of resources while attempting to calculate one or more formulas.

In excel 2007 I used the same multiple inter-linking files daily w/o trouble

Using Windows Task Manager: When ever Memory (Private Working Set) for excel goes above 1Gb I start to drag/crash

Have tried to reduce/eliminate volatile formulas... some are necessary --at least boss thinks =today() is necessary rather than a macro to set date each time the file is opened :(

CPU usage does not max out
Running Windows 7 Professional
4 GB ram

Is there some setting to allow Excel to use more memory?
 
Brian

Have you tried processing your file on a PC with 8 or 16GB or more of memory ?

I would close all other applications, especially Web Browsers, they are memory hogs (all of them) before trying it

You may also want to try only using a Single Processor, goto
File,
Options
Advanced
Scroll down to Formulas
Disable Multi-Thread calculations
Ok

Try again
 
If you are using array formulas that refer to whole columns, this may be the issue.
eg. VLOOKUP, INDEX, MATCH ...

Each new version of excel released increased the effective grid size or number of usable cells per worksheet. Formulas that previously would refer to whole rows or columns would involve calculation of tens or hundreds of thousands of cells, now calculate in the multiple millions. This chews up memory resources.

Re-define your arrays to a smaller range and it will help immensely (even using, say, A1:C100000 rather than A:C for example is an immense decrease in required memory).

Have a read here for in-depth explanation and some other tips.
https://support.microsoft.com/en-gb/kb/3070372
 
You must log in or register to reply here.
Back
Top