Excel limits - Not enough memory

Hello -
I am using an Excel file that is 80M and it is making my system run very slow.  After thinking for a long time about updating a pivot table, it throws a message "not enough memory".  Any advice about how I can work with this file?  Would it be better to save it on an external hard drive and try to use it that way?  
Thank you,
-Becky
rmk6dukeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

shijupaul1980Commented:
Try to increase the amount of data your pc can recieve at a time by adding the following in the Windows registry:
Start > Run > regedit > OK
Hkey_local_machine\system\currentcontrolset\services\vxd\mstcp
Right click on mstcp > add > string value > DefaultRcvWindow
Rightclick on the new string > modify > edit string > 11040
Restart the pc.

Hope this helps

Check the stuffs in your temp folders (e.g. C:\Documents and Settings\user\Local Settings\temp
Clear the unwanted tmp, emf files etc. They could be the cause.
0
kenwagersCommented:
I'd suggest downloading MS SQL Server Express (free), and importing the data from the Excel spreadsheet into SQL.  You'll be able to process the data more efficiently there.
0
werafaCommented:
you could also copy the file, copy and pastespecial/values the data you are drawing the pivot table from, and then delete any unnecessary sheets.

refresh the data by copy and paste from your working sheet into the pivot sheet.

you could also play with your virtual memory settings
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello Becky,

putting the file on an external drive will not solve the problem, since it's not the hard drive that's running out of memory but your computer's RAM. Either increase the RAM or reduce the file size and/or the formulas that your are using. Some formulas can be very inefficient and use a lot of memory. Re-engineering the workbook for more efficient formulas can often be a big improvement.

Read here about some tips: http://msdn.microsoft.com/en-us/library/aa730921.aspx

Although the article is written for Excel 2007, a lot of the general principles about volatile and array/Sumproduct formulas also apply to earlier versions.

Good luck

0
rmk6dukeAuthor Commented:
Thank you to all four of you who responded so quickly!!

shijupaul1980: I really like your ideas.  I tried to execute them, but it appears my computer is set-up differently.  I got stuck when I tried to navigate to: Hkey_local_machine\system\currentcontrolset\services\vxd\mstcp ... There is no \services\ folder inside currentcontrolset.  Also, C:\Documents and Settings\user\Local Settings\temp doesn't exist (actually Documents and Settings doesn't exist on my computer)

kenwagers: Good idea.  I should look for tools with more processing capacity.  I appreciate you pointing me to a free tool.  I am not familiar with SQL yet - though I think I'll need to learn - so I didn't brave the conversion (yet! I am sure that I will someday soon)

werafa: You're the hero!  This was the quick solution that allowed me to move forward.  It's a great (and seemingly obvious - but I didn't think of it) idea to copy and paste as values.  I did that and the file shrunk from 80MB to 30MB and my computer is once again able to process it.  

teylyn: First, congratulations on all the cases you've been solving lately! I am not at all surprised to see your user name shoot up there on the high-scorers board - you have definitely helped and taught me.  After reading the material at your suggested link, I realize (once again) what kenwagers also has pointed out...  I should look into a tool with more processing capability - be it Excel 2007, MS SQL, or another.  In addition to this realization, though, this material has helped me learn some additional tricks for improving performance.  Thank you!  
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.