Excel’s #REF! error normally indicates that the cell or cells you are referring to in a formula do not exist. There are number of reasons why this might be the case. Read our tutorial on the #REF error or if you would prefer watch our video tutorial at the end of this page.
Reason 1: You Have Deleted the Cells a Formula Refers To
If you have deleted the cells your formula refers to you will get the #REF! error. Take this scenario where we are recording sales data and calculating commission due. The commission calculation is shown below.
The formula refers to two cells in column M. If you were to delete the two cells – that is the cells, not just the data, you would get the #REF! error. Deleting cells requires you to right-click on a cell or range of cells and select Delete from the shortcut menu, as shown below.
You are then asked what to do with surrounding cells.
You now get the #REF! error.
So the error will occur if you actually delete cells. This is different to deleting the data in cells with the Delete key on your keyboard which will not cause the #REF! error.
Please note you also delete cells when you delete an entire column or row.
Reason 2: You Have Cut & Pasted Cells Over Cells That Your Formula Refers To
This is a bit of a strange one to get your head round, but if you cut and paste cells into cells that your formula refers to you, you will get the #REF! error. That’s because Excel deletes cells that are being pasted into. Take the example below where you want to replace the old commission rate and threshold table with the new one below it.
When you cut the new table and paste it over the old one, you end up with the #REF! error in the commission column.
To avoid the #REF! error in this scenario, copy and paste instead of cut and paste.
Reason 3: Lookup Error
If you use lookup functions such as VLOOKUP and INDEX you will get a #REF! error if you have an incorrect reference in your formula.
If you are familiar with the VLOOKUP function, you will notice a glaring error in the formula below. Spotted it?
The error is that the col_index_num argument contains a 3, but the table_array only contains 2 columns.
This formula will result in a #REF! error because there is not a third column in the table array.
To avoid the #REF! in this scenario it is simply a matter of getting your formula right!
Watch our video tutorial on the #REF! error