This guide will explain the best methods you can use to fix the #REF! error in Excel.
The #REF! error often occurs when a column or row is deleted. This issue may also occur if your formula references a cell in a recently deleted tab in your workbook.
The #REF! error can be frustrating because there are cases where the user must try and remember what the cell was referring to.
It’s best to solve these errors as soon as they appear. Spotting these errors early on is more convenient because the user can use the Undo action to return the missing values back to the sheet.
In this guide, we will explain how to list down all #REF! errors in your sheet using the Find & Replace tool.
Now that we know why the #REF! error occurs, let’s take a closer look at real examples of the #REF! error and actual solutions you can use to fix them.
A Real Example of Fixing #REF! errors in Excel
In the following section, we will provide a few examples of when a #REF! error might occur. We’ll explain in-depth why the error occurred and how to fix it yourself.
First, let’s look at an example that often happens when the user deletes a row or column that is referenced elsewhere.
In the example below, we have two formulas that try to calculate values using functions. Cell E1 returns the total of all values in the range B2:B9. Cell E2 attempts to calculate the growth rate of another range.
You later realize that the range it’s referencing has been deleted. It might be difficult to fix the issue if you are unsure what the original reference was.
This is the reason why it’s best to resolve #REF! errors as soon as they appear in your spreadsheet.
The #REF! error may also appear when using functions such as VLOOKUP. These functions often rely on the user to specify a given range and a specific row or column from that range.
If the row or column does not exist in the range, then the formula will return a #REF! error.
In the example below, we have a VLOOKUP function in cell G8. The function will lookup the value ‘a’ in the cell range G1:H6. The user wants to retrieve the third column in the range even though the range itself has only two columns. Because a third column does not exist, this is considered an invalid reference.
To fix this issue, we can simply modify our formula to return the right column number.
Do you want to take a closer look at our examples? You can make your own copy of the spreadsheet above using the link attached below.
Suppose you want to learn how to list all #REF! errors in your Excel spreadsheet, head over to the next section and read our detailed tutorial on how to do it!
How to Fix the #REF! Error in Excel
This section will guide you through each step needed to start listing all #REF! errors in your spreadsheet. You’ll also see a simple example of how we can fix a single #REF! error.
Follow these steps to fix #REF! errors in Excel:
- Type the Ctrl + H shortcut to access Excel’s Find and Replace tool. We can use the Find feature to go through each instance of the #REF! error.
- Type the string ‘#REF!’ and click the Find All button.
- If there are multiple results, you may click on the Find Next button to cycle through each result.
- For each result, you must replace each instance of #REF! with a valid cell or cell range reference. If you cannot remember which reference to use, it may be possible to revert the error using the Undo feature.
- For functions such as
VLOOKUP, ensure that each argument is a valid cell reference. For example, trying to return the third column of a range with only two columns is considered invalid.
These are all the steps needed to list all #REF! errors in your sheet.
Frequently Asked Questions (FAQ)
- Why does my macro return a #REF! error?
Some macros may return a #REF! error if the macro tries to refer to cells that do not exist. For example, if we try to reference a cell above cell A1, we will end up with an invalid reference. - What is the best way to avoid the #REF! error?
Since the #REF! error often compounds. It’s best to prevent these errors as much as possible. Anytime you delete a column or row, check if any cells return a #REF!. Use the Undo action to return the spreadsheet to its previous state.
Since deleting tabs is harder to undo, ensure that no formulas rely on data found on the tab being deleted.
This step-by-step guide should be all you need to list all #REF! errors in Excel. Our guide also gave best practices to fix the #REF! error in Excel from appearing.
Suppose you are encountering issues in your sheet other than the #REF! error, we may have the solution you need. Our website covers hundreds of other Excel methods and functions that you can apply to your own spreadsheets.
Are you interested in learning more about what Excel can do? Subscribe to our newsletter to look into the latest Excel guides and tutorials from us.
