Excel 2010 x64 crashing with ram usage above 50% :(

[imported_Jid]

Before I search out an excel forum I thought I'd ask here. I'm using Excel 2010 x64 and have run into an issue where if my total system ram usage goes above 50% Excel crashes. I had 8GB in my desktop, windows 7 with all it's doo dats generally runs with less than 2GB, with my spreadsheets first opened I'd stay under 4GB but as I worked the usage just grew and grew and when it hit 4GB or exceeded it Excel would lock up at the first large operation and crashed. Out of impatience I simply upgraded to 16GB to avoid this since desktop ram was pretty cheap but it just seems ridiculous that I can only use 50% of my ram.

Now I have a laptop which I'll have to upgrade to 16GB, eventually my spreadsheets will grow and I think I'll hit the 8GB mark at some point in the future and crash again. The lappy can't do 32GB so I'll be stuck if I can't solve this problem. Also, it's silly I can't use most of my ram and Excel crashes.

Help!

 

[lxskllr]

Do you have a page file? I don't know a thing about it, but could it be duplicating your spreadsheet, effectively doubling its size? If you don't have a page file, maybe it's running out of memory.

 

[tweakboy]

As for page file, 4GB and up you disable page file guys.

As for your problem. Do you think there is a difference in x64 excel and 32bit excel, shure RAM limits, but that is bs the OS will use as much RAM as needed.

My advice. Please try Excel 32bit and see if the problem still happens and then get to us. thx gl

 

[postmortemIA]

could be that you have bad RAM stick, and need something to exercise it.

 

[Nothinman]

As for page file, 4GB and up you disable page file guys.

Only if you're an idiot.

As for your problem. Do you think there is a difference in x64 excel and 32bit excel, shure RAM limits, but that is bs the OS will use as much RAM as needed.

My advice. Please try Excel 32bit and see if the problem still happens and then get to us. thx gl

I'm not sure if I'm reading these things that can barely be call sentences properly, but the main difference between the 64-bit and 32-bit Excel will be that the 64-bit binary can use >2G of VM. 32-bit processes are limited to 2G of VM by default and can only be extended to 3G of VM with some Windows settings and setting a bit on the binary itself. The 32-bit version of Excel will either crash or just stop letting you do more once you hit those limits so testing it isn't for high memory usage even an option.

 

[imported_Jid]

Only if you're an idiot.



I'm not sure if I'm reading these things that can barely be call sentences properly, but the main difference between the 64-bit and 32-bit Excel will be that the 64-bit binary can use >2G of VM. 32-bit processes are limited to 2G of VM by default and can only be extended to 3G of VM with some Windows settings and setting a bit on the binary itself. The 32-bit version of Excel will either crash or just stop letting you do more once you hit those limits so testing it isn't for high memory usage even an option.

This is correct, the reason I switched to x64 is because of memory limits with x86. 32-bit version will NOT use more than the 2gigs, or I guess 3 with setting changes which I don't know about.

As far as bad ram I don't think so, originally when I built this system I played with OC and ran a lot of tests including memtest rigorously. I guess it's always possible but I doubt it. I'll know for sure when I get the lappy running, if it's bad ram then I should not have this issue on the laptop. I'll try to increase page file also, maybe there is something to that.

Any other ideas are welcome

 

[xSauronx]

all i can think of is
http://www.pcpro.co.uk/news/enterprise/356173/microsoft-warns-users-off-64-bit-office-2010

even MS originally said shy away from 64 bit editions of office. i didnt know that, initially, and installed the 64 bit edition. got my surprise when i wanted to use sharepoint and office said "piss off, im not working with sharepoint right now" until i downgraded to 32 bit.

hate suggesting it but id uninstall and go 32 bit and see if it has problems for you. are you using documents larger than 2gb where you require 64 bit?

 

[Nothinman]

all i can think of is
http://www.pcpro.co.uk/news/enterprise/356173/microsoft-warns-users-off-64-bit-office-2010

even MS originally said shy away from 64 bit editions of office. i didnt know that, initially, and installed the 64 bit edition. got my surprise when i wanted to use sharepoint and office said "piss off, im not working with sharepoint right now" until i downgraded to 32 bit.

hate suggesting it but id uninstall and go 32 bit and see if it has problems for you. are you using documents larger than 2gb where you require 64 bit?

That does seem like an odd position for MS to take, it's not like they haven't had time to port all of their own apps to 64-bits yet. I had a similar situation with GP which apparently is still 32-bit only and had issues integrating with a 64-bit Office install.

 

[imported_Jid]

all i can think of is
http://www.pcpro.co.uk/news/enterprise/356173/microsoft-warns-users-off-64-bit-office-2010

even MS originally said shy away from 64 bit editions of office. i didnt know that, initially, and installed the 64 bit edition. got my surprise when i wanted to use sharepoint and office said "piss off, im not working with sharepoint right now" until i downgraded to 32 bit.

hate suggesting it but id uninstall and go 32 bit and see if it has problems for you. are you using documents larger than 2gb where you require 64 bit?

Unfortunately I need more than 2gb, many other people seem to be having random memory related problems. Mine aren't random at all, as you continue to use excel it populates ram, mostly it uses memory to allow you to undo operations, since my operations tend to be large it eats up ram fast. In a desperate situation I will try to find this feature and turn it off, another thing I used to do in the past with older Excel is manual save and restart. F'ing Microsoft.

Apparently x64 excel can support up to 8TB of virtual memory.. right.

 

[oynaz]

That does seem like an odd position for MS to take, it's not like they haven't had time to port all of their own apps to 64-bits yet. I had a similar situation with GP which apparently is still 32-bit only and had issues integrating with a 64-bit Office install.

Odd or not, that is indeed MS's position. I doubt we will ever see Office 2010 x64 working properly.

 

[Cerb]

Are the spreadsheets themselves large? There's always a chance your spreadsheet is just getting too big for Excel. If it needs that much memory, I can't help but think it either shouldn't be in Excel anymore, or that maybe you are using operations that require a lot of RAM, and could change to alternatives.

Not unlike this other reply:

your Excel files are too big for Excel, time to move to a database of some sort or break them down smaller or you have some really badly written queries or something in them

It should be able to use more RAM, but when you're thinking about putting 32GB in a laptop, I kind of think that's likely to be a secondary problem, compared to what's making it use all the RAM it tries to use, now.

 

[Nothinman]

Are the spreadsheets themselves large? There's always a chance your spreadsheet is just getting too big for Excel. If it needs that much memory, I can't help but think it either shouldn't be in Excel anymore, or that maybe you are using operations that require a lot of RAM, and could change to alternatives.

Not unlike this other reply:

It should be able to use more RAM, but when you're thinking about putting 32GB in a laptop, I kind of think that's likely to be a secondary problem, compared to what's making it use all the RAM it tries to use, now.

The problem is that MS markets it as a viable tool for analyzing large datasets with things like pivot tables querying data from external databases. And it's not an easy jump to go from Excel to SSRS so most stick with Excel and just deal with how poorly it handles the dataset.

 

[GrumpyMan]

We tried 64bit at the office and found that addons like ASAP Utilities would not work, so we uninstalled and went with 32bit as recommended by MS.

 

[Zxian]

This definitely sounds like a hardware or an installation issue. I have Excel 2010 x64 installed at home and at work and routinely have spreadsheets with 4-6GB of memory usage. My workstation was recently upgraded to 16GB of RAM, but even with the 8GB I had before opening a workbook that used the majority of my RAM was not an issue.

Do you have any third-party addins installed for Excel? Are your workbooks proprietary in nature, or would you be able to share them for others to test on their systems? I'd be more than happy to see if I get the same behaviour on my Excel installations.

 

[Albatross]

Learn mysql,it`s not that complicated.

 

[Zxian]

MySQL isn't that complicated, but it's also much more difficult to audit. At my work, we ensure quality through peer review. Another engineer who isn't working on my project will examine the workbooks I've built to ensure that the calculations are correct. Doing so with SQL queries is far more difficult and time consuming for most scenarios.

 

[xSauronx]

That does seem like an odd position for MS to take, it's not like they haven't had time to port all of their own apps to 64-bits yet. I had a similar situation with GP which apparently is still 32-bit only and had issues integrating with a 64-bit Office install.

yeah, my friends thought it was an obvious issue

server versions are 64 bit
sql is 64 bit
sharepoint is 64 bit

but run all of that together and 64 bit office will NOT work with sharepoint web apps. just wont. really odd.

anyway op, are you getting any particular event viewer errors that have led to anything?

 

[jolancer]

Not to be taken the wrong way, I know you shouldnt need to do this to fix your prob assuming your system specs can handle it. but just for reference cause you never know who knows what.. I do know though the mojority of people who use excel arn't familiar with how resource intensive excel can get if they dont shy away from Volitile functions or arrays and there sheet grows large enough. Avoiding or using alternative formula methods will add alot more data to the sheet(if your not using VBmacros) but also drastically reduces resources used and cycles to process changes.

 

[imported_Jid]

Thanks for the replies guys.

Firstly, no my spreadsheet is not very efficient and should probably not even be in Excel but I learned the program by trial and error for some simple work I thought and things have grown way out of proportion since then. At this point its not realistic for me to switch or change things, in the future and for any other work like this I'm sure I'll approach things differently though.

I don't use addins I just have excel x64 as a standalone app along with word installed. I was hoping it'd be some simple issue to diagnose but reading around now I see it can be any number of random things that could cause memory errors in excel so I think I'll just have to deal with it. I can always break my spreadsheets up, and I'm sure I can disable the feature that stores your actions so that you can use the "undo" function if you mess up, I'd just have to manually save much more often then but that feature eats up ram like crazy. I'll have task manager open on the other monitor and sometimes I copy and paste things across the different worksheets and my ram usage just grows couple hundred megs per click lol.

 

[Nothinman]

Learn mysql,it`s not that complicated.

MySQL and Excel aren't related at all, but thanks for trying.

 

[jolancer]

For anyone who may find this usefull...

http://www.ozgrid.com/News/GoodVsBadDesignSpeedUpEvents.htm

http://www.decisionmodels.com/calcsecretsi.htm

and 'excelisfun' channel on youtube has some great tutorials all the way from basic to advanced which i myself found helpfull a lot in organizing data even though i already learned a lot of the functions on my own.

sacrificing some space for organization can allow you to convert or update functions without harming the data if thats something that apeals to some situations.

and Array formulas ex-(IF=A1:A8>B1:B8,etc,,) [ctrl+shift+Enter]) or aka same as (SUMPRODUCT=xxwatever) are awsome and can do almost anything with, but i learned them to eventually learn to avoid them on large sheets at all cost LoL because they are volitile. theres always a non volitle alternative

 

