I recently added a number of sheets from Tiller Labs to my foundation template. When I update sheets it now takes a significant amount of time to load/update.
I’m curious to hear how others have set up their spreadsheets? Do you keep them separate? Has anyone else ran into this slow load time issue as well?
Ben Collins has some great tips. https://www.benlcollins.com/spreadsheets/slow-google-sheets/
The ones I use are: storing intermediate results. Many of the Tiller sheets are “standalone” which means similar computations are made in multiple sheets. What I did was to compute intermediate results in one sheet and have others simply use that (e.g., for balances, I have a sheet that creates a monthly balance view across accounts and everything else refers to that including net worth, accounts, etc).
Also, I have reduced use of arrayformulas that are open-ended (e.g. “Transactions!A2:D”) and moved to ones where it is closed ended but with a formula that figure out how large the sheet is (e.g., using COUNTA to count the number of rows and using arrayformulas that are precise). Also, where I use the same value over and over again (e.g., the output of TODAY(), size of sheets through COUNTA, etc., I compute once and store as an intermediate result). Again very good for my spreadsheet but makes it less standalone
Lastly, in some cases, I have stopped using INDIRECT where it’s less useful. Though the speed savings there are less
As I state here, Tiller should take the lead on this. Eventually, this issue will affect every user.
Blake
https://community.tillerhq.com/t/can-you-create-a-blog-post-about-speeding-up-a-google-sheet/2133/10