Forum Discussion
Need to speed up Spreadsheet
Thanks for the response.
Sheet2 has some complex formula, some rather hefty stuff and there are probably some efficiencies to made just by tidying it up.
I think you've addressed my general concern, which is in fact that I'm trying to shortcut the issue.
In my simple mind I figured that by turning off large computations (until necessary) this would speed up the rest of the workbook, but perhaps I need to go back and find a way to be more efficient with my formulas.
Link below to the spreadsheet that I'm using to test the VBA code. This includes some random formulas to intentionally weigh down Sheet2 to represent my issue. Feel free to see if you can come up with a solution using macros.
https://we.tl/t-Q6ZeliMzyC
I appreciate the additional context. I reviewed the file you provided. It's a good example, and indeed, large datasets and complex formulas can lead to performance issues.
Your instinct about optimizing the formulas is on point. Here are a few suggestions:
- Check for Volatile Functions: Certain functions, such as INDIRECT, OFFSET, and CELL, are volatile and can significantly slow down your workbook. If possible, try to minimize the use of these functions.
- Use Named Ranges: Instead of referencing entire columns like Sheet2!A:A, try to use named ranges that encompass only the necessary data. This can help Excel perform calculations more efficiently.
- Array Formulas: Some formulas in your Sheet2 use entire column references. Consider using array formulas with specific ranges to reduce the number of cells being calculated.
- Avoid Full Column References: Refrain from using full column references if you don't need them. For example, instead of =IF(A:A="Yes", B:B, ""), use =IF(A1:A100="Yes", B1:B100, "") if your data only goes up to row 100.
While macros can help control when calculations occur, optimizing the formulas themselves will have a more significant impact on overall performance.