How to Fix #REF! Error in Excel (6 Solutions)

Understanding the #REF! Error in Excel

The #REF! error occurs when a cell reference becomes invalid or nonexistent. Here are the common scenarios that lead to this error:

1. Deletion of Rows, Columns, or Sheets:
   • If you delete a row, column, or an entire sheet containing referenced cells, Excel can’t find the expected data, resulting in a #REF! error.
   • Solution: Undo the deletion (Ctrl+Z) or rewrite the formula with corrected references.
2. Incorrect or Invalid Cell References:
   • Providing incorrect or invalid cell references within a formula can trigger the #REF! error.
   • Solution: Double-check your references and ensure they point to valid cells.

Dataset Overview

Let’s start by understanding our dataset, which represents sales for different salespersons across various years.

Solution 1 – Correct Cell Reference

• Suppose we’re summing up total sales for each salesperson using a formula.

• If we delete Column C (which contains referenced data), the #REF! error appears.

Solution:

• Undo the deletion (Ctrl+Z) or rewrite the formula with accurate references.

Solution 2 – Tab Reference

• Imagine we’re calculating total sales using a reference to another sheet named “2019.”

• If we delete the “2019” sheet, the #REF! error occurs.

Solution:

• Unfortunately, you can’t recover a deleted sheet.
• Close the Excel file (if unsaved) and reopen it or remove the reference to the deleted sheet.

Read More: How to Find Reference Errors in Excel

Solution 3 – Cutting and Pasting Issues

• When we cut and paste data into Column C, the #REF! error may appear.

Solution:

• Undo the operation (Ctrl+Z) or copy instead of cutting to avoid the error.

Solution 4 – Relative Reference 

• Suppose we add an extra amount in Cell E6 using the reference G1.

• Copying Cell E6 to G5 results in a #REF! Error because there’s no cell above G1.

Solution:

• Use absolute references for such cells, then copy the formula.

• The error is no longer present.

Solution 5 – VLOOKUP Invalid Reference

• If we use the VLOOKUP function with an incorrect column index number (e.g., 5 when there are only 4 columns), the #REF! error occurs.

The reason is that the incorrect column index number – 5 was used. The array has only 4 columns.

Solution:

• Provide the correct column index (e.g., 2, 3, or 4) to fix the error.

Solution 6 – Correcting INDIRECT Invalid Reference to Eliminate #REF! Error

• In your workbook, you’ve utilized the INDIRECT function along with cell references from another sheet named ‘Sales.’

If you encounter a #REF! error after closing that workbook, follow these steps:

• Perform another operation, such as calculating the sum total for every year. You will see that the previous formula caused #REF! error.

Solution:

• Reopen the workbook containing the ‘Sales’ sheet to resolve the #REF! error.

How to Clear Multiple #REF! Errors

• Suppose you have several #REF! errors in column E, and you want to remove them all at once.

Steps:

• Select the entire column.
• Press Ctrl+H to open the Find and Replace dialog box.
• In the Find what box, type #REF!.
• Leave the Replace with box empty.
• Click Replace All.

• Excel will remove all instances of the #REF! error from the selected column.

Download Practice Workbook

You can download the practice workbook from here:

Related Articles

• How to Remove Error in Excel
• How to Correct a Spill (#SPILL!) Error in Excel
• [Fixed!] ‘There Isn’t Enough Memory’ Error in Excel
• [Fixed] Excel Print Error Not Enough Memory

<< Go Back To Excel Formula Errors | Errors in Excel | Learn Excel