[Albatross]

MySQL and Excel aren't related at all, but thanks for trying.

Mental leaps are too much for some people.

 

[Nothinman]

Mental leaps are too much for some people.

Giving appropriate suggestions are too much for some people.

MySQL isn't a comparable product and doesn't in any way address his question about Excel crashing. Even if he does move his data to some RDBMS, suggesting MySQL by itself doesn't help with analysis and manipulation of that data, which I'm guessing is what he's doing in Excel. If we're moving on to recommending other software then something like MS SQL with SSRS would make much more sense.

 

[Albatross]

Giving appropriate suggestions are too much for some people.

MySQL isn't a comparable product and doesn't in any way address his question about Excel crashing. Even if he does move his data to some RDBMS, suggesting MySQL by itself doesn't help with analysis and manipulation of that data, which I'm guessing is what he's doing in Excel. If we're moving on to recommending other software then something like MS SQL with SSRS would make much more sense.

Fair enough.

 

[imported_Jid]

Giving appropriate suggestions are too much for some people.

MySQL isn't a comparable product and doesn't in any way address his question about Excel crashing. Even if he does move his data to some RDBMS, suggesting MySQL by itself doesn't help with analysis and manipulation of that data, which I'm guessing is what he's doing in Excel. If we're moving on to recommending other software then something like MS SQL with SSRS would make much more sense.

Once again you are right, it's many times faster for me to analyze and manipulate data when it's visual the way it is in excel. I'm not a professional, it's a hobby I do in my spare time and excel is something I know a little so switching to other software would be difficult right now. In all reality I could do this using much less resources even by putting my data into .txt files and doing the calculations with a simple programming language but it would take much longer and would be even more prone to errors.

Anyways, once I get to my next step with this project I think I will consider other software so I appreciate recommendations. Or in that time an improved version of excel will come out and I'll have no more issues like this. It's amazing how much I can do in excel these days regardless, in the past I had to do certain things in Basic (programming language for kids, but it does the job and is easy )