In this article, we will explore the concept of Circular Reference in Excel. We’ll understand why it’s important to avoid them and learn how to find them in your worksheet.

We’ll also learn methods to fix or break the circular reference loop. Along the way, we’ll provide examples to help you see how circular references work in practical situations.

By the end of this article, you’ll have a clear understanding of circular references and be able to handle them effectively in your Excel spreadsheets.

Circular references in Excel are like tricky puzzles. They occur when a formula refers back to its own cell or creates a loop of references. These can mess up your calculations and slow down your computer.

Here, we will help you understand and handle circular references effectively. Get ready to solve the puzzle and master circular references in Excel for accurate calculations and smoother performance.

⏷What is Excel Circular Reference

⏵Direct Circular Reference

⏵Indirect Circular Reference

⏷When Does a Circular Reference Appear

⏷Why should You Avoid Circular Reference

⏷Find Circular Reference

⏵Use Status Bar

⏵Activate Error Checking

⏷Remove Circular Reference

⏵Check Cell References of Formula

⏵Check Indirect Reference

⏵Copy Formula to Difference Cell

⏷Enable Circular Reference

⏷Example of Circular Reference

⏷Use Trace Precedents/Dependents

⏷Frequently Asked Questions

⏷Excel Circular Reference: Knowledge Hub

## What is an Excel Circular Reference

In Excel, a circular reference is a formula that refers back to its own cell either directly or indirectly.

It creates a situation where Excel cannot determine the value of the cell because it depends on itself. This leads to an error and can cause incorrect calculations or an infinite loop.

Suppose you have a spreadsheet where cell** A1** is supposed to contain the sum of **A1** and **B1**. In other words, the formula in **A1** is:

`=A1+B1`

This creates a circular reference because the formula is referencing itself directly. Excel will show this as an error.

### 1. Direct Circular Reference

A direct circular reference occurs in Excel when a formula in a cell refers directly back to the same cell. In other words, the formula is self-referential and creates a loop within that cell.

For example, in **D12**, we have the following formula

`=SUM(D5:D12)`

The result is **zero **because a direct circular reference error has occurred here. We have taken **D12** as an element of **the SUM function** and used the formula in **D12**.

### 2. Indirect Circular Reference

An indirect circular reference occurs when multiple cells are dependent on each other. This eventually leads to a cell being dependent on its own value, creating a loop. This happens due to wrong referencing most of the time.

This is a group of cells with simple relationships among them.

Now, if we try to write down the following formula in **C4**, we will get an indirect circular reference.

`=B8*7`

**Explanation: **

- Here,
**D6**depends on**C4**&**B8**depends on**D6**. So, if we try to make**C4**depending on**B8**,**B8**becomes dependent on its own value indirectly.

That’s why an indirect circular reference occurs.

## When Does a Circular Reference Appear?

A circular reference error happens in Excel when you do the following:

- Create a loop where a formula refers to its own cell.
- Create a new loop after removing any previous ones.
- Open a workbook with a loop that hasn’t been resolved.

When this error occurs, you’ll see a message box in the middle of your screen like the following image. The message alerts you by showing that there is a circular reference problem in the workbook, and this might lead to incorrect calculations.

To help you identify the issue, **blue arrows** appear on the spreadsheet, showing the incorrect calculation chain.

**Note:**

- Excel doesn’t keep showing notification messages repeatedly when we use multiple formulas with circular references.
- When we open a saved file in Excel, we might see a warning message saying that there is a circular reference in one or more formulas.

## Why Should You Avoid Circular Reference?

Circular references in Excel cause several problems. Apart from making your workbook slower and showing a warning message, every time you open it (unless you enable a specific setting), circular references can lead to other unexpected issues.

For example, if you accidentally click on a cell that has a circular reference and then edit the cell, the result will be zero.

## How to Find Circular Reference in Excel

### 1. Using Status Bar

The most convenient method to find a circular reference is to check the **Status Bar**. If there is a circular reference, Excel will show it in the **Status Bar**. You will find the **Status Bar** at the bottom of the sheet.

### 2. Activating Error Checking

You can also activate the **Error Checking** feature to find a circular reference.

- Go to the
**Formulas**tab**>>**select**Formula Auditing****>>**select the drop-down beside**Error Checking****>>**get the**cell address**where a**circular reference**exists.

**Read More:** How to Find a Circular Reference in Excel

## How to Remove Excel Circular Reference?

### 1. Check Cell References of Formula

The first method is to check the formula. For example, the initial formula was,

`=SUM(D5:D12)`

But you have to change it the following way.

`=SUM(D5:D11)`

### 2. Check for Indirect References

If the direct reference is ok, then you should look for any error in the indirect reference and fix the error.

In the following example, **C4** and **D6** are related and dependent on each other.

Since the referencing is not correct, you should fix it and change the referencing. The new formula in **D6** is below.

`=SUM(B5+10)`

### 3. Copy Formula to Different Cell

Another method to deal with a circular reference is to copy the formula in a different cell. For example, the formula was initially in **D12**, resulting in an error. But when I have incorporated it in **D13**, it works fine.

