# 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 theRE2 expressionson 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.