How can I make Excel run faster?

[es330td]

I was asked to give some advice on a spreadsheet for my father-in-
law. From an electronic measuring device it pulls in 4 sheets each
containing 400,000 rows of two columns; a time value and then a
measured value. Once the data is in the workbook a routine is run
that churns it all upp and spits out some results, a process that
takes about 20 minutes to run. A good chunk of the processing logic
is VBA code, which so far as I can tell runs single threaded.

This machine is a 3 Ghz quad core with 8 GB of DDR-2 memory and a fast
SATA HDD and the memory usage rarely goes above 600MB, CPU% is about
30 and the hard drive blinks only intermittently.

Is there any way I can make Excel better utilize the system resources
and run faster?

 

[Jim Thomlinson]

The description of what you are up to is rather thin. If you are processing
1.2 million records though there is no way you will do that quickly. As for
Multi-thread that is not going to happen with VBA. In terms of speed and
memory here is an excelent resource...

http://www.decisionmodels.com/index.htm

As for the code if you posted it we might be able to give it a few tweeks to
speed it up...

 

[Bernie Deitrick]

1) If you are doing a statistical analysis of the data, it may be possible to use a subset of the
data rather than the full data: i.e., pull one out of every 10, 25, 100, 400, 1000 records - depends
on the relative speeds of the data collection versus data change - measuring room temperature every
millisecond won't give your any better average than measuring it once every minute.

2) If you are doing calcs in VBA, consider using Excel formulas instead - they are MUCH faster. For
example, stepping through values to find the Max value is waaaaay slow compared to
Application.Max(Range(...))

3) Post your code...

HTH,
Bernie
MS Excel MVP

 

[es330td]

1) If you are doing a statistical analysis of the data, it may be possible to use a subset of the
data rather than the full data: i.e., pull one out of every 10, 25, 100, 400, 1000 records - depends
on the relative speeds of the data collection versus data change - measuring room temperature every
millisecond won't give your any better average than measuring it once every minute.

2) If you are doing calcs in VBA, consider using Excel formulas instead -they are MUCH faster.  For
example, stepping through values to find the Max value is waaaaay slow compared to
Application.Max(Range(...))

3) Post your code...

HTH,
Bernie
MS Excel MVP








- Show quoted text -

To both posters: I apologize on the lack of details. The spreadsheet
was actually written by somebody else and while he complains about the
speed my f-i-l is so tired of hearing the complaints that he asked if
I could do anything. I got to see it run so I'll add some details.
First, this is Excel 2007 on Vista 64

1. While the monitoring tool produces 4 sets of data all of them are
copied to a single sheet by a macro.

2. The data must all be used; no sampling is possible because one
part of the process scans the column searching for the largest value.
There are actually several of these scans performed as the spreadsheet
calculates many other columns (at least 14) and scans those output
values as well.

3. A great number of the cells are formulas and some are fairly
involved. If I read it right there are several fields that are linear
regessions on hundreds of data pairs.

I will provide additional info if I get any.