The #REF! occurs when a cell reference is no longer present, either on the sheet, another sheet, or another file.
Take this before and after example where a formula subtracts the content of columns C from column B, then we delete the column B data.
With Data in Columns B & C
Data in Column C Only; Column B Deleted
This can be a common behavior when copying and pasting formulas from one location to another in a sheet or between workbooks.
Imagine writing the following formula in cell A100…
Then copying and pasting that formula in cell A1.
Because we are working with Relative References, the cell references will change to rows that are roughly 100 rows above rows 1 through 10 (i.e., B1:B10).
If such a place existed, the cell reference would be a negative value, like
These are not valid locations on a worksheet, to say the least.