How to fix #REF! after deleting rows or columns
#REF! inside the formula, and replace it with the correct cell or range.#REF! means a formula refers to a cell that isn't there anymore. Once Excel writes #REF! into a formula, the original address is gone, so the error won't fix itself when you add the row or column back.
Causes and fixes
- You deleted a row, column or sheet the formula used
- VLOOKUP asks for a column that isn't in the table
- A copied formula points off the edge of the sheet
- INDEX asks for a row or column outside its range
- Links to another workbook or a deleted sheet
1You deleted a row, column or sheet the formula used
How to recognise it
The formula itself contains #REF!, for example =B2+#REF!+B4 or =#REF!*1.1.
Fix
Press Ctrl+Z straight away if you can. Otherwise, edit the formula and type the right reference in place of #REF!.
To find every broken formula, press Ctrl+F, type #REF!, click Options, set Look in to Formulas and click Find All.
Prevent it. Add up ranges instead of single cells. =SUM(B2:B4) shrinks to =SUM(B2:B3) when you delete a row inside it. =B2+B3+B4 breaks. If you only need to empty a row, select it and press Delete to clear the contents rather than deleting the row.
2VLOOKUP asks for a column that isn't in the table
How to recognise it
=VLOOKUP(A2,$D$2:$E$50,3,FALSE) asks for the third column of a two-column range. This also happens after you delete a column inside the range, because the range shrinks but the number stays.
Fix
Fix the column number or widen the range. To stop it happening again, find the column by its heading:
=VLOOKUP(A2,$D$2:$F$50,MATCH("Price",$D$1:$F$1,0),FALSE)Or use XLOOKUP, which refers to the result column itself (Excel 2021 and Microsoft 365):
=XLOOKUP(A2,$D$2:$D$50,$F$2:$F$50)3A copied formula points off the edge of the sheet
Relative references shift when you copy. If B2 contains =A1*2 and you copy it to B1, the new formula would need the row above row 1, so you get =#REF!*2. The same happens near column A.
Fix
Check where the formula should point and lock references that must not move with $ signs ($A$1). Cutting and pasting a cell over cells a formula uses can also turn its references into #REF!, so use Insert or paste into empty cells.
4INDEX asks for a row or column outside its range
How to recognise it
=INDEX(A2:A10,12) asks for the 12th item of a 9-cell range.
Fix
Make sure the position is within the range, for example by checking it against ROWS:
=IF(E2>ROWS(A2:A10),"",INDEX(A2:A10,E2))INDIRECT and OFFSET can also return #REF! when the address they build doesn't exist, such as a sheet name that's been renamed.
5Links to another workbook or a deleted sheet
How to recognise it
The formula once pointed at another file or sheet, and that sheet was deleted.
Fix
Rebuild the reference to the right sheet. To review links to other files, use Data > Edit Links (called Workbook Links in newer Microsoft 365) where you can change the source or break the link.
How to stop #REF! coming back
- Use Excel tables (Insert > Table) and structured references like
Sales[Amount]. They follow the data when rows and columns move. - Use XLOOKUP or INDEX and MATCH instead of VLOOKUP column numbers.
- Use ranges in SUM, AVERAGE and similar functions rather than chains of
+. - Clear contents instead of deleting rows and columns that other sheets may use.
Still stuck?
- Search for
#REF!with Look in: Formulas to find every broken formula, not just the visible ones. - Check VLOOKUP column numbers against the width of the table range.
- Check INDEX row and column numbers against the size of the range.
- Use Formulas > Error Checking > Trace Error when a total shows #REF! but its own formula looks fine.
- If a sheet was deleted, an older saved version of the file may still have it: File > Info > Version History for files on OneDrive or SharePoint.