Personal budget tracker, 8000 rows, maybe 15 columns. Every edit hangs for 15-20 seconds before the cell updates. I assumed 8000 rows was tiny. There are a few VLOOKUPs, some SUMIFS pulling from other tabs, and about 200 conditional formatting rules. Is this just what Sheets does at this size, or have I built something badly?
Also check the actual used range rather than the rows you can see. Select a cell and hit ctrl+end. If it jumps to row 40000 because something was once pasted there, you have 32000 rows of invisible formatting being evaluated. Delete those rows properly rather than clearing them.
Ctrl+end took me to row 26,914. I have no memory of ever putting anything down there.
It is usually one accidental paste or a stray space years ago. Right click the row numbers and delete rows rather than selecting the cells and hitting delete, otherwise the ghost range stays.
Swap the VLOOKUPs for INDEX/MATCH or XLOOKUP if you have it. At this size the raw speed difference is small, but VLOOKUP over a wide lookup table does genuinely more work, and it breaks whenever someone inserts a column, which is its own kind of slow.
Worth timing it in a private window with extensions disabled too. Spreadsheet performance complaints are occasionally a browser extension inspecting the page on every change rather than the sheet at all.
@shadowing_sena · 2w ago · 3 replies
8000 rows is tiny. Something specific is doing this, and the likeliest suspects in order:
200 conditional formatting rules is a lot, and any that use custom formulas over whole columns re-evaluate on every single edit. Consolidate them — one rule applied to a large range is far cheaper than fifty small ones.
Whole-column references in SUMIFS.
SUMIFS(Other!C:C, Other!A:A, $A2)on 8000 rows means eight thousand scans of an unbounded column. Bound the ranges.And check for volatile functions: NOW, TODAY, RAND, INDIRECT and OFFSET all force a full recalculation of everything that depends on them, every time anything changes anywhere. One TODAY in a helper column feeding 8000 formulas is a classic cause of exactly this.
Reply
Report
@till_and_tally · 2w ago
That one fix has saved more spreadsheets than anything else I know. One volatile cell, everything else references it.
Reply
Report
@listcomp_leo · 2w ago
TODAY in a days-since column that everything else references. Replaced it with a single cell holding TODAY and pointed the column at that cell. From 18 seconds to under two.
Reply
Report