How to Find and Remove Circular References in Excel

This short tutorial explains the basics of Excel circular reference and why you should beware of using them. You will also learn how to check, find and remove circular references in Excel worksheets, and if none of the above is an option, how to enable and use circular formulas.

Youve tried to enter some formula in your Excel sheet, but for some reason its not working. Instead, it tells you something about a circular reference. Is this how you ended up on this page? 🙂

Thousands of users are facing the same problem on a daily basis simply because of forcing an Excel formula to calculate its own cell. When you try to do this, Excel throws up the following error message:

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

To put it simply, what Excel is trying to say is this: “Hey, I may get stuck at the round-about. Are you sure you want me to proceed anyway?”

As you can understand, circular references in Excel are troublesome, and the common sense says to avoid them whenever possible. However, there may be some rare cases when an Excel circular reference is the only possible solution for the task you are faced with.

How to enable / disable circular references in Excel

As noted earlier, iterative calculations are usually turned off in Excel be default (in this context, iteration is the repeated recalculation until a specific numeric condition is met). For circular formulas to work, you must enable iterative calculations in your Excel workbook.

In Excel 2010 and higher, click File > Options, go to Formulas, and select the Enable iterative calculation check box under the Calculation options section.

In Excel 2007, click Office button > Excel options > Formulas > Iteration area.

In Excel 2003 and earlier, the Iterative Calculation option resides under Menu > Tools > Options > Calculation tab.

When you turn on iterative calculations, you must specify the following two options:

• Maximum Iterations box – specifies how many times the formula should recalculate. The higher the number of iterations, the more time the calculation takes.
• Maximum Change box – specifies the maximum change between calculation results. The smaller the number, the more accurate result you get and the more time Excel takes to calculate the worksheet.

The default settings are 100 for Maximum Iterations, and 0.001 for Maximum Change. What it means is that Microsoft Excel will stop calculating your circular formula after 100 iterations or after a less than 0.001 change between iterations, whichever comes first.

Using an Excel circular reference – formula example

In one of our previous tutorials, we discussed how to insert todays date in Excel. And an overwhelming majority of questions posted in comments were about how to enter a timestamp in Excel without it changing every time the worksheet is reopened or recalculated. I was very hesitant to reply to those comments because the only solution I know involves circular references, and they should be treated with care. Anyway, here is a very common scenario…

Supposing you have a list of items in column A, and you enter the delivery status in column B. As soon as you type “Yes” in column B, you want the current date and time to be automatically inserted in the same row in column C as a static unchangeable timestamp.

Using a trivial NOW() formula is not an option because this Excel function is volatile, meaning that it updates its value every time the worksheets is re-opened or recalculated. A possible solution is using nested IF functions with a circular reference in the second IF:

=IF(B2="yes", IF(C2="" ,NOW(), C2), "")

Where B2 is the delivery status, and C2 is the cell where you want a timestamp to appear.

In the above formula, the first IF function checks cell B2 for “Yes” (or any other text you supply to the formula), and if the specified text is there, it runs the second IF, otherwise returns an empty string. And the second IF function is a circular formula that fetches the current day and time if C2 doesnt already have a value in it, thus saving all existing time stamps.

Note. For this Excel circular formula to work, you should allow iterative calculations in your worksheet, and this is exactly what we are going to discuss next.

How to find a circular reference in excel (quick and easy fix)

How do I find a circular reference in Excel?

In the drop-down menu, hover over the “Circular References” option to see a list of all circular references in your workbook. To move to the cell containing it, select the cell reference from the menu. Excel will move to highlight the cell containing your circular reference, allowing you to resolve it manually.

How do I check if a formula is a circular reference?

If you can’t find the error, select the Formulas tab, select the arrow next to Error Checking, point to Circular References, and then select the first cell listed in the submenu. Review the formula in the cell. If you can’t determine whether the cell is the cause of the circular reference, select the next cell in the Circular References submenu.

Is there a circular reference between two cells in Excel?

Similarly, on the left side, there is an indirect circular reference too, where cell A2 refers to cell B5, which refers to cell C3, and C3 refers back to cell A2. Here, Excel shows 3 one-way arrows in a triangular shape to mark that there is a circular reference between these three cells.

How to fix a circular reference in Excel?

After selecting ‘Circular References’, Excel will list any instances it has found. It will provide the cell reference of where the circular reference is located. You can then click on the reference to be taken directly to the problematic cell. Once you’ve identified the circular reference, you can proceed to fix it.