How to fix #REF! after deleting a column in Excel
Formula (Excel and Google Sheets)
=SUM(B2:E2)Use a range instead of separate cell references: =SUM(B2:E2) shrinks when a column is deleted, while =B2+C2+D2+E2 turns into #REF!.
- Result on the example
- one result per row in
F2, filled down toF5 - Works in
- Every current version
- Google Sheets
- Same formula
See it work
The formula is already in F2, filled down to F5. Change any input and the result updates, or edit the formula to experiment.
How it works
When you delete a column inside a range like B2:E2, the range shrinks to the columns that remain and SUM keeps working. A formula that names each cell, like =B2+C2+D2+E2, loses the deleted cell and shows #REF! in its place, and the whole result becomes #REF!.
=SUM(B2:E2)Result173000
B2:E21 row × 4 cols: $41,200, $38,900, $45,100, $47,800A range: 4 cells in one row.
SUM(B2:E2)173000SUM: Adds numbers and ranges. Skips text and blanks.- number1
B2:E21 row × 4 cols: $41,200, $38,900, $45,100, $47,800
- number1
In Google Sheets
The same formula works in Google Sheets: =SUM(B2:E2). We ran it in Google Sheets mode on the same data and it returns the same results.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =SUMIF($B$1:$E$1,"Q*",B2:E2) | $173,000, $159,100, $97,300… | Adds only the columns whose header starts with Q, so a notes column inserted in between is left out. |
Common mistakes
=B2+C2+D2+E2becomes=B2+#REF!+C2+D2after deleting column C, and the #REF! can't be repaired automatically.- Undo (Ctrl+Z) straight after the deletion brings back the column and the formulas.
- A range grows only when you insert inside it. A column added after E isn't included in
B2:E2.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Fix #REF! after deleting a column
F2, total the four quarters with a formula that survives a deleted column. Fill down to F5.Questions
How do I find every #REF! in a workbook?
Use Find (Ctrl+F), look in Formulas, and search for #REF!.
Why can't Excel fix the reference itself?
The cell it pointed to no longer exists. A range can shrink, but a single reference has nothing left to point at.
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.