Excel often displays errors whenever there is something wrong with your values. Having an invalid cell range in a formula would simply end up displaying #REF! error.
Do you know what does REF mean on Excel?
Basically, the #REF! in Excel is a short form for reference that often appears when cells were deleted that are referenced in formulas. Finding an error while working in Excel is no doubt an awful experience however, you can blissfully find the error and fix it in Excel
What does REF Mean on Excel?
#REF! error indicates that one or more of the references in the formula used is not valid.
Suppose, you need to sum up cells C1 to C5:
[=C1+C2+C3+C4+C5]
In cell C6, you will notice any one of the cells from C1 to C5 is deleted that ultimately showing the #REF! error.
What does #REF! Error Look Like?
Everyone would have encountered #REF! error once in life. If you have not experienced this error yet, let’s have a look at how it works:
Suppose, you need to add some values in the cells B4, B5, B6, B7, and B8 and you can do it in multiple ways. One way to perform this operation is by using the SUM function:
=SUM(B4:B8)
Another way is by using a formula that references each cell individually:
=B4+B5+B6+B7+B8
Below you can see the highlighted cell B9 has the SUM function and referenced the range. On the other hand, in cell D9 the individual cell references are used:
No matter what method you are using, the result will be the same, which is 81.
By deleting row 4, the formula in B8 keeps in functional mode whereas the formula in D8 that contains individual cell references will break down:
How do You Find Cells with #REF in Excel?
You can find the #REF! errors by following some methods:
Use Find & Replace to Locate #REF Errors
Here are the steps to follow while using Find & Replace feature:
- Apply the CTRL + F shortcut command to open the Find & Replace dialog box.
- Now, put #REF! in the Find field box.
- Click on the Find All button.
- In case, you need to remove the #REF! references, you have to choose the Replace box and leave Replace with the field blank.
- Click on the Replace All option. It will replace all the #REF! with nothing whereas it removes this error from the worksheet.
Use Go To Special to Find #REF in Excel
Here comes another awesome way for the users to find #REF errors in the sheet with the help of the Go To Special feature:
- Open the Home tab and choose Go To. Or else you can apply this command to directly open the Special option: CTRL + G
- Now, choose the Formulas option from the Go To Special dialog box that appears.
- Check the box next to Errors.
- You will see the formulas with #REF! errors are selected.
- Now, you can easily remove them from the sheet.
That’s it!
Use Conditional Formatting to Highlight Errors Automatically
Conditional formatting is another useful trick you can apply to find any error in the sheet. It will need you to apply a designed cell format to the current and expected #REF! errors.
- Choose all cells by pressing CTRL + A or else click on the top left side of the sheet.
- Open the Home tab and choose Conditional Formatting.
- Select Highlight Cell Rules and then choose the More Rules option.
- Click on the Format only cell with: Errors
- Select the Format option so that you can have the desired format.
- Press OK.
- You can now format any existing formula error cells. While correcting them, you will see the formatting is cleared and cells with formula errors will also be deleted by default.
Now, you have learned what does ref mean on Excel and how you can identify it. Let’s learn how you can fix them.
How to Stop Encountering #REF! Error in Excel
To deal with #REF! error, you simply have to stay away from it. Well, it is just a saying, you may encounter this error once in your lifetime that’s why you must learn how to prevent it.
Some points are needed to be considered that certainly happen the occurrence of @REF! error in the sheet.
Deleting Rows and Columns
While deleting rows and columns, you often encounter #REF! errors. It is a common cause and to get rid of this, you need to ensure that formulas are not referencing the values in them.
Copy + Pasting Cells
While copying and pasting cells, relative references are used and for this reason, you may encounter #REF! error. It is because Excel updates them on the basis of their previous location in the sheet. For instance, copying a cell with a formula that references the cell to the left, and then pasting it into a cell in Column A where there is no valid reference present. It will surely result in #REF! error.
Summary:
This post explains well enough what does REF mean on Excel and how one can avoid it. The above-mentioned data is highly effective in terms of its significance.
