Buzz
The #REF! error appears when a formula references an invalid cell. This commonly happens when the cell referenced by a formula is deleted or overwritten.
Encountering #REF! Error when Deleting Cells
For instance, imagine a spreadsheet with data in column D created by multiplying column B with column C.
Deleting column B or C can trigger the #REF! error. The image below depicts the aftermath of Software Tips deleting column B:
Now, the formula becomes =#REF!*B4 => The object to reference is lost due to column deletion, removing the row containing that object.
Additionally, cutting (cut) data and pasting (paste) it over a referenced data range also results in the #REF! error. In the example spreadsheet, use the cut command to move data from any cell, say E2 (containing data 2), to cell B4.
The result is that the data from cell E2 will overwrite cell B4, and in cell D4, the #REF! error will appear.
How to resolve:- If you accidentally performed that action, press Ctrl + Z to undo (go back to the state before performing the delete/cut-paste operation).
- Alternatively, you can close the Excel file without saving the data, then reopen the file to revert to the spreadsheet at the previous save point.
- If you mistakenly saved a file containing a reference error, you cannot fix it anymore. In that case, you need to review the data and manually correct it.
Error #REF! due to missing reference, exceeding the range
The #REF! reference error occurs when using functions with col_index_num, row_index_num arguments in some formulas that exceed the range of the referenced area.
For example, in the spreadsheet below, the range A3:D7 has only 4 columns. However, the col_index_num value of the Vlookup function is 6; exceeding the range of the data area to be referenced. As a result, it returns the #REF! error.
How to fix:
- Review the data and manually correct it.
- Instead of entering numbers for the col_index_num, row_index_num parameters, you can use formulas to automatically calculate col_index_num, row_index_num. Use the Match function to calculate the row_index_num parameter and the Column function to find the col_index_num value.
The #REF! reference error occurs when the external Workbook being referenced by functions like Indirect is closed while the referencing Workbook is still open. In such cases, you need to resolve it by replacing the function with an equivalent one or by copying both the reference data and the Indirect function within the same Workbook.
Now, we have a clear understanding of the #REF! error in Excel. Hopefully, we can avoid encountering this error, and if we do, we know how to fix it. Wishing you all success!
