# How to Find and Remove Circular References in Excel

Circular references can be tedious to trace and remove on your own. Learn to use Excel tools to get rid of them.

## What Are Circular References in Excel?

A circular reference is a term used for a formula that visits a cell more than once in its line of calculations. Due to this, the calculations will take a long time to crunch numbers, and it is quite likely that the calculations will return a wrong answer most of the time.

However, circular references aren’t always detrimental. In some cases, they’re quite useful. Despite that, circular references pose a risk of spiraling out of control and contributing to issues that are not immediately apparent.

If you have a circular reference in your spreadsheet but never intended to have one, then you should get rid of it.

## Understanding Circular References

To remove the formula, you need to understand it first. There are two ways a reference could be circular: directly and indirectly.

### Direct Circular References

A direct circular reference happens when a cell is directly referring to itself. Let’s make a direct circular reference:

- In cells
**A1**,**A2,**and**A3**, enter the numbers 100, 200, and 300. - Select cell
**A4**, and in the formula bar, enter the formula below:`=SUM(A1+A2+A3+A4)`

This is a simple SUM function that will sum the cells A1, A2, A3, and A4.

- Once you have typed the formula, press
**Enter**. An error message will appear. - In the error message that appears, Excel warns you that a circular reference exists in this spreadsheet. Click
**OK**.

This formula creates a circular reference in that A4 seeks to sum a set of values with A4’s value. However, what is the value of A4? You’ll never know, and neither will Excel.

As a result of this circular reference, Excel will return 0 for the formula. You may not know the answer to this formula, but it is not 0.

Related: Excel Lookup Functions to Search Spreadsheets Efficiently

### Indirect Circular References

An indirect circular reference is when a cell is referring to itself through other cells.

- In cell
**A1**, enter the formula below:`=D1`

This will set the value of A1 to that of D1. Whatever it may be.

- Enter the numbers 100 and 200 into cells
**B1**and**C1**, respectively. - Select cell
**D1**, and in the formula bar, enter the formula below:`=SUM(A1+B1 + C1)`

This is another SUM function that will sum the values in cells A1, B1, and C1 and return the result in D1.

- Press
**Enter**. An error message will appear. - In the error message, click
**OK**.

As before, Excel returns 0. However, unlike the previous example, the D4 cell makes no direct reference to itself. It instead sums A1 with a set of values. What is A1? It is D4. So what is D4? It is A1 with an array of values.

A circular reference has been formed.

Related: How to Copy Formulas in Microsoft Excel

## Find and Remove Circular References in Excel

In more complex scenarios, where numerous cells refer to each other, finding circular references may not be as straightforward as looking at the formula alone. Excel has tools to help you find circular references.

Before getting to finding circular references, let’s add some to your spreadsheet:

- In cells
**A1**,**A2**, and**A3**, enter 1, 2, and 3. - Select cell
**A4**. - In the formula bar, enter the formula below and press
**Enter**:`=SUM(A1+A2+A3+A4)`

As in the first example, this is also a direct circular reference where A4 refers to itself.

- In the error message, click
**OK**.

Excel returns 0 because the formula in A4 is a direct circular reference. Let’s create an indirect one as well:

- In cell
**C1**, type**20**. - Enter the formula below for cell
**E4**:`=G1+3`

- Enter the formula below for cell
**G1**:`=C1+2`

- Lastly, select cell
**C1**on the formula bar and replace**20**with the line below:`=E4+1`

As expected, Excel returns 0 because these cells refer to each other in a circle. However, since they don’t directly refer to themselves, they’re indirect circular references.

Related: How to Count Unique Values in Excel

Let’s explore how you can use Excel tools to find circular references in your spreadsheet. Keep in mind that your spreadsheet contains four circular references, namely A4 (direct), C1, E4, and G1 (indirect).

- From the ribbon menu, go to the
**Formula**tab. - In the
**Formula Auditing**section, click the arrow button next to**Error Checking**. - In the menu, hover your mouse over
**Circular References**. This will open a list of circular references. **Click the cell**in the list. This will take you to the cell with circular reference.- You’ll notice that Excel shows only one of the four references. This is because Excel handles these chains one at a time.
- Once you resolve the first circular reference, you can move on to the next.
- In the next step,
**resolve the circular reference**. - Go back to
**Error Checking**and click**Circular References**. - From the list, select the next circular reference and proceed to resolve it.

## Tracing Cell Relationships

Keeping track of cell relationships can become challenging in spreadsheets where too many cells refer to each other. That’s where Excel’s **Formula Auditing** feature can come in handy. It can help you get a better visualization of your formulas.

- Select a cell containing a formula.
- From the ribbon, go to the
**Formula**tab. - In the
**Formula Auditing**section, click**Trace Precedents**. - All cells that affect the values of the selected cell will be connected to each other with blue arrows.
- Next, from the same section, click
**Trace Dependents**. (This method will draw blue arrows from the selected cell to each of the cells that it affects.) - Finally, to see the formulas instead of values for all cells, click on
**Show Formulas**.

## Break the Circle

Excel uses circular references when it tries to compute the result of a cell that has been visited more than one time during the calculation round. So, it won’t help you all the time.

Yet, many financial models contain circular references. But they should be avoided whenever possible. Now that you know how to take care of circular references, it might be the right time to learn more advanced Excel functions.