Insert or Delete Rows in Excel Without Breaking Formulas

The file is called Pension_September.xlsx, and Harlow & Voss — a seventeen-person architecture practice — has produced one like it every month for six years. Column A is the employee, column C is their monthly qualifying pay, column D is the 5% taken out of their salary, column E is the 3% the practice adds, and column F is the two together. F20 totals the column, the practice pays whatever F20 says to the pension provider before the 22nd, and the provider's statement comes back matching it to the penny.

On Monday 5 January 2026, three architectural assistants started on the same day. Whoever updated the file did the obvious thing: clicked the first empty cell under the last employee and typed all three of them in.

F20 contains =SUM(F2:F15). The last employee it had ever needed to reach was on row 15.

Three reconciliations ran every month and all three of them passed. The payment matched the file. The provider's statement matched the payment. The payroll journal matched the deductions taken from salaries. Every one of those comparisons was between the file and something downstream of the file, and the error was upstream of all of them.

It surfaced on 15 September, when Petrosyan opened the provider's app to see what nine months had built, and found an account that had never received anything. The money had been taken out of her pay on time, every month, and stopped at row 16.

Nothing in the workbook was broken. F2:F15 summed correctly, the rate formulas on rows 16, 17 and 18 were present and right — Excel had filled them in by itself — and F20 was an honest total of exactly what it had been asked to total.

What this covers. Inserting and deleting rows and columns works the same way in every version of Excel, on Windows, Mac and the web, and has done for twenty years. Tables (Ctrl+T) need Excel 2007 or later. XLOOKUP needs Microsoft 365 or Excel 2021; INDEX/MATCH, VLOOKUP, SUM, SUMIFS, COUNTIFS, COUNTA, SUMPRODUCT, SUBTOTAL and OFFSET work everywhere. Grouping sheets is a desktop behaviour. The example is a pension return because a pension return is the shape of problem where a short total survives review: one number a month, paid to an outside party who has no idea how many people should be on it.

1) The Return That Balanced Every Month

Here is the sheet as the file holds it, with the September figures.

Column G is not in the real file; it is the question nobody asked. Fourteen rows are inside the range F20 totals and three are not, and there is nothing on the screen to tell them apart — same font, same format, same borders, same number of decimal places, the same three formulas across columns D, E and F.

That last part is worth dwelling on, because it is what made the rows look finished. Excel has an option called Extend data range formats and formulas, on by default, which copies the formatting and the formulas of a column down when you type in the row immediately below a list. So D16, E16 and F16 filled themselves in the moment a number was typed into C16. The starters' rows were complete and correct. The only thing missing was a total that reached them.

🎯 Scenario: One cell tells you whether a column's total covers its column. Put =SUM(F:F)-2*F20 somewhere out of the way. Every number in column F is either data or the total, so the whole column adds up to exactly twice the total — unless something is outside the range. In this file it returned 749.20, which is not a diagnostic: it is the missing money, to the penny.

2) A Range Grows When You Insert Inside It, and Nowhere Else

Excel rewrites references by following the cells they point at. Insert a row, and every cell below it moves down one; every reference that pointed at those cells is rewritten to point at their new addresses. That is the whole mechanism, and everything below is a consequence of it.

So whether an inserted row joins a range depends entirely on where you insert it:

Both edges leak and the middle is safe, which is the opposite of the mental model most people carry. Inserting at the top of a block feels like the safest possible place to add a row; it is one of the two places where the range does not follow you.

There is one piece of help built in, and this file had designed it away. When a total sits in the cell immediately below its range — =SUM(F2:F15) in F16, with nothing between them — inserting a row directly above the total does extend the range, because Excel can see the formula and its range as one block. Put a blank row between the data and the total, the way this sheet does with row 19, and that protection is gone. Treat it as a convenience you are pleased to get, never as a thing you rely on.

🎯 Scenario: Test it on your own file rather than trusting the table above. Insert a row at the very top of a data block, type 1 into the column your total covers, and watch the total. If it does not move by exactly 1, every row anybody ever adds at the top of that block is invisible to it.

3) Typing Under a Block Is Not Inserting a Row

An insert is a structural edit: cells move, and Excel rewrites formulas to keep them pointing at the same data. Typing is not. Typing puts a value in a cell that was already there, nothing moves, and no formula anywhere is rewritten — because from Excel's point of view nothing has happened that could need rewriting.

