# The Causes and Solutions of Circular Reference in Excel

Sometimes you may encounter a common issue known as a circular reference. This problem occurs when an Excel formula in a cell tries to include itself in the formula. This problem can slow down your productivity sometimes, especially when your dataset is large. In this article, I will explore what circular reference means in Excel, its causes, and how to identify and resolve it.

## What is a Circular Reference in Excel?

A circular reference occurs in Excel when a formula refers back to the cell it is supposed to calculate. In other words, a circular reference occurs when a formula refers to the same cell that contains the formula. This results in a feedback loop, where Excel tries to calculate the formula, but the formula depends on the result of the formula, creating a never-ending loop.

## An Example of a Circular Reference in Excel

Suppose I have a budget spreadsheet where I want to calculate the total amount of expenses based on the following expenses:

When calculating the total expenses in **cell** **B5**, suppose I mistakenly added **B5** to the formula.

Now the value of **cell B5** depends on its own value because the formula contains **cell B5**. It creates a never-ending loop in the cell. The circular reference creates a loop that Excel cannot break, resulting in an infinite calculation.

When this type of error occurs, a warning message will pop up on the screen instantly.

## What Causes Circular References in Excel?

Circular references can be caused by different factors. Some of the most common causes include:

- Accidentally entering a formula that refers to the cell it’s supposed to be calculating.
- Creating a circular reference intentionally to achieve a specific result.
- Using a formula that refers to a range of cells that includes the cell that contains the formula.
- Using iterative calculations in Excel. Iterative calculations are a feature that allows a formula to repeatedly calculate a cell until a specific condition is met.

## How to Identify Circular References in Excel?

### 1. Warning Message

Excel will detect circular references and display a warning message in the formula bar when a circular reference occurs.

After seeing this warning message, recheck the formula input again to find the error.

### 2. Error Checking

Excel also has a built-in tool that can help identify circular references. To use this tool:

**Step_1**: Go to the **Formulas** tab in the ribbon.

**Step_2**: Click on the **Error Checking** dropdown menu and take the cursor to **Circular References**.

The submenu of **Circular References** will show the errors.

**Step_3:** Click on the cell from the **Circular References** submenu.

It will take you directly to the cell.

## How to Resolve Circular References in Excel?

Excel provides different ways to resolve circular references, depending on the situation. Some of the common methods include:

### Solution #1: Change the Formula to Resolve Circular References in Excel

The easiest way to resolve a circular reference error is to check and correct the formula that is causing the error. You can either modify the formula or delete it entirely.

### Solution #2: Use Iterative Calculations to Resolve Circular References in Excel

In some cases, iterative calculations can help resolve circular references. To do this:

**Step_1:** Go to the **File** menu.

**Step_2:** Select **Options**.

**Step_3**: Select the **Formulas** tab.

**Step_4**: Now check the **Enable iterative calculations** box.

**Step_5**: Finally hit the **OK** button to apply the changes.

### Solution #3: Use a Macro to Resolve Circular References in Excel

If the circular reference is too complex, you can use a **Macro** to help resolve it.

**Step_1**: Press **Alt + F11** to open the **Visual Basic Editor**.

**Step_2**: In the **Project** window, right-click the **worksheet > Insert > Module**.

**Step_3**: In the new module, enter the following code:

```
Sub SolveCircularReference()
Application.Calculation = xlCalculationManual
Do
On Error Resume Next
Application.Calculate
Loop Until Err.Number = 0
Application.Calculation = xlCalculationAutomatic
End Sub
```

**Step_4**: Press **F5** to run the macro.

**Step_5**: Now select the cell with the circular reference.

**Step_6**: Then go to the **Developer** tab.

**Step_7**: Click on the Macros icon.

The **Macro **dialog box will appear on the screen.

**Step_8**: Finally, hit the **Run** button from the **Macro dialog box**.

## Conclusion

Circular references are a common issue in Excel that can cause calculation errors and slow down performance. I hope this article helped you with the solutions to resolve all the problems with circular references. Thank you!

**Excel Glossary** | **Cell Reference** | **Reference Style** | **Absolute Cell Reference** | **Relative Cell Reference** | **Mixed Cell Reference**