#REF! error in Excel: find the broken reference and fix it

#REF!
=SUM(B2,#REF!,C2)

Why it happens

A formula stores references to cells. When one of those references can no longer point to a real cell, Excel shows #REF! instead of a result. The Microsoft pages give these causes.

• A referenced row or column was deleted. Microsoft's example puts =SUM(B2,C2,D2) in column E, then deletes column C. The formula becomes =SUM(B2,#REF!,C2): column D has moved into C and is still counted, but the cell that was in C no longer exists. The #REF! page explains that when you list cells one by one, separated by commas, and delete one of them, "Excel can't resolve it, so it returns the #REF! error". That is the reason Microsoft gives for not recommending explicit cell references in functions.
• Cells were pasted over. The Detect formula errors page adds the case where you paste cells you moved on top of cells that other formulas referred to.
• VLOOKUP asks for a column the range does not have. In =VLOOKUP(A8,A2:D5,5,FALSE) the range A2:D5 has four columns, but the formula asks for the fifth. The VLOOKUP reference states the rule: if col_index_num is greater than the number of columns in the table array, you get #REF!.
• INDEX asks for a row or column outside its range. =INDEX(B2:E5,5,5) asks for row 5, column 5 of a range that is four rows by four columns. The INDEX reference says row_num and column_num "must point to a cell within array; otherwise, INDEX returns a #REF! error". In the reference form, area_num has the same requirement.
• INDIRECT points somewhere it cannot reach. According to the INDIRECT reference, INDIRECT returns #REF! when ref_text is not a valid cell reference, when it refers to another workbook that is not open, and when it refers to a range beyond the row limit of 1,048,576 or the column limit of 16,384 (XFD). The same page notes that external references are not supported in Excel Web App.
• OFFSET moves off the edge of the sheet. The OFFSET reference states: "If rows and cols offset reference over the edge of the worksheet, OFFSET returns the #REF! error value." Its own example, =OFFSET(D3, -3, -3), returns #REF!, because three rows above row 3 does not exist.

How to fix it

1. Undo a deletion you have only just made. Microsoft's advice for accidentally deleted rows or columns is to select Undo on the Quick Access Toolbar, or press Ctrl+Z, immediately. The rows or columns come back and the formulas point at them again.

2. Find every formula that contains #REF!. Use the Find feature described in Find or replace text and numbers on a worksheet: press Ctrl+F (or Home > Editing > Find & Select > Find), select Options >>, set Within to Workbook and Look in to Formulas, type the text below in Find what, and select Find All.

   #REF!
   

   Every occurrence is listed, and selecting one in the list selects its cell. Look in Formulas searches the text of your formulas, which is where a lost reference shows up as #REF!, as in =SUM(B2,#REF!,C2).

3. Rebuild a broken SUM, or any list of single cells, as a range. Replace the #REF! with the cell you meant, or switch to a range reference. Microsoft's fix for the example above is:

   =SUM(B2:D2)
   

   With a range, you can delete any column inside it and Excel adjusts the formula on its own. For a column of values, the same page gives =SUM(B2:B5).

4. Bring VLOOKUP's column number back inside the table. Either widen the table array to include the column you want, or lower the column number to one the table has. Both of these are valid versions of the broken example:

   =VLOOKUP(A8,A2:E5,5,FALSE)
   =VLOOKUP(A8,A2:D5,4,FALSE)
   

   Count columns from the first column of the table array, not from column A of the sheet. If VLOOKUP shows other symptoms too, the VLOOKUP not working guide goes through its #N/A, #VALUE! and wrong-value cases.

5. Bring INDEX's row and column numbers back inside the range. For a range of four rows by four columns, the largest valid position is row 4, column 4:

   =INDEX(B2:E5,4,4)
   

   If the row or column number comes from another formula, such as a MATCH, check what that formula returns: any number larger than the range's height or width gives #REF!.

6. Open the workbook that INDIRECT refers to. Microsoft's fix for INDIRECT pointing at a closed workbook is to open the referenced workbook. If the workbook is open and the error remains, check the text INDIRECT receives: it must form a valid reference, and it must not point past row 1,048,576 or column XFD. A reference built from text looks like this, where A2 holds a sheet name:

   =INDIRECT("'"&A2&"'!B5")
   

7. Keep OFFSET's result on the sheet. Change the rows and cols arguments so that the reference they produce stays within the worksheet. In Microsoft's examples, =OFFSET(D3,3,-2,1,1) is valid and returns the value in B6, while =OFFSET(D3, -3, -3) is not.

Check that it worked

• Run the same Find All search for #REF! with Look in: Formulas and Within: Workbook. It should find nothing.
• Select a repaired formula and go to Formulas > Formula Auditing > Trace Precedents. The formula relationships page says blue arrows show cells with no errors and "Red arrows show cells that cause errors." You can also select the cell and press F2 to see the colour-coded precedents for the arguments in the formula. Trace Precedents cannot follow a reference into a workbook that is closed.
• Go to Formulas > Formula Auditing > Error Checking to step through any errors left on the sheet. #REF! is one of the error values the error checking rules flag, and with background error checking on, an error cell has a triangle in its top-left corner.
• If you use Formulas > Formula Auditing > Evaluate Formula (Excel for Windows) to step through a repaired INDEX, OFFSET or INDIRECT formula, be aware that Microsoft lists these among the functions recalculated each time the worksheet changes, so the dialog can show results different from the cell.
• If a repaired lookup now returns #N/A for a value you can see in the table, the reference is fine and the problem is the data; see XLOOKUP returns #N/A when the value exists for hidden spaces and text-versus-number mismatches.

#REF! from linked workbooks, OLE and DDE links

Microsoft's #REF! page lists a few causes that involve links to other files or programs rather than a deleted cell:

• Dynamic array functions and closed workbooks. You get the same #REF! as with INDIRECT if a dynamic array function references a closed workbook. Open the workbook. Dynamic array formulas are the ones that spill their results into neighbouring cells, explained in the #SPILL! error guide.
• Structured references in linked workbooks. Structured references to table and column names in linked workbooks are not supported.
• Calculated references to linked workbooks. These are not supported either.
• OLE links. If an Object Linking and Embedding link returns #REF!, start the program that the link is calling.
• DDE topics. If a Dynamic Data Exchange topic returns #REF!, first check that you reference the correct topic. If the error continues, Microsoft points to the Trust Center settings for external content.

#REF! is Excel telling you that a reference no longer leads to a valid cell, so each fix above gives the formula a valid target again: undo the deletion while you still can, find the damaged formulas with a search on formulas, and replace the broken part with a range, a column or row number that fits, an open source workbook or an offset that stays on the sheet. Google Sheets also shows #REF!, but for IMPORTRANGE the cause is a missing permission rather than a deleted cell; that case is covered in IMPORTRANGE: "You need to connect these sheets".