• Skip to main content
  • Skip to primary sidebar

Nerd Excel

Excel Simplified

  • Excel
  • Google Sheets
  • Word
  • Google Docs

How to Fix #REF! Error in Google Sheets

By: Salman Patwegar

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

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.

#REF Error From Missing Reference in Google Sheets

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.

#REF Error From Copied Formula in Google Sheets

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).

#REF Error From Circular Dependency in Google Sheets

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.

#REF Error From VLOOKUP Formula in Google Sheets

If we correct the formula by changing the index (3rd value in the formula) to 2 instead of 3, then the error is fixed.

Related
  1. How to Fix #DIV/0! Error in Google Sheets

Primary Sidebar

Popular Now

  • How to Calculate Age in Google Sheets
  • How to Sort by Date in Google Sheets
  • How to Generate Random Numbers in Google Sheets
  • How to Generate Random Numbers in Excel
  • How to Unprotect Excel Sheet Without Password

About | Privacy Policy | Disclosure | Terms | Contact
© Copyright 2022 @Nerdexcel.com · All Rights Reserved