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.