Circular Reference: An Overview
When a cell formula in Excel refers back to its own cell either directly or indirectly, it’s called a Circular Reference:
In the above picture, in D5, the formula used is:
It refers to the same cell.
After entering a circular reference, a warning message is displayed:
How to Remove Circular Reference in Excel – 2 Methods
The sample dataset showcases a Covid-19 cumulative death toll.
Method 1 – Using the Formula Auditing Feature to Remove a Circular Reference in Excel
1.1 Trace Precedents
Steps:
- Select any cell, here D7.
- Go to Formulas ▶ Formula Auditing ▶ Trace Precedents.
In the picture above, D7 contains the following formula. In the following figure, you can see that C7 is the precedent that affects D7.
1.2 Trace Dependents
Steps:
- Select any cell, here C9.
- Go to Formulas ▶ Formula Auditing ▶ Trace Dependents.
In the picture above, the selected cell is C9. After selecting the trace dependents option, the blue arrow points towards D9, which means C9 is dependent on D9.
Read More: How to Fix Circular Reference Error in Excel
Method 2 – Move Formulas to Another Cell to Remove the Circular Reference in Excel
Steps:
- Select the cell with the circular reference.
- Press CTRL + X to cut the cell formula.
- Select another cell and press CTRL + V to paste it.
Things to Remember
- You can press ALT + T + U + T to enable the Trace Precedents option.
- To enable the Trace Dependents feature, press ALT + T + U + D.
Download the Practice Workbook
Download the Excel file and practice.
Conclusion
To sum up, we have discussed 2 methods to remove a circular reference in Excel. You are recommended to download the practice workbook attached along with this article and practice all the methods with that. And don’t hesitate to ask any questions in the comment section below. We will try to respond to all the relevant queries asap.
Related Articles
- How to Find a Circular Reference in Excel
- How to Enable Iterative Calculation in Excel
- Fix Circular Reference That Cannot be Listed in Excel
- How to Allow Circular Reference in Excel
<< Go Back to Circular Reference | Cell Reference in Excel | Excel Formulas | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!