How to Fix Reference Error in Excel

A #REF error is a message that Excel sends when the formula references a cell that is no longer available, maybe because it has been deleted. If you have worked with formulas in Excel, you have likely come across this error. It appears when the formula has no idea where to point to as it was earlier deleted. You have a data set and use simple arithmetic on the operator. If you delete one column, the formula ceases to have a reference for the deleted cell. Let us see how to find and fix the #REF Excel errors.

Table of Contents

How to find the #REF! Excel errors

Reference errors are quite common; it is, therefore, important that you do a quick check before using a dataset in calculations. It minimizes the possibility of having #REF! Errors in Excel.

Follow the simple steps below;

1. Press F5

2. Click on Special

3. On the Menu that appears, choose Formulas

4. Check the box written Errors

5. Press OK; this takes you to all the cells with #REF! in them.

You can also follow these steps;

1. Select the whole dataset you want to check for the #REF! Error.

2. Press the Control key, thus opening the Find and Replace dialog box.

3. Go to the Find What field and type #REF!

4. Select Find All hence finding all cells that have the error. Press Ctrl+ A in case you are using Mac

5. Close the dialog boxes

The other method to use to find #REF! is as follows;

1. Press Ctrl +F

2. Type #REF! in the field that appears

3. Choose on Find All, hence every cell with the #REF! is highlighted.

Fixing the #REF! Excel Error in cells

Once you find the error, you can use any of these methods to fix this;

1. Deleting the error. It is done by

2. Hitting the delete key on your keyboard.

3. Replacing this with a value or text. It is done by;

4. Type the text or value

5. Press Ctrl+ Enter

Use the following easy steps below also;

1. Press Ctrl+ F

2. Go to the Replace Tab

3. Type #REF! in the empty field that appears, leaving the Replace field empty.

4. Choose on Replace All, thus removing all #REF! Errors from the cells by fixing them.

How to find Errors by using Go To Special Option

This method allows you to find and select the cells having any type of error apart from the #REF! Error. This method only finds and selects cells where the error is because of the formula. However, it does not find those errors that emerge from texts. Use the following steps to find cells with formula errors, including the #REF! Error.

1. Select the whole dataset that you want to check for errors.