Causes and solutions of #REF! errors in Excel

This is one of the most problematic errors in Excel. There is no direct way how to fix it. I show you some of the frequent examples with #REF errors. The worst case is if you open the file and you see the # REF error in it. At first you may try to contact the file creator and ask him about the original formula. If you are the creator of the file, you have to decipher the composition of the original formula. I hope these examples will help you.

The formula is copied out of the line

It may happen that copying the formula gets one of the addresses out of the border. The following video shows the simplest example. We have formula A1+B1. The first bad copy of the formula should change the formula to A0+B0. These addresses are not valid, so Excel shows the #REF! error. The second bad copy should change the formula to 01+A1. These addresses are so not valid and Excel shows the #REF! error.

Solution

There is no one simple way how to fix this.

1)      You can try to insert $ character to the formula. For example $A1+$B1. It depends on your data.

2)      You can try to move the source cells (A1, B1) to the position that does not cause the #REF error.

Wrong file name in the formula

Let’s have the fileA. The formula contains data from another file: [fileB] sheet1!$A$1. When you rename the file fileB to fileC and fileC does not exist, it occurs the #REF! error.

Solution

1)      You have to find the original source files and repair the file names in the formulas.

Deleting cells referred to other formula

The following video shows how deleting the cell causes #REF! error in other cell.

Pasting moved cells over cells referred to other formula

Similar as previous example. The difference is that the cell is not deleted, but it is replaced by another cell.

General solution

If the formula is complicated, you can try Excel tool called Trace Error. It helps to find out which part of the formula causes the error. Click on the cell with the problematic formula and go to the menu Formulas >> Error Checking >> Trace Error.

Did I forget something? Please leave a comment.