Tyler Durdin
New Member
- Joined
- May 29, 2017
- Messages
- 3
I have also posted this question on stackoverflow.com at the following link:
stackoverflow.com
Every day I download a table of customer and product combinations into an Excel table from a local SQL server. In Excel I have a total of 294 calculated columns performing calculations against the downloaded data (14 columns each day for the next 21 days = 294). Consistently I have discovered for the first refresh on any given day the file takes 5-10 mins to refresh and recalculate whereas any subsequent refreshes take less than 1 minute.
From reading some microsoft documentation, faster refresh times can usually be expected on second and subsequent refreshes because excel optimises the calculation chain with each subsequent calculation.
My question is whether anyone can suggest a way of forcing Excel to store the optimised calculation chain sequence so that it does not need to start from scratch every time excel is opened so that my refresh times can stay less than 1 minute.
I have attempted to execute both Application.CalculateFullRebuild and Application.CalculateFull prior to beginning the data refresh but have not found them to make a noticeable difference. I have also spent a fair bit of time refining my formulas using the usual techniques and, while they're maybe not entirely perfect, I can safely say there's not a lot else I can do to refine the formulas further.
Any suggestions are welcome.
Ways to speed up table refresh in Excel
Every day I download a table of customer and product combinations into an Excel table from a local SQL server. In Excel I have a total of 294 calculated columns performing calculations against the
Every day I download a table of customer and product combinations into an Excel table from a local SQL server. In Excel I have a total of 294 calculated columns performing calculations against the downloaded data (14 columns each day for the next 21 days = 294). Consistently I have discovered for the first refresh on any given day the file takes 5-10 mins to refresh and recalculate whereas any subsequent refreshes take less than 1 minute.
From reading some microsoft documentation, faster refresh times can usually be expected on second and subsequent refreshes because excel optimises the calculation chain with each subsequent calculation.
My question is whether anyone can suggest a way of forcing Excel to store the optimised calculation chain sequence so that it does not need to start from scratch every time excel is opened so that my refresh times can stay less than 1 minute.
I have attempted to execute both Application.CalculateFullRebuild and Application.CalculateFull prior to beginning the data refresh but have not found them to make a noticeable difference. I have also spent a fair bit of time refining my formulas using the usual techniques and, while they're maybe not entirely perfect, I can safely say there's not a lot else I can do to refine the formulas further.
Any suggestions are welcome.
