Google Sheets updating after every change, which is too often, slowing everything down

Question

I have a lot of independent functions and information in my sheet, and every time I make a slight change somewhere in the sheet, it seems like the entire sheet is being saved and re-updated. So every (automatic) save slows down my spreadsheets tremendously. It is hard to tell, because sometimes the re-updating occurs immediately, sometimes after a while, and sometimes it doesn't happen at all.

If I am correct about this, how can I change it so my spreadsheet updates only when I prompt it to?

In File->Spreadsheet Settings there are three recalculation options. I'm not sure if they are specific to:

how often NOW, TODAY, RAND, and RANDBETWEEN are updated.

or what that actually means is how often any input is updated, but in any case, all three options include a recalculation after On change, which, if applicable to my case, is too much.

How do I make it that updating of inputs is only when I decide it?

Otherwise, every time I make the slightest change, the entire large document takes its time saving and recalculating the inputs, and I can't get anything done.

Answer 1

What you are seeing is a spreadsheet package's default behaviour.

Google Spreadsehets has an option under File > Spreadsheet Settings > Recalcualtion.

But unfortunately there does not seem to be a "recalculate on user request only" option: instead all the available options include "on change". (This is different to Excel, for example, which does have a manual only option for worksheet calculation).

One option would be to to split your data into multiple sheets, so that the amount of data to be saved in each one is less.

Answer 2

How do I make it that updating of inputs is only when I decide it?

Very simple, turn Iterative calculation on. Name a cell freeze and put a checkbox. Then apply this to the beginning of any formula you want to be able to freeze.

A1 should be the address of the cell you are in (a circular reference) rand() is just a placeholder you can put any formula you want.

=if(freeze,A1,rand())

If you have formulas that output to multiple cells you will have to create a helper cell for each one of them to "compact" the output into a single cell and use the second cell to "extract" them into multiple cells due to a glitch in how Google Sheets is able to detect circular references.