A #REF! error simply means Excel has lost its reference. The formula was pointing to a cell, a range, or a sheet that no longer exists—maybe someone deleted a row, moved a column, removed a sheet, or pasted over a range the formula depended on. Excel can tell you something's broken, but it can't tell you what used to be there, why it mattered, or how to fix it.
Sales_2026
Summary
Budget ✕
Charts
H5
fx
=IFERROR(#REF!*Budget!#REF!,0)
A
B
C
D
E
F
1
Region
Product
Qty
Revenue
Budget
Variance
3
North
Widget Pro
145
£26,825
£25,000
£1,825
4
South
Widget Pro
98
£18,130
£20,000
-£1,870
5
West
Widget Pro
200
£37,000
#REF!
#REF!
6
East
Widget Pro
76
£14,060
#REF!
#REF!
Why this happened: The sheet "Budget" was deleted. All formulas referencing Budget!B5, Budget!B6 etc. are now broken — Excel replaces them with #REF!.
This is exactly where Claude shines. You paste in the broken formula, give a quick description of what the sheet is supposed to do, and Claude works backwards. It explains what the formula was trying to reference, why the reference broke, and what the corrected version should look like.
The three most common causes of #REF! errors:
🗑️
Deleted row or column
A formula referenced D5 and column D was deleted. Excel replaces it with #REF!
=SUM(#REF!:D20)
📄
Deleted sheet
A formula referenced Budget!B12 and the Budget sheet was removed.
=#REF!!B12*1.2
📋
Copy-paste overwrite
A formula was pasted into a cell that another formula depended on, wiping out the source.
=VLOOKUP(A2,#REF!,2,0)
What Claude does — broken formula vs fixed formula:
Broken formula (what you see)
=IFERROR(#REF! *Budget!#REF!, 0)
Returns: #REF!
Fixed formula (Claude's output)
=IFERROR(D5 *Targets!C5, 0)
Returns: £1,825
You paste the broken formula and Claude spots exactly which part is the #REF! reference
You describe what the formula was calculating and Claude reconstructs the correct version
You share a bit about the sheet structure and Claude tells you whether the error is isolated or cascading
Claude explains the fix in plain English so you actually understand it — not just copy and paste
Good to know: If the deletion was recent, try Ctrl+Z first. Undoing the change restores the original reference instantly — no formula rewriting needed.
Here's the six-step rhythm for diagnosing and fixing a #REF! error with Claude.
Ctrl+F
→
Search for:
#REF!
→
4 cells found with #REF! errors
Find every broken cell in seconds
01
Find all #REF! errors in the sheet
Press Ctrl+F and search for #REF! to locate every broken cell. Make a quick note of how many cells are affected — a single deleted column can easily trigger dozens of cascading errors.
02
Click the broken cell and copy the formula
Click the cell showing #REF!. Look up at the formula bar — you'll see the full formula with #REF! in place of the broken reference. Copy it exactly as it appears, including the #REF! text.
03
Gather context before going to Claude
Note: what column or row was recently deleted or moved? What is this formula supposed to calculate? If it was a cross-sheet formula, which sheet was it referencing?
04
Paste into Claude with context
Give Claude the broken formula, what the formula was supposed to do, and what changed in the sheet. Claude will identify the broken reference, explain what it was pointing to, and hand you the corrected formula.
05
Verify before fixing
Before you replace anything, check whether the column, row, or sheet that was deleted can be restored. If yes, restore it first — that fixes the error without rewriting the formula. If not, go with Claude's corrected version.
06
Check for cascading errors
After fixing the first one, press Ctrl+F again to see if other #REF! errors remain. One deleted column often breaks multiple formulas across the sheet.
Tip: If you have many #REF! errors caused by the same deletion, paste all the broken formulas into Claude at once. Claude can fix the entire batch in a single response instead of one at a time.
Copy these prompts directly into Claude. Fill in the bracketed placeholders with your specific formula and context.
Core diagnostic prompt
Prompt — paste into Claude
I have an Excel formula that is returning a #REF! error. Here is the broken formula as it appears in the cell:
[paste formula — e.g. =SUM(D2:#REF!)]
The formula is in cell [cell reference, e.g. E5] on sheet [sheet name].
This formula is supposed to [describe what it calculates — e.g. "sum all revenue values in column D from rows 2 to 20"].
Recently, [describe what changed — e.g. "I deleted column C" or "someone removed the sheet called Budget"].
Please: (1) identify which part of the formula is the broken reference, (2) explain what it was originally pointing to, (3) give me the corrected formula.
Cascading errors prompt
Prompt — paste into Claude
I have fixed one #REF! error but there are still [X] more #REF! errors in the sheet. Here are the other broken formulas:
[paste all broken formulas]
All of these formulas are on sheet [name]. The change that caused the errors was [describe the change].
Please identify the broken reference in each formula and provide the corrected version.
Cross-sheet reference prompt
Prompt — paste into Claude
I have a #REF! error in a formula that was referencing another sheet. The broken formula is:
[paste formula — e.g. =Budget!#REF!*1.2]
The sheet it was referencing was called [sheet name] and it has been deleted. The formula was supposed to [describe purpose]. Is there a way to rewrite this formula to work without that sheet, or do I need to recreate the sheet?
Fix checklist
All #REF! errors located using Ctrl+F
Broken formula copied from the formula bar exactly as it appears
Context noted: what was deleted, moved, or overwritten
Claude prompt sent with formula + context + description of purpose
Corrected formula verified in Excel before applying to all affected cells
Cascading errors checked after first fix with Ctrl+F
Undo option considered (Ctrl+Z) if the deletion was recent
Important: Claude reconstructs the formula based on what you describe. Always verify the corrected formula against your actual data before applying it across multiple cells.