19. Formula Performance

On a small table any formula calculates instantly. But in a file with 100,000 rows, poor techniques turn every change into a wait. The main sources of slowness:

• Repeated lookups. =IF(ISNA(VLOOKUP(…)),0,VLOOKUP(…)) searches for the same thing twice. One lookup instead: =XLOOKUP(B2,G2:G6,H2:H6,0) (the fourth argument is what to return if nothing is found), =IFNA(VLOOKUP(…),0) or LET to store the result in a variable. If you need one lookup in several columns, find the row number once in a helper column with MATCH, and then use INDEX.
• References to entire columns such as B:B in SUMIF, and especially in array formulas, make Excel scan a million rows. Use an exact range, a Table, or a spilled range such as J2#.
• Volatile functions (TODAY, NOW, RAND, RANDBETWEEN, OFFSET, INDIRECT, CELL) recalculate on any change in the workbook, and so does every formula that depends on them. Replace OFFSET with INDEX or TAKE: =SUM(TAKE(D2:D13,H8)). Keep the report date in a single parameter cell instead of writing TODAY() in every row.
• One formula instead of a hundred. =SUMIF(B2:B13,J2:J6,D2:D13) in one cell spills over the whole list and calculates in a single pass.

Calculation modes (Formulas › Calculation Options): "Automatic" recalculates after every change; "Manual" recalculates when you press F9 (Shift+F9 recalculates only the current sheet). Manual mode helps when you edit a heavy model, but it is easy to forget to recalculate and send out stale numbers.

To be honest about the practice grid: there is no recalculation timer here, the table is small, and you will not see a speed difference. The check looks at the techniques: the result must stay the same, and the slow constructions must go away.