The #REF error occurs in a formula with an invalid cell reference.
This happens when you delete a range that contains an explicit cell reference in the formula.
It can also happen when you paste another value over it.
For example, if we have a SUM formula with explicit cell references, it is vulnerable to the #REF error.
Now we will delete column C.
Our formula in method 1 now returns a #REF error.
The SUM formula in method 2 adapts to the missing value and recalculates based on the new cell range.
Note the formula in method 1 indicates the #REF error for the explicit cell reference that we deleted.
This comparison demonstrates why you should avoid explicit cell references. Yet, in some types of formulas, there is not another option.
The #REF error can occur in VLOOKUP formulas as well.
In the following example, we have a table that we have created a lookup for sales by quarter for a given SKU.
If we take a closer look at the actual VLOOKUP formula, we can see the problem.
The table_array argument in the VLOOKUP formula is 5 columns (B2:F15). However, the col_index_num argument is 6.
Since col_index_num cannot be a value greater than 5 in this case, this results in the #REF error.
There are two ways to fix this.
Either adjust the table_array argument or change the col_index_num argument.