# How to Find and Remove Circular References in Excel

Circular references represent one of the most challenging issues that can derail Excel models and calculations. Formulas inadvertently referring back to themselves create endless, recursive loops that grind spreadsheets to a crawl.

Gaining circular reference prowess lets you rapidly resolve Excel’s thorniest calculations. Let’s dive in and break the vicious circles plaguing your workbooks!

Working with formulas in Excel can get tricky sometimes. One of the most common issues many users face is the dreaded “circular reference” error. This happens when a formula refers back to its own cell creating an endless calculation loop.

While circular references may seem harmless at first glance, they can slow down your workbook and even cause errors. So it’s important to find and remove them from your worksheets. In this comprehensive guide, I’ll explain what circular references are, why you should avoid them, and most importantly – how to detect and eliminate circular formulas in Excel.

## What is a Circular Reference in Excel?

A circular reference occurs when a cell formula refers to its own cell, either directly or indirectly.

For example if you enter =A1 in cell A1 it creates a direct circular reference. Similarly, entering any formula that refers to A1, like =A1+B1 or =IF(A1>0, “Yes”, “No”) also leads to a circular reference.

When you enter a formula with a circular reference and press Enter, Excel displays a warning message:

``Careful, we found one or more circular references in your workbook which might cause your formula to calculate incorrectly.``

This happens because circular references can create endless loops causing Excel to get stuck in an infinite recalculation. This slows down your workbook and consumes system resources unnecessarily.

If you click OK after the warning, Excel displays either 0 or the last calculated value in the cell. The circular reference may work briefly before getting stuck in an infinite loop.

So why does Excel allow circular references at all? There are some rare cases where a circular reference may provide an elegant solution when no other alternative exists. But it’s best to avoid them if possible.

## Problems Caused by Circular References

Here are some of the issues you may encounter with circular reference formulas in Excel:

• Infinite Recalculations – This is the most obvious and noticeable problem. Excel gets stuck trying to evaluate the formula forever, slowing down the workbook.

• Incorrect Values – Due to endless iterations, the formula may return an incorrect or unexpected value.

• Formula Errors – Excel may show #DIV/0!, #VALUE! or #REF errors due to incorrect calculations.

• Unstable Results – Circular formulas may work intermittently, giving different results each time.

• Difficulty Debugging – It becomes extremely tricky to debug and fix errors in a sheet with circular references.

• Workbook Corruption – In extreme cases, heavy circular references can even corrupt your Excel files.

So unless you have a rare specific need, it’s wise to avoid circular references in your worksheets.

## How to Find Circular References in Excel

Excel provides a handy built-in feature to check for circular formulas. Here are a few ways to find circles in your workbook:

### Check Formula Auditing Tools

Go to Formulas > Formula Auditing > Error Checking. In the dropdown, click Circular References.

This will display the first circular reference in your workbook.

![Find Circular References][]

You can navigate between all the circles using the Previous and Next buttons.

### Check Status Bar

Click on the cell displayed under circular references in the Formula Auditing dropdown.

This will select the problem cell and Excel will display a message on the status bar – “Circular References: Cell-Reference”.![Status Bar Circular Reference][]

### Use F2 Shortcut

Press F2 to enter edit mode in any cell. If it contains a circular reference, you will see a warning message.

Press Esc to close the message box.

### Trace Precedents/Dependents

Click Formulas > Formula Auditing > Trace Precedents to see which cells provide data to the formula.

Click Trace Dependents to see which cells contain formulas that refer to the selected cell.

If a cell is both a precedent and dependent of the selected cell, it indicates a circle.![Trace Precedents Circular Reference][]

These tracing arrows help visualize the circular reference path.

## How to Remove Circular References in Excel

Unfortunately, there is no one-click option in Excel to remove all circular references at once. You will have to delete them one by one manually. Here are a few ways to do it:

• Delete or modify the problematic formula that’s causing the circle.
• Use simple non-circular formulas instead of complex circular ones.
• Divide a complex circular formula into smaller non-circular parts.
• Copy-paste values only (Paste Special > Values) to break the reference link.
• Trace precedents and dependents to uncover all cells in the circle.

Let’s go through an example to understand how to remove circular formulas in Excel.

### Example of Removing Circular Reference

Consider the worksheet below where cell D4 contains this formula:`=B4*C4+D4`

This refers to cell D4 itself, creating a circular reference. When you enter it, Excel displays a warning message.![Circular Reference Example][]

