Extremely slow response times in Excel 2007

[Shantanu_Das]

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?

 

[Jan]

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?

 

[Jan]

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?

 

[Jan]

Hi,
Some spreadsheats which I used without problems in Excel 2003 also show
extremely slow response times.
In my company laptop the IT boys created a"bubble"as they call it, which
enables me to use 2003 Excel version next to 2007 version.
It seems to me that condtional formats may cause the problem. But also big
records are treated very slowly. My advice is: use Excel 2003.

 

[Bill Sharpe]

Hi,
Some spreadsheats which I used without problems in Excel 2003 also show
extremely slow response times.
In my company laptop the IT boys created a"bubble"as they call it, which
enables me to use 2003 Excel version next to 2007 version.
It seems to me that condtional formats may cause the problem. But also big
records are treated very slowly. My advice is: use Excel 2003.

You obviously didn't have 300,000 rows in Excel 2003.

I suspect those 300,000 IF statements slow you down considerably.

Either go back to Excel 2003 with its 64k row limit or figure out a way
to reduce the amount of data in your spreadsheet to a more manageable
number. Is all the data required for each graph? If not, separate the
data and graphs into separate spreadsheets. What happens if you
eliminate nine out of every ten rows, leaving you with 30,000 instead of
300,000? Your graph itself can be made up of as many points as you like,
but perhaps it's still meaningful if you leave only each tenth point.

Sounds like a problem for Mathcad, not Excel.

Bill