Excel 2010 Severe Performance Problem

[gwinn7]

We have a machine running Excel 2010 on a Windows 7 Professional 64-bit Intel i7 Processor running about 3GB of RAM.

When my user opens a local native version of .xlsx document it takes over 30seconds to load and when doing some of the most basic alterations to the spreadsheet, the changes take literally minutes to complete.

One example would be selecting a column and unbolding the content of all rows in that column. The spreadsheet has just over 100,000 rows with about 3 formulas. We purchased Excel 2010 in an effort to move beyond the 50,000 row limit of older Excel versions, but we honestly did not expect the performance to be this bad. Has anyone seen any similar problems and are there any workarounds and solutions?

Thanks,
gwinn7

 

[Gruuuu]

This sounds like an issue with the workbook itself.
A few questions:

What is the file size of the workbook in question?
Are there many formulas which could be causing long calculation times?
Are there any links to other files? and if so
Are those files large files? or
Are those files on a network file storage?

 

[gwinn7]

Thank you for responding. Here are your answers...

Workbook file size? 26.1MB
Many Formulas? 2 column unique formulas + subtotals
External File links? No, none.

Even with the subtotals removed, the file is still intolerably slow.

Gary

 

[Gruuuu]

Whoa, 26.1MB is huge. Are there a lot of images embedded in the file?

If not, my suspicion is that all 1 million+ rows have some sort of formatting on them, causing Excel to think they are being used. This suspicion is supported by your statement

selecting a column and unbolding the content of all rows in that column

If so, select all the rows beneath your data, and remove all formatting (background, underline, bold, italics, text color, borders), and then save your file (this is actually necessary to let excel know that those cells aren't in being used anymore).

 

[gwinn7]

PROBLEM RESOLVED!

No, no images in the file, just about 100K of rows and about 30 columns. No, I don't think Excel was fooled into using 1million rows.

RESOLUTION

Increase the system paging file size manually.

EXPLANATION

Apparently, the operating system was not effectively estimating the correct size of the memory paging file. After checking the performance stats, it was not efficiently utilizing the available space and paging much more than the system needed to. So, to best utilize the 4GB of RAM (I know I said 3 before, sorry!), we increased the paging file to 6GB Min to a Max of 8GB. After we rebooted the system and re-opened the workbook, POOF! The perceived performance was a HUGE improvement. Rather than taking minutes, it took under 20 seconds.

Thank you Gruuuu so much for giving this consideration!

Hopefully, other people don't have to pull their hair out for this problem.

Gary
gwinn7