`=SUM(D5:D12)`

**Read More:** How to Remove Circular Reference in Excel

## How to Enable Circular Reference in Excel

Circular references can be enabled in Microsoft Excel by activating iterative calculations.

- Go to the
**File**tab**>>**select**Option****>>****Formulas**. **Excel Options**window will appear. Tick the**Enable iterative calculation**box.- Set
**Maximum Iteration**to**1**and**Maximum Change**to**001****>>**click**OK**.

Now, you will see that Excel will get you the correct result.

**Note:**While activating iterative calculation, you should understand some topics.

**Maximum Iterations:** This determines how many times Excel will keep calculating until it reaches a final result. You can choose the number of iterations you want, but keep in mind that higher numbers may take more time and computer resources.

**Maximum Change:** This setting defines the level of accuracy for the calculations. It represents the maximum allowable difference between each iteration. A smaller maximum change value means more precise results. By default, Excel uses a value of **0.001** for maximum change.

**Read More:** How to Allow Circular Reference in Excel

## Example of Excel Circular Reference

Sometimes, you can use a circular reference in Excel. I am showing you a potential example of the use of a circular reference in this section.

Suppose we have a list of **Product(s)**, their **Delivery** status, and **Time** of delivery. You can find the **Time** of delivery using **the NOW function**. Insert the following formula on cell **D5**.

`=IF(C5="Yes",IF(D5="",NOW(),D5),"")`

**Explanation: **In the formula mentioned, there are two parts.

**IF(C5=”Yes”**,IF(D5=””,NOW(),D5)**,””)**

The first part checks if cell **C5** contains a specific text (like “**Yes**” or any text you choose). If it does, the formula moves on to the second part. If not, it leaves the result empty.

- =IF(C5=”Yes”,
**IF(D5=””,NOW()**,D5),””)

The second part looks at cell **D5** and does something only if it doesn’t already have a value in it. In this case, it fills in the time by using the** NOW** function. This helps keep any existing time information in **D5** while adding the current time if **D5** is empty.

Even though the formula works fine here, you should always avoid a circular reference and find an alternate way. In this case, we can use the following alternative formula.

`=IF(C5="Yes",NOW(),"")`

This formula avoids a circular reference.

## How to Use Trace Precedents/Dependents to Check Circular Reference

Excel’s **Trace Precedents** and **Trace Dependents** feature can give you indications about the relationship of the formulas with the cell. **Trace Precedents** draws arrow to show the cell or cell range which are used in the formula. On the other hand, **Trace Dependents** shows the relationship of the selected cell with the formula by drawing arrow.

You can use this feature to see if you have used any circular reference or not. Suppose I have accidentally selected a wrong range of data inside the** SUM** formula. Now, if I want to see the range of cells I have selected in the formula, I can use **Trace Precedents**. Follow the steps:

- Go to the
**Formulas**Tab >> select the cell**D12**which contains the formula >> click on the**Trace Precedents**option under**Formula Auditing**group of commands.

Therefore, you see an arrow is drawn showing the range of cells. Now, I can easily see that, I have accidentally included the cell **D12. **This cell contains the formula itself. So, it will give a circular reference.

- To remove arrows, click on the
**Remove Arrows**option.

If you want to see the relation of a cell with the formula, you can use the **Trace Dependents** option.

- Go to the
**Formulas**Tab >> select the cell**D7**which is used in a formula >> click on the**Trace Dependents**option under**Formula Auditing**group of commands.

As a result, an arrow has been drawn to show the relation of the **D7** cell with the formula where **D7** cell is used.

## Frequently Asked Questions

### 1. Why should I use a circular reference in Excel?

**Answer:** Circular references are typically used when you need the formula to update continuously based on its own result. It can be helpful for iterative calculations or when you want to create dynamic models.

### 2. Are circular references always bad in Excel?

**Answer:** Circular references are not always bad, but they need to be used with caution. In some cases, they can solve specific problems or create dynamic models. However, if not managed properly, circular references can lead to incorrect results or infinite loops.

### 3. How can I troubleshoot circular references in Excel?

**Answer**: Excel provides tools to help troubleshoot circular references. You can use the “Circular References” option in the “**Formulas**” tab to locate and analyze circular references in your worksheet. It also provides suggestions to resolve them.

**Download Practice Workbook**

You can download this practice workbook while going through the article.

## Conclusion

In this article, we have explored the nature of Excel circular reference, understood why they should generally be avoided, and learned how to identify and manage them effectively. Understanding circular references in Excel is crucial for getting accurate results and keeping your spreadsheets running smoothly.

Remember to be careful and use the techniques we discussed to break circular loops or enable iterative calculation when necessary. With these skills, you can confidently solve any circular reference issues and make the most of Excel’s data analysis features.

## Excel Circular Reference: Knowledge Hub

- Excel Iterative Calculation Not Working
- Enable Iterative Calculation
- Fix Circular Reference Error
- Fix Circular Reference That Cannot be Listed

**<< Go Back to Cell Reference in Excel | Excel Formulas | Learn Excel**