5 Common #REF! Errors in Excel and Quick Fixes You Must Know

The #REF error in Excel is a common issue that many people encounter. It can be time-consuming and effort-intensive to fix this error if one is not familiar with its cause. So, what exactly is the #REF error and how can it be resolved? Let's find out!

By
Administrator
-
0
159

The #REF! error in Excel can cause issues when calculating and displaying incorrect results in your spreadsheet. To avoid encountering this error, you need to understand the causes and proper handling methods to ensure accurate operations.

What is the #REF! error in Excel?

REF is an abbreviation for Reference, which means referring to something. Therefore, if you see #REF! in a cell within a worksheet, it indicates a reference error.

Therefore, the #REF! error in Excel can be understood as a reference error when you refer to invalid cells. These errors usually occur when a cell in the spreadsheet refers to a non-existent or deleted cell.

3 main causes of the #REF! error

  • References to a file that is not open and cannot be referenced.
  • The object to be referenced is not within the reference range.
  • The object to be referenced is lost due to deleting a column, deleting a row containing that object, or being overwritten by a copy-paste operation.

Some REF errors in Excel and how to fix them

The #REF! error in Excel can occur when you insert or delete rows/columns in the worksheet, or when you copy and paste cells that reference other cells. Specifically, here are some common errors:

1. #REF! error in Excel due to deleted reference column

If you accidentally delete the column that you are referring to, the #REF! error will be displayed. To fix this, you just need to add the deleted column back with its corresponding values.

Step 1: In the data table, you try to delete column D “Sales Q2/2021”. The table will immediately show the #REF! error.

Step 2: Fix the error by pressing Ctrl + Z to undo or manually add the column back.

2. Error caused by exceeding the allowed reference value

If you reference more cells than what was initially defined (usually greater than the original range), the function will display the #REF! error. To fix this error, simply reduce the number of referenced cells to match the actual number in the table.

Step 1: In the same data table, you use the VLOOKUP function to reference 5 columns. However, the data table only has 4 columns, resulting in the #REF! error.

Step 2: Change the column number from 5 to 4, and the function will return the correct result.

3. Incorrect reference row/column in INDEX function

In the case of the INDEX function, if you return a value from a row or column beyond the reference range, it will also result in the #REF! error in Excel.

Step 1: Request to return a value in the 5th row and 5th column, but the cell range only has 4 rows and 4 columns.

Step 2: Adjust the row or column values to fit within the appropriate reference range.

4. Error due to INDIRECT function referring to closed file

In Excel, the INDIRECT function retrieves data from closed files that cannot be referenced. Therefore, you need to reopen the function and re-reference to the closed file.

Step 1: Enter the data table and reference cells as shown in the image below.

Step 2: At this point, the screen displays the #REF! error. To fix it, reopen the INDIRECT function and re-reference accordingly.

5. Error when copying data from one worksheet to another

Sometimes, copying data from one Excel file to another can also result in the #REF! error. To resolve this, compare the copied file with the original file and modify the data to match.

Step 1: Copy the old data table to a new Excel file.

Step 2: When the #REF! error appears, compare the two files and adjust the data to match. The correct result will be displayed in the Excel table.

Other common syntax errors in Excel

In addition to the #REF! error in Excel, you should also be aware of other common syntax errors such as:

  • #N/A error: Indicates that a result is not found or a result is not available.
  • #VALUE! error: Indicates a wrong data format.
  • #NAME? error: Indicates a misspelled function name.
  • #NUM! error: Indicates a function that contains invalid numeric values.
  • #DIV/0! error: Indicates a division by zero error.

Conclusion

This article provides detailed information about the #REF! error in Excel. Hopefully, you will be able to operate smoothly and easily with the functions in Excel. If you are looking for computer devices to run Excel efficiently, check out FPT Shop’s products!

See more laptop models here: Laptops

RELATED ARTICLESMORE FROM AUTHOR