?
Solved

Microsoft Excel Memory Issues  - Cannot complete this task with available resources

Posted on 2012-09-06
4
Medium Priority
?
490 Views
Last Modified: 2013-09-03
I am running Windows 7 Enterprise 64-bit with MS Office 2010 32-bit, fully patched.

I have an analytics user with an Excel file that contains a single table which is 22,000 rows x 617 columns.  No vlookups or other formulas - just data (text and numbers).  No pivot tables or charts.  The file is ~25MB in size.

The user is attempting what seems like simple tasks - delete a column, add a column, delete filtered rows, etc.  In all cases, Excel refuses with "Excel cannot complete this task with available resources.  Choose less data or close other applications.

The hardware is robust - 8GB RAM and Core i7 processor.

Task manager is showing only 31% of physical memory in use, and only ~250MB allocated to EXCEL.EXE *32 process.

My questions:
1) why is Excel not accessing more of the roughly 5GB of available physical memory in order to complete these tasks?
2) if Excel 32-bit is limited to only addressing 2GB, why does it not expand to at least this size to accommodate this request?
3) likewise, if Excel is currently utilizing 250MB of memory, why is an operation on a 25MB file causing it to max out resources?
4) finally, what can be done?


Thank you!
0
Comment
Question by:Pgh_Habitat
2 Comments
 
LVL 24

Expert Comment

by:Steve
ID: 38374200
Try not to add / remove full columns / rows.

In 2007+ there are far far more rows to shift about and this can cause problems.
Try using [ctrl]+[shift]+[left/right/up/down arrows] to select rows of data and insert / delete rows / columns of data only, not full rows/columns.
0
 

Accepted Solution

by:
Pgh_Habitat earned 0 total points
ID: 38447622
I upgraded to Office 2007 x64 (including Excel), and this resolved my issues.  Excel is now accessing 1.5 GB of RAM when necessary to handle these operations.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question