This is the distinction the whole story turns on, and it is invisible at the moment it matters. Both actions leave you with a new row of correct-looking data under the last one. One of them is in the total and one is not, and the difference was decided several seconds earlier by whether you pressed Ctrl + or just started typing.

The reason it is so rarely caught is that the total still moves. Add three people below the range and F20 does not sit frozen at a suspicious number — the fourteen people inside the range get pay rises, bonuses, leave, and the total drifts every month exactly as a total should. A figure that never changed would have been noticed in February.

🎯 Scenario: Find out which way your Excel is set: File → Options → Advanced → Extend data range formats and formulas. On, it fills new rows with the column's formulas and formats, which is what makes an out-of-range row look finished. Off, new rows look obviously unformatted — uglier, and a great deal louder.

4) Deleting: #REF!, and the Quieter One That Is Not an Error

Deleting has two failure modes and only one of them tells you.

The loud one. Delete a row or column that a formula points at directly and the reference has nowhere to go, so Excel writes #REF! into the formula itself — permanently. =C2*0.05 becomes =#REF!*0.05. Undo puts it back; nothing else will, because the formula no longer records what it used to point at.

The quiet one. Delete a row inside a range and there is no error at all. =SUM(F2:F15) becomes =SUM(F2:F14), which is a perfectly valid formula that totals fourteen rows instead of fifteen, returns a smaller number, and looks exactly as correct as it did before. The same is true of SUMIFS, COUNTIFS, AVERAGE and every other aggregate: ranges shrink silently. A deleted row costs you its own value and says nothing.

Two related distinctions worth keeping straight:

• Delete clears, Ctrl - removes. Pressing Delete on a selected row empties the cells and leaves the row in place, so every range keeps its shape and the total falls by exactly that row's value — wrong, but arithmetically honest. Ctrl - takes the row out and moves everything under it up.
• Deleting a column is deleting a row sideways. Same two failure modes, and one extra victim in section 5.

🎯 Scenario: After any structural edit, search the whole workbook for the loud failure before you close it. Ctrl F → Options → Within: Workbook, Look in: Formulas, find #REF!. It takes four seconds and it finds every one of them, including the ones on sheets nobody has opened this year.

5) VLOOKUP Counts Columns; It Does Not Watch Them

Harlow & Voss had a second incident in the same workbook in April, and it is worth telling because of how it ended: somebody found it.

Monthly qualifying pay came from a Staff sheet via =VLOOKUP($A2,Staff!$A$2:$G$60,5,0). In April an administrator deleted the Staff sheet's column C, an old payroll reference nobody had used since the last system migration. Columns D through G slid left. The lookup range became Staff!$A$2:$F$60, which Excel rewrote correctly. The 5 did not change, because the 5 is not a reference — it is a number, and Excel has no way to know it was ever about a column.

Seven of the seventeen staff have a car allowance or regular contractual overtime, so for seven people qualifying pay and basic pay are different numbers. The April and May returns were £136.40 light between them. It was found in June, corrected, and cost almost nothing — which is the point of putting it next to the other one. The same workbook, the same kind of edit, the same absence of an error message; the difference in cost is entirely a difference in how quickly somebody looked.

Two rules follow:

• If the deleted column pushes the index past the new right-hand edge, you get #REF! and you are lucky. If it does not, you get a different field, silently. HLOOKUP does the same thing with rows.
• INDEX/MATCH and XLOOKUP do not count. They take the return column as a reference, so it moves when the column moves, and deleting the return column itself gives you #REF! rather than a neighbour's data. =XLOOKUP($A2,Staff!$A:$A,Staff!$E:$E) survives every reorganisation of the columns between A and E.

🎯 Scenario: In any workbook that people reorganise, Ctrl F for VLOOKUP( with Look in: Formulas, and read the index number in each hit. Every literal number above 2 is a bet that nobody will ever insert or delete a column inside that table. Convert the ones that matter to INDEX/MATCH or XLOOKUP and the bet goes away.

6) Cut Is Not Copy

Copy-and-paste and cut-and-paste look like the same operation with a different keystroke. For formulas they are close to opposites.

