112
@pomodoro_pat ·

Is it normal that 8000 rows takes 20 seconds to recalculate after every edit Formula Help

Roughly 8,000 rows, 15 columns, mostly SUMIFS with a few INDEX MATCH and some conditional formatting. Every time I type in a cell the whole thing freezes for about 20 seconds. The file is only 4 MB. Is this normal at that size or is something specific in there dragging it down?

7 answers Share
Report

Answering anonymously — a moderator will review it first.

  • @kernel_ring_buf · 2mo ago · 3 replies

    Not normal. 8,000 rows should feel instant, so something in there is either volatile or scanning whole columns. The order I check:

    • Formulas referencing A:A instead of A2:A8000. Fifteen columns of SUMIFS over full columns means a million row scan per formula, per recalculation.
    • Volatile functions: OFFSET, INDIRECT, TODAY, NOW, RAND. Any one of them forces the whole workbook to recalculate on every keystroke.
    • Conditional formatting applied to entire columns, or rules that multiplied when people copied rows. Open the rules manager — I have seen 4,000 rules in a sheet that needed three.
    • Array formulas or SUMPRODUCT over full columns, same problem as the first point.
      Fix those and it drops under a second.
    149
    Share
    Reply

    Answering anonymously — a moderator will review it first.

    Report
    • @pomodoro_pat · 2mo ago

      The conditional formatting rules manager had about 1,900 entries in it. I did not know that could happen.

      67
      Share
      Reply

      Answering anonymously — a moderator will review it first.

      Report
    • @kernel_ring_buf · 2mo ago

      It happens every time someone copies and pastes formatted rows. Delete them all and reapply once to the used range. Check the used range itself while you are there — Ctrl+End sometimes lands on row 900,000 because of old formatting, and everything is slower for it.

      71
      Share
      Reply

      Answering anonymously — a moderator will review it first.

      Report
  • @xlookup_lena · 2mo ago

    4 MB is large for 8,000 by 15 of plain data, which supports the theory that there is dead formatting or a phantom used range in there. A clean file of that shape is usually well under a megabyte.

    88
    Share
    Reply

    Answering anonymously — a moderator will review it first.

    Report
  • @gpio_gwen · 2mo ago

    As a stopgap, set calculation to manual and press F9 when you want it updated. Fine while diagnosing, dangerous as a permanent state because sooner or later you will screenshot stale numbers.

    41
    Share
    Reply

    Answering anonymously — a moderator will review it first.

    Report
  • @harlan_voss · 2mo ago · 2 replies

    Move it to a database if it is that slow.

    20
    Share
    Reply

    Answering anonymously — a moderator will review it first.

    Report
    • @xlookup_lena · 2mo ago

      8,000 rows is nowhere near needing a database. This is workbook hygiene, not scale.

      17
      Share
      Reply

      Answering anonymously — a moderator will review it first.

      Report