Excel Mortally Slow
 

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?

Excel Mortally Slow
 

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:B1000 0,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

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Ray S." wrote in message
...
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?

Excel Mortally Slow
 

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" wrote:

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:B1000 0,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

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Ray S." wrote in message
...
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?

Excel Mortally Slow
 

Thanks for the feedback!

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

www.decisionmodels.com

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Ray S." wrote in message
...
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" wrote:

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:B1000 0,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

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Ray S." wrote in message
...
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?