Why does my formula show an error?

Open the linked error pages (#N/A, #VALUE!, #REF!) from our directory. Most formula posts fail because of data type mismatches, deleted references, or wrong match modes — not because the function name is wrong.

Should I copy formulas down or use a Table?

Copying down is fine for one-off models. Convert the range to an Excel Table when you add rows often — structured references stay readable and break less.

Formulas - 2026-01-21

How to Fix #REF! Error in Excel

The #REF! error means a cell reference is invalid. Learn why it happens and how to fix it.

What is the #REF! Error?

The #REF! error appears when Excel can't find a cell reference—usually because cells were deleted, moved, or the reference points to something that no longer exists. For all Excel errors, see Common Excel Errors Explained.

Quick Reference: #REF! Error Causes

Cause Example Solution Deleted cells =A1+#REF!+C1 Undo or rebuild formula Cut and paste Reference to moved cell Use Copy instead of Cut Invalid column index VLOOKUP col 5 in 3-col range Use valid column number Broken external links ='[OldFile.xlsx]Sheet1'!A1 Update or break links Invalid INDIRECT =INDIRECT("BadSheet!A1") Fix sheet/cell name Circular reference Cell references itself Restructure formula

Common Causes (Detailed)

Deleted Rows or Columns This is the #1 cause of #REF! errors. When you delete a row or column that's referenced in a formula, Excel replaces that reference with #REF!. Before deletion: \\\ ' Cell D1 contains: =A1+B1+C1 ' Result: 30 (if A1=10, B1=10, C1=10) \\\ After deleting column B: \\\ ' Cell C1 now contains: =A1+#REF!+B1 ' Result: #REF! \\\ Real-world scenario: You have a budget spreadsheet with monthly columns. Someone deletes "February" column to "clean up" the sheet. Now every formula that referenced February shows #REF!. Solutions: Immediate fix: Press Ctrl+Z to undo (works if you catch it quickly) Manual fix: Find and replace #REF! with correct references Prevention: Use Excel Tables or named ranges (see below) Cut and Paste Operations Cutting cells (Ctrl+X) behaves differently than copying (Ctrl+C). When you cut a cell that's referenced by a formula and paste it elsewhere, the original reference breaks. Example scenario: \\\ ' A1 contains: 100 ' B1 contains: =A1*2 (result: 200) ' You cut A1 and paste to D1 ' B1 now shows: #REF! \\\ Why this happens: Copy (Ctrl+C) leaves the original cell intact Cut (Ctrl+X) removes the original cell, breaking references Best practice: \\\ ' Instead of Cut → Paste: Copy the cell (Ctrl+C) Paste to new location (Ctrl+V) Delete original cell only if no formulas reference it \\\ Invalid VLOOKUP/HLOOKUP Column Index When the column index in VLOOKUP (or row index in HLOOKUP) exceeds the number of columns in your range, you get #REF!. Example: \\\ ' Your data range B2:D10 has 3 columns =VLOOKUP("Product A", B2:D10, 5, FALSE) ' Error: Column 5 doesn't exist in a 3-column range! \\\ Common mistakes: Formula Range Columns Index Result =VLOOKUP(A1, B:D, 3, FALSE) 3 3 ✓ Works =VLOOKUP(A1, B:D, 4, FALSE) 3 4 #REF! =VLOOKUP(A1, B:E, 5, FALSE...

Step-by-Step: Finding and Fixing All #REF! Errors

Step 1: Find All Errors \\\ Ctrl+F (Find dialog) Find what: #REF! Within: Workbook (to search all sheets) Click "Find All" \\\ This gives you a list of every cell containing #REF!. Step 2: Categorize the Errors Before fixing, understand what caused each error: Error Location Likely Cause Fix Approach In formula itself Deleted cells Rebuild formula In external reference Broken link Update/break link In VLOOKUP/INDEX Invalid index Correct the number In INDIRECT Bad reference string Fix the text Step 3: Fix Systematically For deleted cell references: \\\ ' Original broken formula: =SUM(A1:A5)+#REF!+SUM(C1:C5) ' Identify what #REF! should be (was it B column?) ' Rebuild: =SUM(A1:A5)+SUM(B1:B5)+SUM(C1:C5) \\\ For VLOOKUP errors: \\\ ' Broken: =VLOOKUP(A1, Data, 10, FALSE) ' 10 exceeds columns ' Fixed: =VLOOKUP(A1, Data, 3, FALSE) ' Use valid column \\\ Step 4: Verify Fixes After fixing, run Find again to confirm no #REF! errors remain.

Prevention Strategies

Strategy 1: Use Excel Tables Excel Tables automatically adjust references when rows/columns are added or deleted. \\\ ' Regular formula (fragile): =SUM(B2:B100) ' Table formula (robust): =SUM(SalesTable[Revenue]) ' Deleting rows won't cause #REF! \\\ Creating a table: Select your data Press Ctrl+T Check "My table has headers" Use structured references in formulas Strategy 2: Use Named Ranges Named ranges are more resilient than cell references. \\\ ' Define a named range: ' Name: MonthlySales ' Refers to: =Sheet1!$B$2:$B$13 ' Use in formulas: =SUM(MonthlySales) =AVERAGE(MonthlySales) \\\ Benefits: Formulas are more readable References update when range moves Less likely to break from deletions Strategy 3: Protect Critical Cells Prevent accidental deletion of cells that formulas depend on. Select cells that shouldn't be deleted Right-click → Format Cells → Protection Check "Locked" Review → Protect Sheet Strategy 4: Use IFERROR for Graceful Handling Even with prevention, errors can happen. Use IFERROR to handle them gracefully: \\\ ' Instead of: =VLOOKUP(A1, Data, 3, FALSE) ' Use: =IFERROR(VLOOKUP(A1, Data, 3, FALSE), "Not found") \\\ Learn more: IFERROR Function →

Real-World Scenarios

Scenario 1: Shared Workbook Chaos Problem: Multiple people edit a shared budget file. Someone deletes a column, breaking 50+ formulas. Solution: Immediately check if anyone can Undo (Ctrl+Z) If not, restore from backup/version history Implement protection on critical columns Convert to Excel Table for future resilience Scenario 2: Template with Dynamic References Problem: A report template uses INDIRECT to reference different sheets. When a sheet is renamed, formulas break. Solution: \\\ ' Add error handling: =IFERROR(INDIRECT(A1&"!B10"), "Sheet not found: "&A1) ' Or validate sheet exists first: =IF(ISREF(INDIRECT(A1&"!A1")), INDIRECT(A1&"!B10"), "Invalid sheet") \\\ Scenario 3: VLOOKUP in Growing Dataset Problem: VLOOKUP column index becomes invalid when columns are inserted. Solution - Use MATCH for dynamic column: \\\ ' Instead of hardcoded column 3: =VLOOKUP(A1, Data, 3, FALSE) ' Use MATCH to find column dynamically: =VLOOKUP(A1, Data, MATCH("Price", Headers, 0), FALSE) ' Or switch to INDEX/MATCH: =INDEX(PriceColumn, MATCH(A1, ProductColumn, 0)) \\\

Quick Troubleshooting Checklist

When you see #REF!, check these in order: [ ] Did you recently delete any rows or columns? → Undo (Ctrl+Z) [ ] Did you cut and paste cells? → Rebuild references [ ] Is it a VLOOKUP/HLOOKUP? → Check column/row index [ ] Does formula reference another file? → Check Data → Edit Links [ ] Is INDIRECT involved? → Verify the text creates valid reference [ ] Is it a circular reference? → Check Formulas → Circular References

Related Resources

Common Excel Errors Explained Why Excel Shows #VALUE! Error #N/A Error: Causes and Solutions VLOOKUP Function → INDEX Function → INDIRECT Function → IFERROR Function → Error Reference →

Formula debugging workflow

Step Action 1 Select the cell and read the formula in the formula bar 2 Use Formulas → Evaluate Formula for nested functions 3 Check for text stored as numbers (green triangle) 4 Fix errors using our formula error guide Go deeper: Compare similar functions · Fix common problems

Frequently asked questions

Why does my formula show an error? Open the linked error pages (#N/A, #VALUE!, #REF!) from our directory. Most formula posts fail because of data type mismatches, deleted references, or wrong match modes — not because the function name is wrong.
Should I copy formulas down or use a Table? Copying down is fine for one-off models. Convert the range to an Excel Table when you add rows often — structured references stay readable and break less.