Speeding up Data Imports

[dflak]

If you are importing a lot of data into a table with a lot of formulas, you sometimes get a lot squared time downloading the data.

Even if you turn off calculations, Excel still calculates formulas in tables “on the fly” as it is importing the data.

One way to cut down on the amount of time it takes to import data is to remove the columns that contain the formulas, import the data and then re-apply the formulas after all the data are imported. This can speed up import by a factor of 10.

The accompanying spreadsheet has an example and the code to do this.

[DannyPhilips]

Interesting method, thank you for sharing!

[FDibbins]

I have taken this approach in the past too, just left formulas in the top tow, then copied them down once the data was transferred.

Another approach is to VALUE those formulas that wont change with the addition of new data, eg previous year's summaries