I have a huge spreadsheet in Google Sheets and it takes about a half-minute to recalculate after every change. During this delay, the user interface is responsive, but there's a slow progress bar showing the ongoing update.
What changes would provide the greatest improvement?
- Should I remove nearly-static sheets?
- I have 20 such sheets; 10x150 cells in each, which never change and don't need re-calculation;
- 80% of their content is constant values,
- 20% is one-operation simple calculations (no RANDs or heavy functions).
- Other sheets are heavily dependent on formulas using RAND/RANDBETWEEN.
- would it help to use a single source of RAND values (a dedicated sheet) and refer to it multiple times, rather than invoking RAND separately in each of them?
- The logic of my spreadsheet allows such re-use of randoms. I have 17 such sheets; each is 12x150 cells, about 6x150 RAND calls, about 20x150 non-RAND operations.
- Should I reduce the number of formulas by integrating the formulas used to return preliminary/helper results directly into the formulas that reference them?
- Should I reduce the number of non-RAND atomic operations even though this will negatively affect the readability of those formulas?
Overall, I'm interested in the impact by largest-to-smallest of factors contributing to the long recalculation times following changes.