Error #REF! show when the formula refers to a cell that is not valid. This happens most often when a cell referenced by a formula is deleted or pasted.
Error #REF! when clearing cells
For example, you have a worksheet with data column D created by Column B times column C.
Deleting column B or C will cause a #REF! Error . . The image below is after the Software Wizard deleted column B:
Now the formula will become = # REF! * B4 => The object that needs reference is lost by deleting the column or row that contains that object.
In addition, cutting data and pasting it into a referenced area also causes a #REF! Error . . For the example worksheet above, you use the cut command to cut data from any cell, for example E2 (with data 2) into cell B4.
As a result, the data of cell E2 will be overwritten in cell B4 and in cell D4, the #REF ! Error will appear .
- In case of an error that you accidentally perform that action, press Ctrl + Z to undo (back to the previous time when performing the delete / cut - paste action of that data).
- Or you can turn off the excel file without saving the data, then reopen the file to return to the spreadsheet at the time of the previous save.
- If you accidentally saved the file containing the reference error, you cannot fix it again. At that time, to review the data and correct it manually.
Error #REF! Due to unavailable reference, out of range
Error reference #REF! Occurs when using functions with the col_index_num, row_index_num arguments in some formulas that exceed the range of references.
For example, in the spreadsheet below, the A3: D7 only 4 columns, but the value col_index_num function VLOOKUP is 6; exceeds the range of data ranges to be referenced. Therefore the result is a #REF! Error .
How to fix:
- Review the data and correct it manually.
- Instead of entering numbers at the arguments col_index_num, row_index_num ; You can use the formula to automatically calculate the col_index_num, row_index_num arguments . Use the Match function to calculate the row_index_num argument and the Column function to find the col_index_num object .
Error reference #REF! because the reference object outside of the Workbook is working, that reference Workbook is closing in a number of functions like Indirect. In the above case, you must overcome by replacing the function with equivalent uses or copying reference data and the Indirect function with the same Workbook.
So we can understand the #REF! Error! in Excel already. Hopefully we will avoid this error and if we encounter this error, we can also know how to fix it. Good luck!