Spreadsheets

Google Sheets slow with a big statement: what to change

Google Sheets slow with a big bank statement? Fixes in order of impact: bounded ranges, fewer ARRAYFORMULA and IMPORTRANGE calls, and splitting by year.

Editorial illustration for Google Sheets slow with a big statement: what to change

A Google Sheet slows down with a big statement when too many formulas depend on too many rows. The fixes that help most, in order, are bounded ranges instead of whole columns, fewer ARRAYFORMULA and IMPORTRANGE calls, removing volatile functions, deleting unused columns, and splitting the data by financial year. Test each change on a copy so you can see what actually helped.

Fix 1: bound your ranges

Replace C:C with $C$2:$C$5000, or with a range sized to your data. Whole-column ranges are fine for tiny files, but in a file with thousands of rows, every SUMIFS and QUERY has to look at more than it needs. Oversized ranges also make an ARRAYFORMULA fill far more rows than your data needs.

Fix 2: cut down ARRAYFORMULA and IMPORTRANGE

  • One ARRAYFORMULA per column is fine. A hundred of them, each spanning thousands of rows, is not. Convert helper columns you no longer change into plain values.
  • IMPORTRANGE fetches data from another file each time it needs to. If three tabs all import the same source, import it once into a hidden tab and point the others at that tab.
  • If a month is finished and will not change, copy the range and paste values only with Ctrl+Shift+V. A frozen number costs nothing.

Fix 3: replace volatile functions

NOW, TODAY, RAND and RANDBETWEEN recalculate with every change, and so can formulas that depend on them. INDIRECT and OFFSET can have the same effect. Put =TODAY() in one cell and refer to it, and replace INDIRECT and OFFSET with direct references or INDEX. You can also check File, Settings, Calculation and make sure recalculation is set to On change, not On change and every minute.

Which parts of the sheet are the slow ones?

  1. Make a copy of the file.
  2. Delete one tab at a time, or the formula column you suspect, and see if scrolling and typing feel faster.
  3. Look for formulas that repeat the same lookup for every row. A VLOOKUP in 8,000 rows that scans the same 5,000-row table can mean up to 40 million comparisons: 8,000 times 5,000.
  4. Ask whether each lookup is needed at all. Once a month is final, paste the looked-up result as values and delete the formula.
ChangeEffortLikely effect on a statement-sized sheet
Bound ranges instead of whole columnsLowOften noticeable
Convert finished months to valuesLowBig, if many formulas
Import once instead of three timesMediumNoticeable if IMPORTRANGE is heavy
Remove volatile functionsMediumDepends how many
Split by financial yearMediumLarge for multi-year files

Fix 4: clear out unused columns and rows

A tab has a grid, and blank columns and rows still take up memory. Select the unused columns to the right of your data, right-click and choose Delete columns, then do the same for unused rows at the bottom. Also remove old conditional formatting rules and data validation applied to the whole sheet: Format, Conditional formatting lists them for you.

Fix 5: split by financial year

When a sheet is for April to March, make one file per year and keep only the current year live. The old year becomes a read-only archive. A summary tab that needs several years can read totals using one IMPORTRANGE per year, not the full data.

When the sheet is the wrong tool

Once you are loading a full year of statement files from several accounts, spreadsheets stop being pleasant. SpendixAI is designed for that case: you upload PDF, CSV or Excel statements, review rows before saving, and use reports and CSV downloads, while a sheet can remain for the odd custom calculation.

Quick checklist

  • Ranges bounded.
  • Finished months pasted as values.
  • TODAY in one cell.
  • Unused columns and rows deleted.
  • One file per financial year.
SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports