Excel Mortally Slow

[Ray S.]

I'm running Excel 2003 under Windows 2000 (no choice...this is at work). My
desktop unit has a Pentium 4 2.66 GHz chip and 2 Gig of RAM. I've had to turn
off the auto recalc because it takes forever. I literally mean forever. If I
have a file with around 9,000 records and I have to do some vlookups and some
simple calculations or functions, I might as well take lunch. I only get this
snail's pace response with Excel. Lately, any vlookup to another file takes
from a few minutes to maybe ten...and that adds up to an enormous waste of
time. Any ideas?

 

[Niek Otten]

Without the nature of your data and your exact formulas: difficult.
Bu you mention VLOOKUPs.
VLOOKUPs can be extremely slow if the 4th argument is FALSE. Or if you
specify a whole column to search instead of just the cells with data.
If the data is sorted, use TRUE as 4th argument, search the first column
first to check that the key is right, and then do a 2nd lookup in the target
column.
This can be several thousands of times faster!

Example:

Don't use

=VLOOKUP(C1,A1:B10000,2,FALSE)

Use

=IF(VLOOKUP(C1,A1:A10000,1)=C1,VLOOKUP(C1,A1:B10000,2),NA())

Really, hundreds to thousends times faster! But only if the data is sorted
ascending. There are tricks for descending data, post back if you need them

 

[Ray S.]

Wow, where did you pick up that trick...it is amazingly faster. Is there
anything I can do about speeding up the recalculation of formulas? This was
so helpful, I'd love to read ANY tips you can pass along...GREAT!

 

[Niek Otten]

Thanks for the feedback!

To learn all about Excel speed, visit Charles Williams' site:

www.decisionmodels.com