How to Find Circular References in Excel? Quick steps to fix

Are you getting circular references error on your spreadsheet and cannot find where it is? Then, here’s a easy guide for you on how to find circular references in Excel?

Table Of Content

1. What is a Circular Reference in Excel?
2. How to Find Circular References in Excel?
3. Types of Circular References
4. How to remove a circular reference in Excel?
5. How to disable circular references in Excel?
6. Conclusion

So, we’ll help you identify the source of the circular references in your worksheet or workbook.

1. What is a Circular Reference in Excel?

When an Excel formula directly or indirectly refers to its own cell, making a loop, this is called a circular reference.

So, this can happen if a cell talks to itself or if several cells talk to each other in a way that makes a closed loop. As Excel tries to fix the infinite loop, circular references can lead to mistakes or strange behavior in your calculations.

Example:

For instance, if you put the formula =A1 + 1 in cell A1, Excel will know that this is a circular reference because the formula in A1 is trying to refer to its own value.

Also, if cell A1 has the formula =A2 + 1 and cell A2 has the formula =A1 + 3, then there is a circular reference loop between A1 and A2.

So, when you build some formula like this, Excel will warn you that you’re having a circular references. Particularly, this messages says that “there are one or more circular references where a formula refers to its own cell either directly or indirectly” and warns that “this might cause them to calculate incorrectly“.

In fact, this is a heads-up from Excel saying that you’re using a circular reference which might end up with incorrect calculations or corruptions on your workbook model. Another possible result might be a significant slowdown on your workbook.

You can either click Help to learn more or close this warning windows. If you close the window or click on ok, then this cell will get a zero as value or will keep the last successful value before the circular reference.

2. How to Find Circular References in Excel?

Above are, of course, very simple examples and you can easily track these circular references. But, if you’re working on complex models, which most people usually do, finding the circular references might be a real burden.

Luckily, Excel has a lot of tools that can help you find and fix these problems.

Method 1: Error Checking Menu

So, the most common and easy way to detect circular references in the built-in error checking menu on your ribbon.

• Step 1: Start up your workbook

Firstly, open the Excel file that you think has references that go in circles.

• Step 2: Start error checking

On the ribbon, go to the Formulas > Formula Auditing Group > Error Checking > Circular References.

• Step 3: Select the circular references

Excel will show a list of any cells that have circular references.

So, the circle references should be chosen from the drop-down menu. If you click on a cell in this list, you will go straight to that cell.

• Step 4: Fix your circular error

Now you know where your circular reference is and you should fix the formula. The below method will help you to evaluate your formula to find out the root cause of the circular references.

Method 2: Evaluating the Formula

Once you find the cells with circular references, you can know trace your formulas.

• Firstly, we’ll open Evaluate Formula wizard.
• On the ribbon, go to Formulas > Formula Auditing Group > Evaluate Formula

This will open the evaluate wizard.

Then you can dive into your formula with Evaluate and Step in buttons to find out where’s your circular reference coming from.

Method 3: Formula Tracers

If you’re still now clear enough about your circular references, then you can use the Trace features in Excel.

Firstly, go to Formulas > Formula Auditing group to find tracing features.

Example:

For instance, we have a table including unit prices, sales quantities, sales amount and commissions. The Sales amount is calculated as unit price x sales quantities. And, the commissions are calculated as 10% of the sales amount.

So, the above image shows the precedents and the dependents of the Sales cell. Excel brings blue arrows to show you both the precedents and the dependents.

In summary, Excel shows you both the precedents and the dependents. That’s to say, if a cell is calculated from a value on the other cell, this can be traced with Trace Precedets. And if a cell is used for a formula in another cell, this can be also traced by Trace Dependents button.

Thus, you can just follow the arrows. And, the arrows will help you see how the cells are connected, which will make it easier to find loops and references that go in circles.

3. Types of Circular References

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

Direct Circular Reference

When a formula refers directly to its own cell, this is called a direct circular reference.

As an example, the formula =B2 + 5 in cell B2 refers directly to itself, making a circular reference.

Indirect Circular Reference

A chain of references that end up going back to the original cell is what makes up an indirect circular reference. As an example:

• The equation =C2 + 3 is in cell C1.
• The equation =C3 + 2 is in cell C2.
• The formula =C1 + 1 is in cell C3.

In this case, C1 talks about itself through C2 and C3. And, this creates an indirect circular reference.

4. How to remove a circular reference in Excel?

To get rid of circular references, you have to find and break the loop that the cell references create. Here are some steps you can take to get rid of circular references:

• Find the Circular Reference: To find the cells that are part of the circular reference, use the above methods.
• Change the Formulas: To see the circular reference, click on the cell that has it.
• Either get rid of the reference to the cell or break the loop in the formula.
• Look for Other Problems: After making changes to the formula, use the error checking tool again to look for any other circular references.

Please do not forget to get help from Excel’s built-in formula auditing tools like Error Checks, Evaluate Formula, Trace Precedents and Trace Dependents.

5. How to disable circular references in Excel?

It’s important to find and fix circular references, but sometimes you might want to turn them off completely to avoid any problems. You can allow iterative calculations to turn off circular reference messages.

You can change your Excel workbook settings to disable or enable the circular reference warnings.

• Go to File > Options to open your Excel Options window
• Under the Formulas tab, you can turn on Iterative Calculations
• You can also set maximum iteration and change values. The defaults are 100 and 0.001, respectively.

As a note, the maximum iteration defined the maximum number of calculations for the formula, while the change value defines the maximum change value between the results.

*WARNING: If you're not a pro Excel user and enabling iterations on purpose, we never recommending to turn on this feature. In fact, the Excel turns of iterative calculations by default. This prevents unexpected results and also performance issues.

So, instead of disabling circular reference warnings, we highly recommend you to avoid circular references in your models.

Some very experienced Excel users and model builders use circular references intendedly to trigger repetitive calculations on their workbook models. But this really needs a full-control over the formulas, as otherwise may cause unexpected results and miscalculations.

6. Conclusion

Finally, in this article, we have dived deep into circular references in Excel. We have tried to keep it simple without getting too much into technical details.

So, to keep your spreadsheets safe, you need to know how to handle and understand circular references in Excel. Also, remember these important things:

KEY TAKEAWAYS:
- Circular reference means a formula that refers to its own cell in some way, either directly or indirectly, making a loop.
- To find circular references, you can use Excel's built-in formula auditing tools like Error Checks, Evaluate Formula, Trace Precedents and Trace Dependents.
- The circular references are mostly unintended or unhidden. But in some very rare cases and mostly by pro Excel users, they might be also intended.
- You can turn of circular reference warnings from the Excel Options menu. But this is not recommended for regular excel users.

As many people has this circular reference error various times in their daily work, we wanted to underline some important issues about circular references and also how to find circular references in Excel. Hope you enjoy our article.

Stay tuned for further Excel tips!

Recommended Readings:

How to fix SPILL Error in Excel? Easy and Clear Steps

XLOOKUP VS INDEX MATCH: Which is better and why?

How to fix #VALUE! Error in Excel? Reasons, Troubleshoot Tips & Solutions