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.

Fix #REF! with Correct Cell Reference in Excel

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

Fix #REF! with Correct Cell Reference in Excel

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.”

Fix Tab Reference to Remove #REF! Error in Excel

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

Fix Tab Reference to Remove #REF! Error in Excel

Fix Tab Reference to Remove #REF! Error in Excel

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.

Fix Cutting and Pasting Issues to Delete #REF! Error in Excel

Fix Cutting and Pasting Issues to Delete #REF! Error in Excel

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.

Fix Relative Reference to Delete #REF! Error in Excel

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

Fix Relative Reference to Delete #REF! Error in Excel

Solution:

Fix Relative Reference to Delete #REF! Error in Excel

  • 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.

Fix VLOOKUP Invalid Reference to Erase #REF! Error in Excel

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

Fix VLOOKUP Invalid Reference to Erase #REF! Error in Excel

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.’

Fix INDIRECT Invalid Reference to Erase #REF! Error in Excel

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

Fix INDIRECT Invalid Reference to Erase #REF! Error in Excel

  • 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.

How to Clear Multiple #REF! Errors

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 workbooks from here:


Related Articles


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

Get FREE Advanced Excel Exercises with Solutions!
Md. Sourov Hossain Mithun
Md. Sourov Hossain Mithun

Md. Sourov Hossain Mithun, an Excel and VBA Content Developer at Softeko's ExcelDemy project, joined in October 2021. Holding a Naval Architecture & Marine Engineering degree from BUET, Mithun showcases expertise during his two-year tenure. With over 200 published articles on Excel topics, he earned a promotion to Team Leader, excelling in leading diverse teams. Mithun's passion extends to Advanced Excel, Excel VBA, Data Analysis, and Python programming, contributing significantly to the innovative and dynamic environment of ExcelDemy... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo