Mrinmoy Roy
Having circular references in Excel cells is problematic. Because circular reference always leads to an infinite loop that might cause the Excel operations to slow down. Besides, it returns the value zero (0) within the cell other than the expected calculated value. To fix all of the issues, you might want to remove a circular reference in Excel. In this regard, we have discussed 2 ways that you can use to remove a circular reference in Excel with ease.
Download the Practice Workbook
You are recommended to download the Excel file and practice along with it.
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. Now, look carefully at the picture below:
In the above picture, within cell D5, we’ve inserted the formula
=D5Which is basically referring to the same cell itself. This type of cell reference is called circular cell reference.
After entering a circular reference within a cell, you will be notified of a warning message as follows:
You get this warning message after inserting a formula having circular cell reference because you have a feature in Excel called iterative calculation turned off.
A circular reference is not always desired because of two reasons. Firstly, it leads to an infinite loop within the cell that might slow down the overall Excel workflow. Secondly, a formula having a circular cell reference always returns 0 instead of the expected actual formula result. To get rid of these issues, we need to remove circular references; which we will be covering in this tutorial.
2 Ways to Remove Circular Reference in Excel
We will be using a sample Covid-19 cumulative death toll as a data table to demonstrate all the methods to wrap text in Excel. Now, let’s have a sneak peek of the data table:
So, without having any further discussion let’s get into all the methods one by one.
1. Use Formula Auditing Feature to Remove Circular Reference in Excel
Unfortunately, there’s no straightforward feature that will detect and delete a circular reference in Excel. But one interesting thing that Excel has embedded is to trace the cells regarding the circular reference. Tracing cells can be of two types:
1.1 Trace Precedents
The Trace precedents feature enables us to trace all the cells that affect the selected cell. To enable the trace precedents option follow:
🔗 Steps:
❶ Select any cell, D7 for instance.
❷ Go to Formulas ▶ Formula Auditing ▶ Trace Precedents.
❸ In the picture above, the selected cell D7 contains the following formula. In the following figure, you can see that cell C7 is the precedent that affects cell D7.
=D7+C7
As long as we have the information that which cell contains circular reference and which cells affect which cell, we can replace the erroneous formula with the simplest one having no circular cell reference.
1.2 Trace Dependents
The trace dependents feature enables us to trace all the cells that are dependent on the selected cell. To enable the trace precedents option follow:
🔗 Steps:
❶ Select any cell, C9 for instance.
❷ Go to Formulas ▶ Formula Auditing ▶ Trace Dependents.
❸ In the picture above, our selected cell is C9. After selecting the trace dependents option, the blue arrow points out cell C9 towards cell D9; which means cell C9 is dependent on cell D9.
Now, as we know which cell is dependent on which cell and how our formula is causing trouble, we can replace the erroneous formula with a better one having no issues such as circular cell reference.
Read More: How to Fix Circular Reference Error in Excel (A Detailed Guideline)
2. Move Formulas to Another Cell to Remove Circular Reference in Excel
As there’s no established feature to remove the circular reference in Excel, all you can do is follow a little trickery. Which is you can cut the cell formula and paste it to another cell. That is,
🔗 Steps:
❶ Select the cell having circular reference.
❷ Press CTRL + X to cut the cell formula.
❸ Select another cell and press CTRL + V to paste it.
Read More: How to Find a Circular Reference in Excel (2 Easy Tricks)
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.
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. And please visit our website Exceldemy to explore more.
