How to Remove Circular Reference in Excel (2 Ways)

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:

Circular Reference example

In the above picture, within cell D5, we’ve inserted the formula

=D5

Which 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:

Circular Reference error in excel

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:

Sample Dataset

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.

Trace Precedents to remove circular reference in excel

In the picture above, the selected cell D7 contains the formula:

=D7+C7

Here, cell C7 is the precedent that affects cell D7. 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.

Trace dependents to remove circular reference

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.

Move Formulas to Another Cell

Related Content:  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.


Related Articles

Mrinmoy

Mrinmoy

Hi! I'm Mrinmoy Roy. I'm an Excel and VBA content developer. I write blogs relating to Microsoft Excel on Exceldemy.com. I've completed my graduation in Electronics and Communication Engineering from Khulna University of Engineering & Technology. I've expertise in Excel functions, formulas, Pivot Table, Power Query, Visual Basic, etc. I write blogs to lessen people's hassles while working on Microsoft Excel.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo