Circular Reference

 

A formula in a cell that directly or indirectly refers to its own cell is called a circular reference. This is not possible.

1. For example, the formula in cell A3 below directly refers to its own cell. This is not possible.

Direct Circular Reference in Excel

Note: Excel returns a 0 if you accept this circular reference.

2a. For example, the formula in cell C2 below refers to cell C1.

Relative Reference

2b. The formula in cell C3 refers to cell C2.

Relative Reference

2c. The formula in cell C4 refers to cell C3.

Relative Reference

2d. So far, everything's OK. Now change the value in cell C1 to the formula =C4. Cell C1 refers to cell C4, cell C4 refers to cell C3, cell C3 refers to cell C2, and cell C2 refers to cell C1. In other words, the formula in cell C1 indirectly refers to its own cell. This is not possible.

Indirect Circular Reference in Excel

Note: Excel returns a 0 if you accept this circular reference.

3. To find your circular references, on the Formulas tab, in the Formula Auditing group, click the down arrow next to Error Checking.

Error Checking

4. Click Circular References.

Circular References