📥 Learn how to find circular references in Excel with hands-on practice
Want to get the most out of this guide? Download the free practice workbook 👉 HERE and follow along step by step.
What is a Circular Reference in Excel?
A circular reference happens when a formula refers to its own cell, either directly or indirectly. This creates an endless loop, stopping Excel from calculating properly.
Example of a Circular Reference
Imagine you enter this formula in cell B2:
=B2 + 10
Excel gets confused because B2 is referring to itself. Since it can’t calculate a final value, it throws an error.
How to Find a Circular Reference in Excel
Method 1: Check the Status Bar
One of the easiest ways to find a circular reference is by looking at Excel’s status bar (bottom-left corner).
📌 Steps:
- If Excel detects a circular reference, you’ll see a message like Circular References: B2 in the status bar.
- You must manually go to B2 and check the formula.
👉 If there are multiple circular references, Excel only shows them one at a time. You have to fix them one by one until the message disappears.
Method 2: Use the Error Checking Tool
If your Excel file has multiple sheets, finding circular references manually can take long. The Error Checking tool makes it easier by listing each circular reference in the workbook, so you can fix them one by one.
Here’s how to use it:
- Click the Formulas tab.
- Click on the Error Checking button to open a drop-down menu.
- In the drop-down menu, hover over Circular References. Excel will display the first detected circular reference, if any exist.
- Click on the cell listed under Circular References to jump directly to that cell in your worksheet.
- Modify the formula to eliminate the circular reference. This may involve changing cell references or restructuring the formula.
Repeat as Necessary
- After correcting one circular reference, repeat the process to ensure no additional circular references exist in your workbook.
Featured Course
Excel Essentials for the Real World
Method 3: Use Trace Precedents & Dependents
When Excel detects a circular reference, it highlights only the first issue in the status bar. But sometimes, the real problem is hidden in another cell.
That’s where Trace Precedents & Dependents help—they show you how cells are linked, so you can track down the exact cause of the error.
Example
You’re tracking your monthly expenses in Excel. Your formula should add up all costs, but something is wrong—the total isn’t updating correctly.
At first glance, everything looks fine. But Excel detects a circular reference in the status bar.
The Problem
- B5 (Other Expenses) depends on B6 (Total Cost).
- B6 (Total Cost) depends on B5 (Other Expenses).
This creates a circular reference loop:
- B5 (Other Expenses) →
=B6 - SUM(B2:B4) - B6 (Total Cost) →
=SUM(B2:B5)
Since B5 and B6 refer to each other, Excel is stuck in a calculation loop, and the total cost won’t update properly.
How to Use Trace Precedents & Dependents
- Click on the cell listed in the status bar (e.g., B6): This is the first circular reference Excel detects.
- Go to the Formulas tab: Click Trace Precedents in the Formula Auditing section.
- Look at the arrows that appear.
- Excel will draw blue arrows pointing to B6.
- These arrows show which cells B6 depends on for its calculation.
Now that you’ve checked which cells feed into B6, you need to see which cells depend on B6.
This will reveal if another cell is causing the circular reference:
- Select B6, then click Trace Dependents.
- This will show which cells rely on B6 for their calculations.
- Look for an arrow pointing back to B5.
- If B6 depends on B5 and B5 depends on B6, Excel is stuck in a loop.
- This confirms that B5 is actually causing the circular reference, even though Excel first flagged B6.
- You’ve found the real issue!
💡 Once you’ve identified the issue, you can clear the arrows to declutter your worksheet.
- Go to the Formulas tab
- Click Remove Arrows (located right under Trace Dependents)
How to Remove Circular References in Excel
A circular reference can break your calculations, slow down Excel, and cause endless frustration. But fixing it is easier than you think.
Below are the best ways to remove circular references in Excel and restore proper calculations.
1. Adjust the Formula Logic
The most effective way to resolve a circular reference is to modify the formula causing the loop.
This involves rewriting the formula so that it no longer references itself, either directly or indirectly.
Example:
If cell A1 contains the formula =A1 + 1, it creates a direct circular reference. To fix this, you could change the formula to =A2 + 1, assuming A2 holds the intended reference value.
2. Use Helper Cells
When adjusting the formula logic isn’t straightforward, helper cells can be employed to break the circular reference.
Helper cells act as intermediaries to separate calculations and prevent self-referencing loops.
Example:
Suppose cells B1 and B2 contain formulas that reference each other, creating a circular reference.
You can introduce a helper cell, say B3, to compute an intermediate result, and then adjust the formulas in B1 and B2 to reference B3 instead of each other.
3. Enable Iterative Calculations (Use with Caution)
In scenarios where circular references are intentional and necessary for iterative calculations, you can enable Excel’s iterative calculation feature.
However, this should be done cautiously, as it can lead to performance issues and may not always yield accurate results.
Steps to Enable Iterative Calculations:
- Access Excel Options:
- Go to File > Options > Formulas.
- Enable Iterative Calculation:
- Check the box for Enable iterative calculation.
- Set Parameters:
- Define the Maximum Iterations and Maximum Change to control the calculation process.
- Confirm Settings:
- Click OK to apply the changes.
Note: Use this method only when necessary, as it can impact Excel’s performance and lead to unintended consequences if not properly managed.
Download the Free Practice File
Want to try these steps in Excel without messing up your own data? We’ve got you covered!
📥 Download our free practice file to follow along with the tutorial and test each method yourself. It includes a dataset so you can practice finding and removing circular references in Excel.
👉 Click here to download the Excel practice file
Featured Bundle
Black Belt Excel Bundle
Leila Gharani
I’ve spent over 20 years helping businesses use data to improve their results. I've worked as an economist and a consultant. I spent 12 years in corporate roles across finance, operations, and IT—managing SAP and Oracle projects.
As a 7-time Microsoft MVP, I have deep knowledge of tools like Excel and Power BI.
I love making complex tech topics easy to understand. There’s nothing better than helping someone realize they can do it themselves. I’m always learning new things too and finding better ways to help others succeed.
