Improving speed of SLOW spreadsheet(s)

[spencer_time]

Hello,

I will give one example (the worst offender) although I have several families of spreadsheets that have this problem.

When I am crunching data for work I use Excel. The way I do this is to import the space delimited raw data into excel and begin manipulating it. In the process I end up with a lot of linked cells and formulas and files. In this example I have the following:
1 summary file that has 68 sheets with ~half having 100 rows and 25 columns and the other half having 1700 rows and 11 columns
33 sample average files that have 30 sheets with 1501 rows and 11 columns
33 corrected data files that have 30 sheets with 1501 rows and 11 columns
33 raw data files that have 30 sheets with 1501 rows and 11 columns

The files all link to each other both directly as well through formulas. This ends up being quite a slow file, to open, to perform a find&replace, to refresh links, to save, etc. And on this file in particular, it is so large I wasn't able to run my macro that graphs the final data, it kept causing my computer to freeze up (I even tried leaving it over the weekend with no success).

This leads to a few questions.

1. Is it the sheer amount of data, or are the links the primary cause of the slowdown?
2. Would it be better/faster to have one file with 3,038 sheets, or the 100 files with 30-68 sheets all linked together?
3. Do some functions work quicker than others? Does =STDDEV() slow down a file more than =AVG() or is it negligible?
4. Is there anything else I might can do to speed up this and future spreadsheets?
5. Is there standard practices that I should have done that would have made for a cleaner family of files and a more efficient/faster outcome?

This example is VBA free, so that isn't a concern here. That being said some of my files do use VBA. I know a little, is there anything other than the following that I should do to help my VBA files? I currently call the disable subroutine at the beginning of a VBA calculation and then call enable when it has reached the end of the code. **I know this isn't the VBA sub-forum, so I understand if this part isn't answered, it was just a related secondary question**

Please Login or Register  to view this content.

Thanks in advance for your advice!

[Bernie Deitrick]

When working with raw data, the end result should depend solely on the input data. So, what I do in such situations is to do everything through a macro, and convert all formulas into values and not link anything. The formulas should never return different values since the data does not change - if the base data needs to be changed, then just re-run the master macro.