ExcelInsider Team
Circular references in Excel can cause calculation errors and incorrect results. They occur when a formula refers back to its own cell directly or indirectly. . This can cause Excel to get stuck in an endless calculation cycle.
For example: Cell A2 has a formula =A2+10. Since it refers to itself, Excel cannot compute the correct result. We usually face this problem while working on payroll sheets, financial models, or inventory trackers, where totals depend on values that themselves include totals.
To remove circular reference in Excel, follow these steps:
➤ Open the Excel sheet and note the circular reference warning.
➤ Identify the cells causing the circular reference using the Formulas > Error Checking > Circular References menu.
➤ Delete or correct the problematic formula to stop the loop.
In this article we will cover what circular references are, how to find them in Excel, and how to remove them using methods like correcting the formula.
What is Circular Reference in Excel?
A circular reference occurs when a formula refers to its own cell that creates a loop.
For example, if cell A1 contains the formula =A1 + 1, Excel flags this as a circular reference.
Circular references may also be indirect for example, if A1 refers to B1, and B1 refers to A1. These references can prevent proper calculation and may cause confusion or incorrect data unless intentional and controlled via iterative calculations.
Note:
Circular references are usually accidental and should not be used unless in advanced models like loan calculations or goal-seek setups.
Steps to Remove or DIsable Circular Reference in Excel
Step 1: Open Excel File That Has Circular Reference
Open your Excel file. If there is a circular reference, Excel will usually show a warning message with Circular Reference Detected like warning. Click on Ok
Let’s see the dataset with Circular Reference. We have chosen a dataset where in Column D we have a formula that is causing circular cell reference. We will adjust the formula to remove the circular reference
Step 2: Find Out Affected Cell with Circular Reference
Go to Formulas > Error Checking > Circular References. This will show the exact cell(s) causing the issue, example: Suppose the warning shows cell $D$2.
Step 3: Apply Trace Precedents
If you want to visually see the other cells that is related to the formula that is causing the circular reference click on Formulas > Trace Precedents
Step 4: Visualize Formula That Has Circular Reference
Now you will see the cells that are related to the formula causing circular reference with the Blue line.
Step 5: Identify the Circular Reference in Formula
Click on the problematic cell (e.g., D2). Check its formula in the formula bar. For example: If the formula is =SUM(B2+C2+D2), it is referring to itself (D2), which causes a loop (Circular reference)
Step 6: Modify the Formula to Remove Circular Reference
Rewrite the Formula Correctly. We will change the formula in D2
From: =SUM(B2+C2+D2)
To: =SUM(B2+C2)
Step 7: Apply the Corrected Formula to Rest of the Cells
Repeat the same correction for all affected cells (D3 to D8) to remove the circular reference from all cells.
How to Allow Circular Reference in Excel
Click on “File” in the top left corner, then select “Options“. In the Excel Options dialog box, click on “Formulas” in the left-hand menu. Locate the “Calculation options” section and check the box next to “Enable iterative calculation“.
Set Iteration Parameters:
- Maximum Iterations: This determines how many times Excel will recalculate the sheet when a circular reference is present. The default is 100.
- Maximum Change: This sets the smallest value that will trigger another recalculation. The default is 001.
Click the “OK” button to save the changes. Now you have allowed circular reference in excel.
Frequently Asked Questions
How to get rid of a circular reference in Excel?
You can remove it by finding the problematic cell using the Circular References menu and either deleting or correcting the formula reference.
How do I disable circular references?
Go to File > Options > Formulas and uncheck the “Enable iterative calculation” option. But note that this only stops Excel from calculating circular references; it doesn’t fix the formula.
How to find circular references in Excel?
Use Formulas > Error Checking > Circular References. Excel will show the list of cells that have circular references so you can fix them.
How to fix reference errors in Excel?
Reference errors like #REF! occurs when a formula refers to a deleted or invalid cell. To fix it, reassign the correct cell or range in the formula.
How do I turn off references in Excel?
You can’t turn off referencing entirely, but you can disable iterative calculations (which allow circular references) by going to: File > Options > Formulas > Uncheck “Enable iterative calculation.”.
How do I change all references in Excel?
Use Find and Replace (Ctrl+H). In formulas, replace specific references (e.g., A1) with new ones (e.g., B1). Ensure formulas update logically after the change.
Concluding Words
Through using the Deleting or Removing the Formula or Cells method, you can easily remove circular references in Excel and ensure that your data is calculated correctly. Correcting circular references early helps to avoid complex errors in your spreadsheets.
