How to Remove a Circular Reference in Excel – 2 Methods

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

The Trace precedents feature can trace all the cells that affect the selected cell. To enable the trace precedents option:

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

The trace dependents feature can trace all cells that are dependent on the selected cell. To enable the trace precedents option:

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