# How to fix #REF! after deleting a column in Excel

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!.

**Excel:** `=SUM(B2:E2)` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F |
| --- | --- | --- | --- | --- | --- |
| Region | Q1 | Q2 | Q3 | Q4 | Total |
| East | 41200 | 38900 | 45100 | 47800 |  |
| West | 36500 | 40200 | 39800 | 42600 |  |
| North | 22800 | 24100 | 23500 | 26900 |  |
| South | 30100 | 28700 | 31400 | 33200 |  |

Formula in F2, filled to F5: `=SUM(B2:E2)`

Result: $173,000; $159,100; $97,300; $123,400

## 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!.

## Other ways

- `=SUMIF($B$1:$E$1,"Q*",B2:E2)`: Adds only the columns whose header starts with Q, so a notes column inserted in between is left out.

## Common mistakes

- `=B2+C2+D2+E2` becomes `=B2+#REF!+C2+D2` after 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`.

## 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.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/ref-error-after-deleting-a-column