See all How-To Articles

How to Find a Circular Reference Error in Excel & Google Sheets

This tutorial will demonstrate how to find a circular reference error in Excel and Google Sheets.

 

circular refs intro

 

There are two types of circular references in Excel: direct and indirect.

Find Direct Circular Reference

A direct circular reference is when the formula contained in the cell refers to the cell the formula is in.

 

circular refs direct ref

 

In the formula bar, (1) the formula =SUM(C5:C15) is stored in (2) cell C15, as C15 is the location of the formula and also part of the formula, a (3) DIRECT circular reference is found, and the formula returns an answer of zero.

In the Ribbon, select Formulas > Formula Auditing > Error Checking > Circular References. The cell that contains the circular reference will be shown.

 

circular refs find error

 

Adjust the formula to remove the circular reference.

 

circular refs removed

 

Find Indirect Circular Reference

An indirect circular reference is when a formula uses a cell that is referring back to itself, i.e., it indirectly refers back to its own cell.

 

circular refs indirect error

 

The formula in Cell C2 is causing a circular reference. In the Ribbon, select Formula > Formula Auditing > Error Checking > Circular References. This shows two references.

 

circular refs indirect 2 refs

 

If you select $D$5, the Excel pointer will move to D5 so you can examine the formula in that cell.

 

circular refs indirect second ref

 

If you note the formula in cell D5, it refers to cell C5, but if you note the formula in cell C5, it in turn refers to cell D5 – thus creating a circle and therefore a circular reference. These two cells cannot depend on each other. One of them needs to be amended to contain a value.

 

circular refs remove indirect

 

Removing the formula in C2 and replacing it with a value will remove the circular references from Excel.

Find Circular Reference Error in Google Sheets

Google Sheets returns an error triangle and a #REF! to any cells that contain a circular reference.

 

circular refs gs