The #REF! error is one of the most annoying formula parse errors in Google Sheets. The error is usually caused by a missing/invalid reference or circular dependency in your formula. We will take a closer look at exactly what this looks like and how to fix it.
Fix #REF! Error in Google Sheets
There are multiple ways to fix the #REF! error in Google Sheets.
Check for a Missing Reference
A missing reference usually occurs after deleting a column or row in your worksheet.
For example, if your formula was calculating the average in columns B and C and you deleted column B, then the #REF error would be thrown, as seen in the image below.
The easiest way to find if your formula has a missing reference is to simply click on the cell with #REF error and check if the formula contains the words #REF! anywhere.
Simply remove the word #REF! from the formula and the error will disappear.
Another common place where a missing reference occurs is when you copy a formula from one cell and place it in another.
Check for Circular Dependency
To confirm that circular dependency is causing your error, simply hover your mouse over the cell that contains the #REF error and it should say circular dependency detected (See image below).
Circular dependency happens when your formula is referring to itself. In the example above when calculating the average of cells B2 to B6, the formula refers to itself throwing the #REF error.
To fix this simply take out the self-reference from the formula. In the example above changing the formula to =AVERAGE(B2:B5) fixes the error.
Check for Out of Bounds Error
An out of bounds error usually happens when you use VLOOKUP to find a value that is out of range.
In the example below we are trying to find values from column number 3 of the table on the right, however this table only contains 2 columns.
If we correct the formula by changing the index (3rd value in the formula) to 2 instead of 3, then the error is fixed.