Excel cannot complete the task with available resources. Choose less data or close applications.

Hi everybody - a lot to address here!  :)

First - the memory background.  Excel has a memory limitation of 2GB.  This is not Microsoft or Office only, but all 32-bit applications made by anyone.  So having a 4GB, 8GB, 20GB, however big machine doesn't really matter.  32-bit Excel can only use 2GB of it.  However, starting with Office 2010, you can now get these applications in 64-bit which alllows Excel to use all of the memory available on the machine.  Most users won't need that much memory, but if you're consistently running into the above error it would be something to look at.  (P.S.  Processor speed isn't part of the equation.)

Second - the error message.  Excel is throwing the message, but it may or may not be Excel using all the memory.  As the message states, closing other applications is a possible solution.  Another resolution may be to not referencing entire columns in formulas or defined names (a best practice is to NEVER reference entire columns, especially in the larger grid introduced in 2007).

Third - the resolution.  If it were me constantly or randomly getting the error, I'd take a look at the memory allocation when it happens.  You could try Task Manager, but it only shows the memory being used, Excel (and other apps) "reserve" memory as well.  This is not shown in Task Manager, but can be seen using a different tool called Process Explorer.  It's a free download from Microsoft, and can be found here:

http://technet.microsoft.com/en-us/sysinternals/bb896653

Hope this helps!

So how do you explain that this happens with a 64-bit machine with 16GB of RAM and only a couple apps open and only three very small Excel files open. Oh and I can just simply start another instance of Excel (while the other stuff including previous Excel files are still open) and that same file will open fine? I am pretty sure it could be a memory allocation thing. Where Windows or Office only says of available memory Excel you are allowed only X amount AND to coincide with that a memory leak (non-garbage collection)issue is also in play. So that as I open and close and open and close and open and close my excel files I get closer and closer to the artifical limit then pop goes the error. Now that is a theory I can get behind!!

Based on the amount of data you are talking about here I question whether XL is really the right application to use. Access or some other database app might be a better choice. Here is a link on XL memory and processing...

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

 

This happens with the tiniest of Excel files, so it has nothing to do with the size. And you can open another instance of Excel and open the same file that failed just fine. It is a bug through and through.

Many thanks i have been looking for a work around and this works very well