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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Mike C - 30 May 2008 16:13 GMT

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.

Reply to this Message

Nick Hodge - 30 May 2008 16:38 GMT

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

Signature

HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
nick_hodgeTAKETHISOUT@zen.co.ukANDTHIS
web: www.excelusergroup.org
web: www.nickhodge.co.uk

> 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
[quoted text clipped - 11 lines]
>
> Thanks.

Reply to this Message

Pete_UK - 30 May 2008 17:43 GMT

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

> 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
[quoted text clipped - 11 lines]
>
> Thanks.

Reply to this Message

Mais qui est Paul - 30 May 2008 20:00 GMT

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

Signature

Reply to this Message