To fix it, we can simply replace D4 with D3 in the formula to remove the circle:`=B4*C4+D3`

Now the calculation will work correctly without any issues!

While this example was straightforward, some circular references may be complex and difficult to identify. Make use of the Formula Auditing tools described above to trace the links and remove the problematic references.

## When Circular References May be Required

In some rare cases, you may have to deliberately use a circular reference because it provides the most efficient solution.

One common example is when you need to insert today’s date in a cell, but don’t want it to change on recalculation.

Here is one way to do it with a circular formula:`=IF(A1="",TODAY(),A1)`

This will display TODAY() if A1 is blank, else retains the existing value. The circular reference allows the formula to retain the date stamp.

However, it’s recommended to use this method sparingly only when no alternative exists.

## How to Enable Circular References in Excel

Since circular references can cause problems, Excel has an option to control whether they are allowed or not. Here’s how to enable/disable circular formulas:

• Go to File > Options > Formulas.
• In the Calculation options section, check or uncheck the box for Enable iterative calculation.
• You can also set the maximum number of iterations and the amount of acceptable change.![Enable Circular References in Excel][]

By default, this setting is disabled in Excel. When you need circular capability, enable it and be sure to disable it again later for smooth performance.

## Avoid Circular References in Excel

The best way to avoid circular reference headaches is by using simple, non-circular formulas in your worksheets. Split complex formulas into smaller parts and reference predecessor cells instead of self-referencing.

However, errors can happen. Be vigilant in watching for the warning signs and know how to check for circular cells. Trace precedents and dependents to uncover hidden circles.

Frequency of Entities:

Excel: 31
formula: 18
cell: 14
reference: 13
circular: 12
worksheet: 5
precedent: 4
dependent: 4
enable: 3
calculation: 3
warning: 3
iteration: 3
trace: 3
error: 3
value: 2
status bar: 2
auditing: 2
precedes: 1
infinite: 1
debug: 1
corrupt: 1
today: 1
date: 1
timestamp: 1

### Strategies for Allowing Circular Formulas

By default, Excel disables and flags circular references as errors. However, for models requiring circular logic, you can override this under File > Options:

• Navigate to Formulas > Calculation options
• Check the box for “Enable iterative calculation”
• Set the maximum number of iterations Excel will cycle through when encountering circles
• Click OK

Now Excel allows but still flags circular formulas, subject to the iteration limit set. This permits resolvable circulars to function without halting calculations.

However, allowing circular references risks two consequences:

Straining spreadsheet performance: Iterative calculations bog down responsiveness, particularly for large models.

Inaccurate results: Just because Excel iterates doesn’t guarantee circular formulas resolve accurately. The iterations may converge on an incorrect value.

## What Is a Circular Reference?

A circular reference occurs when a formula refers directly or indirectly back to its own cell value, creating an endless cyclical calculation loop.

For example, if cell A1 contains the formula =A1+B1, then A1 references itself. When performing calculations, Excel detects these cycles when a chain of precedents ultimately refers back up the sequence.

Once it encounters a loop, Excel repeatedly iterates through the circular formulas up to its limit, attempting to resolve them. This strains performance and risks inaccurate results if the iterations fail to converge. Prolonged circles can freeze workbooks, requiring force to quit Excel.

## HOW TO LOCATE AND REMOVE CIRCULAR REFERENCES IN EXCEL

How do I remove a circular reference in Excel?

You’ll also see the cell reference listed in the status bar at the bottom of the window. Once you’ve located the cell, you’ll need to alter it to remove the circular reference and repeat this for each example of the problem in your workbook. Removing circular references from formulas will allow Excel to correctly return a result. 3.

What are circular references in Excel?

Circular references in Excel are situations where a formula refers to its own cell or refers to a cell that’s dependent on the formula’s result. While a circular reference usually returns a value of zero, it may also return the cell’s last successful calculation.

What causes a circular reference error in Excel?

For example, cell A3 might contain the formula = (A1+A2)/A3. Formulas like =A1+1 (in cell A1) also cause circular reference errors. While you’re looking, check for indirect references. They happen when you put a formula in cell A1, and it uses another formula in B1 that in turn refers back to cell A1.

How do I Break a circular reference in Excel?

To get started, open the workbook and click on “Formulas” followed by “Circular References.” This action will take you to the offending cell referenced in the circular reference. Next, identify the problem area and adjust the formula to break the circular reference, so it no longer refers to itself.