What does #REF! mean in Excel?

Ask Mini NOVA, then repair the reference

Leave the broken cell and the formula bar on screen. These checks are the ones Microsoft Support gives for a #REF! error.

1. Leave the error on screen. Keep the message, the formula bar, or the settings page in view. Mini NOVA captures the screen when you submit the question, so scroll until the error and a little of the window around it are visible.
2. Press Insert and type your question. Press Insert, then type what you want to know. Mini NOVA sees that screen and guides you step by step. You make the click. There is no server or browser extension to set up, and no Microsoft 365 plan is needed.
3. Change one thing, then look again. You make the change. If the error is still there, ask a follow-up with the new screen visible. Mini NOVA remembers the conversation context. There is no chat history list.
4. Undo the deletion if you just made it. If you deleted a row, column, or cells that the formula used, press Ctrl+Z, or use Undo on the Quick Access Toolbar, before you do anything else. That restores the cells the formula still expects.
5. Replace #REF! in the formula bar. When a referenced column is deleted, a formula such as =SUM(B2,C2,D2) can become =SUM(B2,#REF!,C2). Select the cell, delete the #REF! token in the formula bar, and type a cell that still exists. A range such as =SUM(B2:D2) adjusts itself when a column inside the range is deleted, which is why Microsoft Support prefers a range over a list of single cells.
6. Check a lookup column number. VLOOKUP returns #REF! when the column number is larger than the range. =VLOOKUP(A8,A2:D5,5,FALSE) asks for column 5 of a range that only has four columns. Widen the range, or lower the column number so it falls inside the range. INDEX does the same thing when the row or column number sits outside its range.
7. Open a workbook the formula still needs. INDIRECT, and a dynamic array formula, can return #REF! when they point at a workbook that is closed. Open that workbook and calculate again. A sheet that has been deleted cannot be recovered from the formula. You have to point the formula at a sheet that is still there.

If it is a different error

#N/A from VLOOKUP means the lookup did not find a match. #VALUE! means a value is the wrong kind. #SPILL! means the results have nowhere to land. Ask about whichever code is actually on the sheet, from the spreadsheet guide.

Sources

Checked on 9 Oct 2026. Support pages change, so open the source before you rely on a step. Mini NOVA is not affiliated with Microsoft. Questions about the account itself go to admin@deskwithnova.com.

• How to correct a #REF! error (Microsoft Support)
• How to avoid broken formulas (Microsoft Support)

FAQ