INVALID REFERENCE ERROR IN EXCEL HOW TO

Because a third column does not exist, this is considered an invalid reference. The user wants to retrieve the third column in the range even though the range itself has only two columns. The function will lookup the value ‘a’ in the cell range G1:H6. In the example below, we have a VLOOKUP function in cell G8. If the row or column does not exist in the range, then the formula will return a #REF! error. These functions often rely on the user to specify a given range and a specific row or column from that range. The #REF! error may also appear when using functions such as VLOOKUP. This is the reason why it’s best to resolve #REF! errors as soon as they appear in your spreadsheet. It might be difficult to fix the issue if you are unsure what the original reference was. You later realize that the range it’s referencing has been deleted. Cell E2 attempts to calculate the growth rate of another range. Cell E1 returns the total of all values in the range B2:B9. In the example below, we have two formulas that try to calculate values using functions.

INVALID REFERENCE ERROR IN EXCEL HOW TO

We’ll explain in-depth why the error occurred and how to fix it yourself.įirst, let’s look at an example that often happens when the user deletes a row or column that is referenced elsewhere. In the following section, we will provide a few examples of when a #REF! error might occur. 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.Ī Real Example of Fixing #REF! errors in Excel In this guide, we will explain how to list down all #REF! errors in your sheet using the Find & Replace tool.

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. It’s best to solve these errors as soon as they appear. The #REF! error can be frustrating because there are cases where the user must try and remember what the cell was referring to. A Real Example of Fixing #REF! errors in Excel.

Invalid reference error in excel