What Is a Circular Reference in Excel?

Get FREE Advanced Excel Exercises with Solutions!

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.

Overview of excel circular reference

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:


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


Direct circular reference

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.

Some inter-related cells

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


Indirect circular reference


  • 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.

Excel cell reference message box

Click Here to See the Full Image

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

Indirect circular reference


  • 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.

Due to Circular reference error showing 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.

Finding circular reference using status bar

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.

Finding circular reference using error checking feature

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,


But you have to change it the following way.


Fixing circular reference by changing formula

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.

Indirect reference

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


Fixing circular reference by changing reference

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.


Fixing circular reference by copying formula

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.

Activating iterative calculations

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

Allowing circular reference by activating iterative calculations

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.


Practical example of circular reference

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.


Avoiding circular reference

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.

Using Trace Precedents

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.

using trace precedents to show relationship of cell and formula

  • To remove arrows, click on the Remove Arrows option.

removing arrows

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.

using trace dependents

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

using trace dependents

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.


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

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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Akib Bin Rashid
Akib Bin Rashid

AKIB BIN RASHID, a materials and metallurgical engineer, is passionate about delving into Excel and VBA programming. To him, programming is a valuable time-saving tool for managing data, files, and internet-related tasks. Proficient in MS Office, AutoCAD, Excel, and VBA, he goes beyond the fundamentals. Holding a B.Sc in Materials and Metallurgical Engineering from Bangladesh University of Engineering and Technology, MD AKIB has transitioned into a content development role. Specializing in creating technical content centred around Excel and... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF