#REF! Error in Excel (Understanding and Fixing)

puneet-gogia-excel-champs

- Written by Puneet

Let’s say, you have a sales report where cell B2 has the total sales formula =SUM(A2:A10). If you delete row 3, the formula changes to =SUM(A2:#REF!).

Now, you see a #REF! Error because the reference to row 10 is broken. Understanding this error is key to fixing it. Update the formula to =SUM(A2:A9) to reflect the new range.

What Does #REF Mean in Excel

The #REF! Error in Excel means a formula is trying to use cells or a range of cells that don’t exist. It happens when you delete cells or use invalid cell references. To fix it, check your formula for missing or wrong cell references. Update or correct the formula to use the right cells.

Reasons for Having #REF!

  1. Deleted Cells or Columns – When a formula references a cell or column that has been deleted.
  2. Invalid Cell References – When a formula refers to a cell reference that does not exist.
  3. Copying Formulas with Absolute References
  4. VLOOKUP with Incorrect Column Index – Using a column index number larger than the number of columns in the lookup range results in this error.
  5. INDIRECT Function with Invalid References – When the text string in INDIRECT refers to a deleted or non-existent cell or range.
  6. Closed Workbook – When a workbook referenced by a formula is moved, deleted, or closed.
  7. Copying and Pasting Across Workbooks – If you copy a formula referencing cells in another workbook, the destination workbook doesn’t have those cells.

Copied Formula to a New Location

The example below shows the sum formula in cell A5, which sums values from the range A1:A5. You have relative cell reference in the formula.

ref-error-in-excel

And when you copy and paste this formula to cell C3, it returns #REF! in the result.

returns-ref-error

As I said earlier, when you copy the formula from A6 to C3, C3 doesn’t have five cells above, which means it’s impossible to refer to five cells from above.

#REF! Error with VLOOKUP

REF! error with VLOOKUP usually occurs when the formula references a column number outside the lookup table’s range.

While using VLOOKUP, specify the column from which you want to get the value.

ref-error-with-vlookup

In the above example, we have specified three as col_index_num in the formula. But, in the lookup table, we have only two columns, and that’s why we have the #REF! Error in the result.

ref-error-in-the-result

You might face the same thing while using INDEX. When you try to look for a row or a column number that is higher than the total number of columns or rows you have in the referred data, this returns a #REF! Error in the result.

ref-error-while-using-index

In the above example, we are looking for the value in the 7th row in the range, but we have only 6 cells in total.

#REF! Error When You Delete a Column

When you delete a column in Excel, any formulas that reference cells in that column can result in #REF! Error. In the example below, we have referred to the range A1:C3 in three parts.

ref-error-when-delete-a-column

But if you delete any of these columns, the formula will return a #REF! Error in the result.

formula-returns-ref-error

#REF! Error with INDIRECT Function

INDIRECT function returns a reference specified by a text string. A #REF! error can occur if the text string refers to a range or cell that doesn’t exist, often because the referenced cell or range was deleted or never existed, or if you are referring to a cell or range from a closed workbook.

There’s a chance that you will get the #REF! Error in the result.

ref-error-with-indirect-function

Fixing the #REF! Error

In Excel, when you are working formulas, it’s better not to trap the #REF! Error with the ISERROR or any other function. It’s better to fix the error when you get it in the result.

In most cases, you need to fix the reference to the range or cell, and your formulas will work fine. But it can be tricky sometimes to know exactly which reference is deleted or removed.

For example, when you delete a sheet from a workbook, it’s impossible to undo that action, and it’s harder to replace those references in the formula.

Using Trace Precedents – First, select the cell with the #REF! error, then go to the Formulas tab. Click on Trace Precedents to see which cells the formula is referencing. This helps identify where the broken cell reference is.

Finding All the #REF! Errors

Using Find and Replace Option

With the help of Excel’s FIND option, you can find and select the cells in which you have the #REF! Error.

  1. Open the Find and Replace using Crtl + F.
  2. Enter the value #REF! in the “Find What”.
  3. Click on the “Options” button.
  4. Select “Values” from the “Look in”.
  5. Click on the “Find All” button.
find-all-ref-errors

After that, you can go to each cell and fix the formula.

Using Conditional Formatting to Highlight Cells with #REF!

Alternatively, you can also use conditional formatting to apply a color to all the cells in the worksheet where you have an #REF! Error.

  1. To start with, you can select the entire sheet by clicking the box at the top left corner of the worksheet or pressing Ctrl + A.
  2. Now, go to the Home tab on the Ribbon, click on Conditional Formatting in the Styles group, and choose New Rule.
  3. After that, select “Use a formula to determine which cells to format”. And in the formula box, enter =ISERROR(A1). Click on the Format button, choose a fill color (e.g., Light Red), and click OK.
  4. Click OK again to apply the rule.

Use a VBA Code

Sub HighlightRefErrors()
    Dim ws As Worksheet
    Dim cell As Range
    Dim errorCount As Integer
    
    ' Initialize error count
    errorCount = 0
    
    ' Set the worksheet to the active sheet
    Set ws = ActiveSheet
    
    ' Loop through each cell in the worksheet
    For Each cell In ws.UsedRange
        ' Check if the cell has a #REF! error
        If IsError(cell.Value) And cell.Value = CVErr(xlErrRef) Then
            ' Highlight the cell with yellow color
            cell.Interior.Color = vbYellow
            ' Increment the error count
            errorCount = errorCount + 1
        End If
    Next cell
    
    ' Show a message box with the count of #REF! errors
    MsgBox "The sheet contains " & errorCount & " #REF! errors.", vbInformation
End Sub

Get the Excel File

Last Updated: May 18, 2024

Leave a Comment