Extremely slow response times in Excel 2007
"Shantanu_Das" wrote:
Hi, I have been using Excel for the past 8 years. While I like all the new
capabilities of Excel 2007, I am very frustrated at the extremely slow
response times when it comes to analyzing or simply manipulating large data
sets.
For example, I have a 130 mb file with 5 worksheets. One of them (Rawdata
sheet) has around 300,000 rows with 25 columns of data and around 300,000 IF
statements - the other sheets are more calculation based spreadsheets with a
few sums, one pivot table, around 17,000 vlookups and 5 indirect functions.
There is a macro that runs to prepare the data in a certain format which is
then plugged into 2 graphs. Now, anything I need to do with the Rawdata sheet
takes inordinately long - for example - if I need to delete data from 1000
rows, and I just select these rows and press 'delete' - it takes around 5
minutes. If I do the same operation on say, 5000 rows, it takes more than 2
hours. I have tried deleting entire columns / rows and same thing happens.
Even scrolling through cells (sideways or up and down) takes more time as the
workbook stays 'open' longer. My question is, why does it take so long to do
simple operations such as Delete, scroll and Paste special etc? Saving /
opening takes very long too - but I can live with those.
My laptop has 2.5 gb RAM and its a 2GHz Thinkpad T60. My calculation is set
to manual and my autosave feature is also turned off. It is also saved as
.xlsm. I have read a lot of posts around how to improve macro performance and
how to avoid certain types of calculation etc. - but just general
manipulation of data in a large file seems extremely unwieldy in Excel 2007.
I have to spend more time than I can afford to just do simple data
manipulation once the file becomes large. Anyone else facing similar issues
out there? Any more ideas on how to speed up general performance of Excel
2007 - whats the use of all the new space on a spreadsheet and all the new
features if we get bogged down by terrible performance?
|