• Copy adjusts the formula you copied. =C2*0.05 copied from D2 to D3 becomes =C3*0.05. Relative references move with the formula.
• Cut does not. The same formula cut from D2 to D3 still reads =C2*0.05. Cut preserves the formula exactly; it changes where it lives, not what it says.
• Cut rewrites everybody else. Every formula anywhere in the workbook that pointed at the cut cells is rewritten to follow them to their new address. =C2*$J$2 becomes =C2*$M$2 the moment J2 is cut to M2 — including the absolute reference, because $ protects a reference from being adjusted on copy, not from following a cell that moved.

The one that bites is the second bullet, because it is the one people have no model for. Tidy a sheet by cutting a block of formulas one column to the right and every one of them still reads the column it read before — pointing now at cells one column away from the ones beside it. The formulas are unchanged, which is exactly the problem: you expected them to move like copies.

Two more things that are cut-and-paste without looking like it:

• Dragging the border of a selection is a cut and a paste. It is the fastest way to move data and the easiest way to do it by accident, off by one row.
• Cutting onto occupied cells destroys them with no warning and no shift. Copy-and-paste does the same; the difference is that a cut leaves a hole behind as well.

🎯 Scenario: After any Ctrl X, click the formula that consumes what you moved and read the formula bar. What happens to a range when you cut part of it out of the middle is not worth memorising — it depends on whether what is left can still be described as a rectangle — and reading the formula afterwards answers the question in every case.

7) Named Ranges, Formatting and Validation Share the Same Edge

Everything else in Excel that holds a range holds it the same way, and fails at the same boundary:

• Named ranges. A name defined as =Sheet1!$F$2:$F$15 behaves exactly like the SUM did: rows inserted inside it expand it, rows typed below it do not. A name is not a dynamic thing unless you made it one.
• Conditional formatting. Insert and delete rows in a formatted block for a year and one rule becomes five, each covering a scrap of the original range. Home → Conditional Formatting → Manage Rules shows the fragments. Rows added below the last rule's range are not coloured, which is how a negative number stops turning red.
• Data validation. The dropdown simply is not on the new row. Nothing warns you; the cell accepts whatever is typed, which is how a department called Finanace gets into a file that has a validated list of departments.
• Pivot tables. The source is a fixed range. Rows typed underneath are not in it, and Refresh reports success, because refreshing re-reads the range it was given rather than looking for more data.
• Charts, print areas and SUMIFS criteria ranges. All the same. A chart built on $A$2:$B$15 keeps drawing fourteen bars.

🎯 Scenario: Open Formulas → Name Manager and read the Refers To column. Anything ending in a row number near the bottom of your data is a range that stopped growing at some point in the past, and the fix is one edit each.

8) Whole-Column References: the Fix That Charges Rent

=SUM(F:F) cannot miss a row, which makes it tempting. It has a price:

• The total cannot live in column F — it would include itself, which is a circular reference.
• It sums everything in the column, including the stray subtotal somebody parked on row 400 in 2023.
• =COUNTA(A:A) counts the header, so headcounts come out one too high.
• SUM, COUNTIFS and SUMIFS are optimised for it and stay fast. SUMPRODUCT and older array formulas are not: handed F:F they will happily consider all 1,048,576 rows, and a handful of them will make a workbook that takes seconds to recalculate.

The middle ground costs nothing and covers almost everything: pad the range. =SUM(F2:F1000) on a block of seventeen rows absorbs the next nine hundred and eighty-two without a thought. Its failure mode is real but distant and loud — one day somebody adds row 1001 — and it beats the alternative, which fails at row 16 and says nothing.

🎯 Scenario: Take the one file you would least like to be wrong about, widen every aggregate range to a padded one, and add the check from section 11. Two minutes, and the class of error in this article is gone from that file.

9) The Range That Grows By Itself

The real fix is to stop maintaining ranges by hand. Select the block, Ctrl T, and it becomes a Table:

• Typing in the row directly under a Table extends the Table, and everything attached to it: the formula columns, the formatting, the data validation, the conditional formatting. Tab in the last cell adds a row too.
• =SUM(Pension[Total]) has no row numbers in it, so there is no boundary to fall off. The reference means "that column", and the column is however tall the Table is today.
• Charts and pivot tables built on a Table grow with it. A pivot whose source is Pension needs a refresh, not a new source range.
• The Total Row (Ctrl Shift T, or the Table Design tab) gives you a total that is part of the Table and cannot be left behind by it.

