Excel is working slowly when I have several tabs with 20,000+rows---- any way to speed it up?

[Mike C]

Hello - I am having a consistent problem when I build a series of
spreadsheets that contain many rows (usually about 20-50,000 rows in
several spreadsheets), and contain several "if formulas", and contain
several Vlookup formulas.

I get a "calculating cells" message for basically every little thing I
do--making my work very, very slow. Just to simply add a column, it
will take as long as a minute.

I have excel 2003.

Any recommendations on how I can avoid this problem, or make things go
faster?

Thanks.

 

[Nick Hodge]

Mike

You could switch off auto recalc (Tools>Options...>Calculation). You could
also use some faster functions that IF and VLOOKUP in INDEX, MATCH, etc.

Also, I would get the data into a database and do some of the heavy work
there, just relying upon Excel for final calcs on smaller subsets.

Just my £0.02

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
web: www.excelusergroup.org
web: www.nickhodge.co.uk

 

[Pete_UK]

Further to Nick's points, do you really need those formulae to be
active all the time? Often once you have retrieved a value through
VLOOKUP that's all you need, so you could fix the values of those
formulae which do not need to be active. As well as speeding things
up, this will also make the file smaller when you save it.

Hope this helps.

Pete

 

[Mais qui est Paul]

Bonsour® Mike C avec ferveur ;o))) vous nous disiez :

Any recommendations on how I can avoid this problem, or make things go
faster?

Its not the number of formulae that consumes the calculation time, it's the number of cell references and calculation operations.

Just have a look at :
http://www.decisionmodels.com/calcsecrets.htm

HTH