I've got a 300+ MB Excel file that I'm working with, to which I add a new sheet with a ~60,000 x 5 array of data, plus additional calculations using that data, every month. Performance is not great - it takes forever to open. I've done what I can to eliminate the use of volatile functions, other than a few indirect() functions for page referencing. My question is whether it is better to paste values of the monthly data, or link to the data in the workbook that I'm sourcing it from (a new one each month).
2 Answers
I think you will find you get better performance out of pasting values. Less calculations for Excel to complete while opening the file. One thing that might help to is go to File>Options>Formulas and change calculations to manual. That way it won't be updating all the other formulas when opening the file, but when saving. You could open the file then click save when you are ready for the updates.
Hope that helps, Brad
What Brad said, plus just checking, are you using a solid state hard drive as your hard drive? I find them a huge help in all demanding applications.
