In Excel, one can analyze and organize data easily. However, keeping track of complex formulas and references can be quite difficult. We will highlight steps on how to find circular references in Excel.
How to find circular references in Excel
Circular references is an issue that arises when a formula keeps referring back to its own cell, either directly or indirectly, thereby creating an infinite loop. Here we will explore how to find and resolve circular references in Excel.
#1. Enable circular reference warning
The first step here is to enable circular reference warning. This helps Excel to alert you if a formula creates a circular reference. In order to do this;
- Go to File.
- Click on Options > Formula.
- Then check the box next to ‘Enable Iterative calculation”.
- Set the number of iterations to 1.
- Click OK.
#2. Identify circular references
After enabling the circular reference warning, you have to find circular references using the Circular Reference tool. Here’s how to use it:
- Click the Formulas tab in the ribbon.
- Click the error checking button in the ribbon.
- Select Circular References from the drop down menu that appears.
- Excel will by itself highlight any cell containing circular references.
#3. Analyze and resolve circular references
After identifying the references, then proceed to analyze and resolve them and there are several ways to do this. Here’s how to do it:
#4. Change Formula
You can change the formula to resolve it, especially if the reference is intentional. This involves using a different cell or range as the reference. Or just use a different formula instead.
Use iterative calculation
If the circular reference is necessary, then use iterative calculation to allow Excel to calculate the formula. Here’s how to:
- Simply go to File.
- Click on Option.
- Go to Formulas.
- Then check the box next to “Enable iterative calculation”. Set the maximum number of iterations to a high number like 100.
- Then click OK.
Break the circular reference
If the circular reference is unintentional and you have no use for it, you can break the circular reference. Go to the cell that contains the circular reference and remove the reference itself.
Or use the “Trace Dependents” and “Trace Precedents” tool to trace relationships between formulas and cells, and see the origin of circular reference. The Trace Precedents and Trace Dependents features give you a clue on finding the origin of circular reference. It does this by drawing a line or lines that show which cells affect or are affected by the selected cell. This feature allows you to remove circular reference from the roots.
To display the trace arrows:
Go to Formulas tab > Click Formula Auditing group, and click one of the options to reveal:
- Trace Precedents which traces cells that provide data to a formula by drawing lines indicating which cells affect the selected cell.
- Trace Dependents which trace cells that are dependent on the active cell by drawing lines indicating which cells are affected by the selected cell. It simply shows which cells contain formulas that reference the selected cell.
There are also shortcuts you can use thus:
- Alt+T U T for Trace Precedents.
- Alt+T U D for Trace Dependents.
If you want to hide the arrows, click the Remove Arrows button under Trace Dependents.
Using circular reference in Excel is not recommended as you will experience performance issues when working with Excel worksheet. It is important you find and resolve the circular reference issues on Excel and ensure your Excel formulas are accurate.
Knowing the right tools and techniques is key to resolve the circular reference issue in Excel. Follow the steps highlighted above, and hopefully you will know how to find circular references in Excel.
If you liked this tutorial, then kindly subscribe to our YouTube Channel for more Microsoft Excel video tutorials.
