I have a huge spreadsheet in Google Sheets and it takes about half a minute to complete every change I make to its contents: user interface stays responsive, but there's a slow progress bar showing the ongoing update (upload? re-calculation? download)?
Which factor will give the most improvement in the update time?
What I choose from is, with respective numbers:
- Removing nearly-static sheets (I have 20 such sheets; 10x150 cells in each) which (a) never change and therefore don't need re-calculation; 80% of content is constant values, the rest is one-operation simple calculations (no RANDs or heavy functions).
- Other sheets are heavily generated based on RAND/BETWEENs
Will it help to use a single source of RAND values (a dedicated sheet) for all of them, referred to 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.
- Reducing number of cells, through integrating preliminary results into more complex formulas.
- Reducing number of non-RAND atomic operations (although it will likely hurt readability of the calculations).
Overall, I'm interested in the largest-to-smallest list of factors contributing to the large update time hence slow progress bar on every update.