Why Excel estimating formulas break when someone adds a row
Inserting a row should not drop a total. Structured references and locked formulas stop the classic estimating-sheet failure.
5 min read · Published 2026-10-30
Someone inserts a row to add a variation. The total above it no longer includes the new line, or it includes a heading. The quote looks finished and the number is wrong.
Why it happens
Ranges written as A10:A40 do not grow when a row is inserted outside them, and some grow when they should not. A SUM of a whole column, or an Excel Table, behaves differently from a hand-built range.
What to change
Put takeoff lines in a table. Lock the cells that calculate. Let people add items in the rows that are meant for items, not in the middle of a total.
Any trade that adds scope during a tender will hit this if the sheet was built as a one-off.