There are certain times when you try to enter a formula in a cell, and it returns with an error. Weirdly, it tells you something about the circular reference that you haven't heard before. But don't worry. Others face the same issue sometimes as well.

This typically occurs when you force an Excel formula to calculate the same cell where the formula is being executed. As a result, you see an error message, which states the existence of circular references.

Circular references are troublesome in Excel and can affect your workflow sometimes. In this article, you'll learn all about circular references in Excel.

What Is Circular Reference in Excel?

In Excel, a circular reference occurs when a formula repeatedly refers to the same cell. As a result, this creates an endless loop between multiple cells. This will slow down your spreadsheet's processing speed, and it may continue unless you stop it.

Here’s a succinct definition from Microsoft for Circular Reference in Excel: “When an Excel formula refers back to its own cell, either directly or indirectly, it creates a circular reference.”

There are two types of circular references in Excel:

  • Direct circular reference
  • Indirect circular reference

The circular reference that is faced most widely by users is the direct circular reference. This can be a result of a clerical error. Let's explain circular references in more detail.

Examples of Circular Reference in Excel

We discussed above that there are two types of circular references in Excel—Direct and Indirect. Let’s see an example of both cases.

Direct Circular Reference

Direct circular reference is simple to understand. It occurs when the formula of a cell refers to its own cell directly. To illustrate this scenario, we’ll take an example.

In the spreadsheet, you can see we have two values in the cells, A1 and A2. If we type the formula =A1+A2+A3 in the A3 cell, A3 refers to itself directly. Logically, this is not possible.

direct circular reference

Once you press Enter, you’ll see the Circular reference error pop up. If you click OK, Excel returns with value 0.

direct circular reference error

If you take a closer look, you will see that the A1 cell has the value 5, which is added to the A2 cell with the value 10. The result should be 15 in the A3 cell.

However, when you add the A3 cell to the answer, it keeps adding itself again and again and goes to infinity with no definite value. As a result, the A3 cell interprets the result as 0.

direct circular reference example in Excel

Indirect Circular Reference

This circular reference is similar to the direct circular reference. The only difference is that instead of directly referring to its own cell, we do it indirectly. Let’s illustrate this with a scenario.

Let’s take cell D2 with the value 5. And, another cell F4 with value =D2*2. Once you hit Enter, you’ll see the result 10.

indirect circular reference

Now, let’s take another cell B4 with value =F4*2. This will result in a value of 20.

indirect circular reference data in Excel

For the final step, let’s just go to the first cell, which is D2. Now, if we edit the value in the cell to =B4*2, it’ll create a loop between these three cells.

indirect circular reference loop

As a result, you’ll see the circular reference error pop up. If you press Enter, the result will be 0, as usual.

indirect circular reference pop-up in Excel

Related: What Is the XLOOKUP Function in Excel? How to Use It

Finding Circular Reference in Excel

You can find the circular reference in Excel using manually or automatically. We’ll explain both the processes here. While the manual method is difficult to process, you can go with the automatic method if you have a lot of cells to consider.

Let's take a look at an indirect circular reference example from the previous examples.

Manual Method

To manually find the circular reference cell, follow the steps below:

  1. Open the spreadsheet where you want to find the circular reference.
  2. Head over to the Formulas tab in the ribbon menu.
  3. Click on the Show Formulas option. This will show all the formulas mentioned in their respective cells.
    circular reference manual method
  4. Find the cell that’s causing the circular reference in the spreadsheet.
  5. Correct the cell that’s causing the error.
    find circular reference manual method

Now it's done. Even though the manual process is a little tedious, it works best when you have fewer cells to check.

Automatic Method

To automatically find the circular reference cell, follow the steps below:

  1. Open the spreadsheet where you want to find the circular reference.
  2. Head over to the Formulas tab in the ribbon menu.
  3. Click on the down arrow next to the Error Checking option.
  4. From the drop-down, select the Circular References option, then you’ll see the list of cells affected by the circular reference error.
    Circular reference automatic method
  5. After you know the cell, go ahead and correct it.

If you want to find more cells with circular references, keep repeating the steps above, and you’ll be able to find them sooner. You can also look at the left-side bottom of the spreadsheet, which shows the cells with circular references.

Related: Excel Formulas Not Working? Ways to Fix Them

How to Enable/Disable Circular Reference in Excel

By default, iterative calculations are turned off in Excel, this results in frequent circular references popups. If you really want to have circular references in your spreadsheet, follow the steps below to enable iterative calculations.

  1. Click on the File option.
  2. Select the Options menu, and a dialog box will open.
  3. Click on the Formula tab.
    Excel formulas tab
  4. Check the Enable Iterative Calculation option.
  5. Click OK and it’ll be done.
    enable iterative calculation in Excel

If you are on an older version of Excel, and couldn’t find these options, follow these steps:

  1. Click the Office button.
  2. Click on Excel options in the options.
  3. Click the Formula tab.
  4. Check the Iterative Calculations option.
  5. Click OK, and you'll be good to go.

When you turn on Iterative Calculations, you’ll be presented with two options—Maximum Iterations and Maximum Change options.

  • Maximum Iterations: It is used to calculate how many times the formula can recalculate.
  • Maximum Change: It is used for maximum change in between the calculation results.

The default settings for Maximum Iterations are set to 100, while the Maximum Change is set to 0.001. You can change them as per your liking.

Try to Avoid Circular References

The circular reference error is caused by looping cells in a formula. There might be times when circular references are not obvious; it's best to use Excel's automated functions.