Speed Up 🐢 Google Spreadsheet Formulas
Slow Spreadsheets?
This article shows a few standard and automated approaches to fix that.
Turn Calculations ON/OFF
The idea is to have a switch between all or some formulas.
Create or use the “Settings” sheet, and add a checkbox there:
✅🔘All Formulas ON/OFF
Create a named range with the inverse logic, for easier use in formulas:
stop_all
Modify all the formulas to this:
=if(stop_all, "-", <<<Original Formula Text>>>)
Do it manually for each formula, or…
💊Automate the process
Use find and replace to modify formulas in bulk. Use these sets:
- use regular expressions,
- and search in formulas.
Will do it in 3 moves.
♟#1. Replace new lines.
Formulas in Sheets may have new lines, and multiline mode does not work for the “Find and Replace” tool. So, workaround!
- find:
\n - replace with:
🙈
♟#2. Modify formulas
The following will add conditions to all formulas, so they calculate when stop_all=false.
- find:
^=(.*) - replace with:
=if(stop_all, "-", $1)
We use regular expressions here. You can see all of the RE2 expressions on GitHub.
♟#3. Fix formulas
Kill the monkey to make formulas work again.
- find:
🙈 - replace with:
Use OFFSET
We’ll reduce the number of calculated cells. This tip will convert wrong array formulas into good ones.
Bad array-formula:=ArrayFormula(A2:A + 1)
Good array-formula:=ArrayFormula(OFFSET(A2,,,n_r) + 1)
where n_r is the calculated number of used rows:
=max(filter(row(A2:A), A2:A<>""))
The trick here is to replace open ranges in formulas with the offset.
A2:A → OFFSET(A2,,,n_r)
Do it manually for each formula, or…
💊Automate the process
Set a named range again, and create the “n_r”.
Use find and replace to modify formulas in bulk. Use these sets:
- use regular expressions,
- and search in formulas.
The following regex should make sure no dangerous things happen:
- find:
([^'!])([a-zA-Z]+)(5)+\:([a-zA-Z]+)
☝🏼Change 5 to your number, this is the first row of ranges like
A5:A
- replace with:
$1OFFSET($2$3:$4$3,,,n_r)
Calculate the formula execution time
There is a formula to benchmark formulas. You can load it as a named function. Usage:
=BENCHMARK(LAMBDA(<<<Your original formula>>>)))
For instance:
=BENCHMARK(LAMBDA(SEQUENCE(150000)))
Returns execution time in seconds.
Inspect heavy formulas
Some formulas may have a quadratic complexity: VLOOKUP, FILTER, COUNTIF. Reduce their number if possible.
☝🏼The
REGEX…family is also slow for a large number of cells.
You may think of different algorithms for heavy formulas. For example, there is a “Cumulative count” formula that works faster.
If built-in formulas become too slow, the last chance is to go to Apps Script. Create one scripting custom formula instead of multiple complex formulas. The process is not easy, but it will solve the issue.
Be careful with the new projects
It is much easier to solve heavy calculations if you notice symptoms in the early stage. Stop if you see the progress bar while counting your formulas:
This indicates your formulas are slow. Start refactoring your calculations.
Your Experience?
What helped you to improve Spreadsheet performance? Share with me and I will add to the article.
