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