The costs are worth knowing before you convert a live file: Tables cannot contain merged cells, they cannot hold a multi-cell array formula, only one Table can cover a given range, INDIRECT cannot build a structured reference from text, and some old formulas elsewhere in the workbook will need a look. In exchange, the range maintenance stops being a task anybody can forget.

If you cannot use a Table — a protected sheet, a template somebody else owns — the second-best is a dynamic name: =OFFSET($F$2,0,0,COUNTA($F$2:$F$1000),1). It works, it is volatile, and it recalculates every time anything in the workbook changes. Prefer the Table.

🎯 Scenario: Convert one block and then go looking for what broke, rather than converting everything and finding out later. The things to check are formulas on other sheets that pointed into the block, any INDIRECT, and anything that assumed a fixed number of rows.

10) One Insert, Twelve Sheets

Select several sheet tabs — Ctrl click, or right-click → Select All Sheets — and every structural edit happens on all of them at the same address. Insert a row on the visible sheet and eleven other sheets get a blank row 7. Type, and you type on all of them, over whatever was there.

The only thing telling you is the word [Group] in the title bar and the fact that several tabs are white. Both are easy to miss, and grouping survives until you click a single tab, which means it survives lunch.

Two consequences specific to monthly workbooks, where tabs are supposed to line up:

• A 3D reference like =SUM(Jan:Dec!F20) reads cell F20 on every sheet between the two. Insert a row on the March tab alone and March's total moves to F21, while the 3D formula goes on reading F20 — which is now a blank cell, or worse, the last employee's contribution.
• Deleting a sheet cannot be undone. Every other edit in this article is one Ctrl Z away while you still have the file open. That one is not.

🎯 Scenario: Make reading the title bar a reflex before any structural edit, and click a single tab to ungroup before you start. If you have inherited a workbook of monthly tabs, Ctrl End on each of them: sheets that should be identical and are not tell you exactly where somebody inserted something on one tab only.

11) Six Checks That Catch a Range That Stopped Growing

Each of these is one cell, and each is worth more than a careful re-read of the formulas.

Two notes on that table. The fourth row is the one that mattered: the only check that would have caught this compares the file with the payroll that feeds it, not with the payment that comes out of it. Reconciling downstream tells you that you paid what you calculated, which is a different claim from calculating the right thing.

And the sixth is there to make a point. Searching for #REF! is a good habit and it would have found nothing here, all nine months. A workbook with no errors in it is not a workbook that is right.

12) Twelve Traps

1. Typing under a block adds a row to the sheet and not to the total. Inserting inside the block adds it to both. They look identical five seconds later.
2. Inserting at the top of a range leaves the new row outside it, the same as inserting below. The middle is the only safe place.
3. A blank row between the data and its total disables the one automatic expansion Excel offers.
4. Deleting a row inside a range is silent. The range shrinks, the total drops, no error appears anywhere.
5. Delete and Ctrl - are different edits. One empties a row, the other removes it, and only the second moves everything underneath.
6. VLOOKUP's column number is a number. Delete a column inside the table and it points somewhere else, with an error only if it now runs off the edge.
7. Cut does not adjust the formulas you cut, even though copy does. Moving a block of formulas is not the same as copying it.
8. $ does not stop a reference following a cut cell. It stops adjustment on copy. Those are different things.
9. Dragging a selection border is a cut and a paste, including when you did it by accident, one row off.
10. A named range is static unless you built it with OFFSET or pointed it at a Table.
11. Conditional formatting fragments as rows come and go: one rule becomes five, and the newest rows are covered by none of them.
12. Grouped sheets multiply every structural edit, and the only warning is [Group] in the title bar. Deleting a sheet, grouped or not, cannot be undone.

The lesson Harlow & Voss took from it was not about pensions and not really about SUM. It was that every range with a row number in it is a claim about how big the data will ever be, made on the day it was typed by somebody who had no way of knowing. F2:F15 was true for six years. It stopped being true on a Monday morning in January, in the ordinary course of the practice doing well enough to hire three people, and nothing about the file changed to mark the moment.

So the discipline is to stop making the claim. Convert the block to a Table and the range describes a column rather than a row count. Failing that, pad the range so far past the data that the claim is safe for years, and put one cell somewhere that checks it: =SUM(F:F)-2*F20, which should be zero and was 749.20 every month from January. And reconcile the file against what feeds it, not only against what it produces — because the three reconciliations that ran here all passed, all nine months, while money was being deducted from three people's pay and going nowhere.