You deleted a column in your Excel worksheet thinking it isn’t required anymore. Suddenly you see #REF! showing up in many cells in the spreadsheet. Then you start to wonder, what does #REF! mean in Excel?

Microsoft Excel uses various error messages to inform you that calculated fields in your dataset have errors that need immediate attention. If you don’t resolve those, Excel is unable to get the desired value you want. Some of these errors are #SPILL!, Circular References, #DIV/0, and so on.

However, the most common one is #REF!. That’s because all the Excel functions require referencing to a cell for input values. These formulas start with at least two referenced cells to hundreds of cells. Now, if any of these linked cells go missing, Excel won’t be able to calculate the target cell. Here comes #REF! error!

Read on to learn everything you need to know about #REF! notation in Excel so you can keep your worksheet error-free.

What Does #REF! Mean in Excel?

In Excel, the error code #REF! stands for value or cell Reference Error. It occurs when a cell reference isn’t valid or is pointing to a cell that no longer exists. This could happen due to several reasons, such as deleting a referenced cell or range, moving cells, renaming sheets, or using incorrect cell references in formulas.

When you see #REF! in a cell, it signifies that Excel can’t locate the referenced cell or range, leading to a breakdown in the formula calculation. This error can propagate through other cells that depend on the erroneous reference, potentially impacting multiple calculations within a worksheet.

The #REF! error is critical because it disrupts the integrity of formulas and can lead to incorrect results or dysfunctional spreadsheets. It’s essential to address this error promptly to ensure the accuracy of your data analysis and decision-making processes.

To resolve #REF! errors, you typically need to identify the source of the invalid reference and correct it. This might involve reviewing and updating formulas, ensuring that referenced cells exist and are in the correct location, or using alternative methods such as named ranges to prevent future errors.

When Do You See the #REF! Error in Excel?

Find below various scenarios where you might expect the #REF! error:

Deletion of a Column

If you see a #REF! error in a cell containing a formula, it’s likely that one of the cell references of that formula is missing.

For example, in the above dataset, the value of C2 is calculated by multiplying A2 and B2. This formula has been copied to the rest of the cells down column C until C10.

If I delete column B in the dataset, I get #REF! errors in all the cells in column C.

Using VLOOKUP Function

VLOOKUP is one of the most popular Excel functions where you often get the #REF! error if you don’t input the arguments carefully.

Firstly, if you incorrectly mention the column index number one point above the actual number, you shall see the reference error.

For example, in the above dataset, there are only three columns for referencing in the VLOOKUP function.

However, if you enter 4 instead of 2 (to find age) or 3 (to find city), you shall get the #REF! error.

Secondly, if you delete the row containing the lookup value, Excel shall also produce this error because the lookup value is a mandatory argument for this formula.

Using INDEX Function

When you’re using the INDEX function in your worksheet, be mindful to enter the row number and column number accurately or you’ll end up producing a reference error in the target cell.

For instance, in the above dataset, I’m trying to populate the total revenue for the North region using the INDEX function.

The appropriate formula would be as outlined below:

=INDEX(B2:E5,2,4)

However, when writing this formula, if I mistakenly put 5 in place of a row or column number argument, Excel can’t calculate the cell due to a reference error.

You only get the reference error when you go beyond the maximum permissible limit for row and column numbers. If you put 1 or 3 as row or column numbers, in this example, Excel does calculate the target cell but generates incorrect results. In the current example, the maximum limit for rows and columns is 4.

Using INDIRECT for Closed Workbooks

You often use the INDIRECT function when you need to dynamically reference cell addresses or ranges within a formula. This function takes a text string as an argument and interprets it as a reference to a cell or range. Therefore, you can create formulas that adapt to changes in the spreadsheet structure or input data.

However, if one of the arguments in the INDIRECT function is from another workbook on your PC, then you might see the #REF! error. The error surfaces when you close the workbook mistakenly or it gets deleted from the shared server.

In other formulas, you can see the #REF! error in the Formula bar when you highlight the target cell. In this case, you don’t see such an error note in the formula.

You need to consider that the #REF! error is due to a closed workbook and verify that by swapping through all the open tabs or windows in your PC.

Structured References

Structured references in Excel are a feature that allows you to refer to tables and their components, such as headers, columns, and rows, in a structured and dynamic way. These references are particularly useful for formulas and data analysis because they automatically adjust as the structure of the table changes.

If you delete any column or rows referred to in any of these structured references in a table, you’ll get the #REF! error as shown in the screenshot given above.

Calculated References

Calculated references, also known as calculated fields or calculated columns in Excel, are formulas that you apply within a table to generate dynamic values based on other data within the table.

These formulas can reference other columns in the same row or even perform calculations across multiple rows within the table.

However, like any formula in Excel, calculated references can also result in #REF! errors under these circumstances:

• Columns have been renamed
• Rows or columns have been deleted
• The table has been moved
• There are circular references

Object Linking and Embedding (OLE) Issues

Object Linking and Embedding (OLE) is a technology that allows you to link or embed objects from one application into another in the Microsoft 365 suite of apps.

In the context of Excel, OLE enables you to insert objects, such as charts, graphs, or entire documents, from other programs like Word or PowerPoint directly into Excel spreadsheets.

If any of the following issues occur with the source data, you’ll get #REF! error due to OLE:

• The source document for charts or other objects has been moved or deleted
• OLE objects have become corrupted

Dynamic Data Exchange (DDE) Issues

Dynamic Data Exchange (DDE) is a technology that allows Microsoft Windows-based applications to share data and commands dynamically. In the context of Excel, DDE enables the communication between Excel and other applications, such as databases or trading platforms, to exchange data in real-time.

DDE can generate #REF! error in situations like broken links, invalid references, corrupted data, or 3rd party application errors.

Deleting or Renaming a Linked Workbook

Suppose, you’ve created an external reference to a workbook within a formula in your current worksheet. Now, the referenced workbook gets deleted or renamed.

Then, you get the #REF! error when you reopen the current worksheet or click the Refresh All command button in the Data tab.

Renaming Worksheets

Let’s imagine, you’ve created a formula by referencing a cell in another worksheet of the same workbook. After a few days, you rename the referenced worksheet. In this situation, you can get an invalid reference error in your dataset.

In the above example, I referenced column A in Sheet8 of the same workbook. Then, I renamed Sheet8 to Sheet to get the #REF! error.

Copying and Pasting Cells

When copying cells from one range and pasting them into another range with a different size or shape, Excel might generate the #REF! errors.

For example, if you copy a formula from a range of cells that references adjacent cells and paste it into a range where those adjacent cells don’t exist, Excel will return #REF! errors because it cannot resolve the references.

How to Find #REF! Errors in Excel

Find below the techniques to locate multiple #REF! errors in your worksheet in a few clicks:

Using Find & Replace

The Find & Replace is the best tool to locate all the #REF! errors in the whole workbook.

Open your worksheet and press Ctrl + F to launch the Find & Replace dialog.

Type #REF! in the Find what field. Click the Within drop-down and switch that to Workbook. The Search field should be set to By Rows, and the Look in field should be Formulas.

Click the Find All button.

Excel shall create a menu below the Find & Replace tool to show all the instances of the invalid reference errors in your workbook along with the complete syntax of the formulas used in those instances.

You can double-click on any of the links of these search results to instantly visit the target cell containing the reference error.

Using Go To

Another cool way to highlight the #REF! error is the Go To dialog. However, this feature only works on a worksheet and not the whole workbook.

Go to the worksheet that you’d like to scan for invalid reference errors. Press Ctrl + G to launch the Go To dialog.

On Go To, click Special to open the Go To Special dialog. Select the Formulas option and uncheck all the checkboxes below the Formulas category except for the Errors checkbox.

Click OK to highlight all the formula errors in the worksheet including the #REF! ones.

Using Excel VBA

If you need to automate the task of finding all #REF! errors in Excel, you can use the following VBA script:

Sub ListRefErrors()
    Dim ws As Worksheet
    Dim r As Range
    Dim newWs As Worksheet
    Dim i As Long

    ' Create a new worksheet
    Set newWs = ThisWorkbook.Sheets.Add
    newWs.Name = "ErrorList"
    i = 1

    ' Loop through each worksheet
    For Each ws In ThisWorkbook.Sheets
        If ws.Name <> "ErrorList" Then
            ' Loop through each cell
            For Each r In ws.UsedRange
                ' Check if cell has #REF! error
                If IsError(r.Value) Then
                    If r.Value = CVErr(xlErrRef) Then
                        ' Write the cell address and worksheet name to the new worksheet
                        newWs.Cells(i, 1).Value = ws.Name
                        newWs.Cells(i, 2).Value = r.Address
                        i = i + 1
                    End If
                End If
            Next r
        End If
    Next ws
End Sub

The macro created using the above script scans the whole workbook for #REF! errors. Then, it creates a list of all such errors in the ErrorList worksheet.

To learn how to use the script to develop a macro, read this article:

📒 Read More: How To Use The VBA Code You Find Online

How to Fix #REF! in Excel

You can locate the invalid reference error using any of the methods mentioned earlier. As soon as you select the cell containing the #REF! error, Excel shall show the whole formula syntax in the Formula bar.

Identify the argument that’s showing the reference error and replace that with a valid reference.

For example, in the above dataset, the #REF! error appears in place of the lookup value of the VLOOKUP formula.

So, I created a row above the cell and entered the text string James inside the F1 cell.

I’ve also updated the new cell reference in the VLOOKUP formula to reflect the changes and fix the invalid reference error.

Also, follow these tips and tricks to avoid invalid reference errors in your Excel workbooks:

• Ensure that all cell references in formulas accurately point to the intended cells.
• Instead of directly referencing cell addresses, assign names to ranges of cells. This reduces the risk of errors when you move or delete cell ranges.
• Be cautious when making structural changes to your data.
• If your spreadsheet includes external workbook or server links, ensure they are valid and accessible. Broken links can trigger #REF! errors.
• Lock cells that contain critical data or formulas to prevent accidental changes.

Conclusions

If you’re here, congratulations! You’ve understood the #REF! error just enough to ensure no inaccurate formulas or values slip through your cautious eyes.

You can now identify and fix invalid reference errors without waiting for someone else to assist you. Also, you’ve discovered the secret tips and tricks to avoid #REF! error in your spreadsheets.

Dif the article help you to understand and fix #REF! errors? Do you know better tactics than the ones mentioned here? Did I miss any point you’ve been looking for? Don’t forget to drop a few lines in the comment box.