Circular References in Excel: How to Find, Fix or Allow Them

A circular reference happens when a formula refers to its own cell, directly or through other cells. For example, a total in B9 that sums B2:B9 includes itself 🔁

To find a circular reference in Excel:

1. Go to the Formulas tab.
2. In the Formula Auditing group, click the arrow next to Error Checking.
3. Point to Circular References and click the cell address that appears. Excel jumps to that cell.

The status bar at the bottom of the window also shows “Circular References” and a cell address.

To fix it, change the formula in that cell so it no longer refers to itself (for example, =SUM(B2:B8) instead of =SUM(B2:B9)). And if you need the loop on purpose, you can allow circular references with iterative calculation.

Below, I’ll walk you through each step, the most common causes, and what to do when Excel says there’s a circular reference but you can’t find it 👇

To practice along with me, download the free sample workbook for this guide here.

How to find a circular reference

In a small sheet, you can often spot a circular reference yourself. But what if you have a report to finalize in an hour, and the data is spread over hundreds of rows?

Don’t worry. Excel can find circular references for you in seconds⌛

Let’s see it through an example.

In the data set below, we have applied the SUM function to sum the column.

Now, if we change the formula entered in Cell B9 from SUM(B2:B8) to SUM(B2:B9), here’s what happens.

A circular reference error appears. This is because the formula SUM(B2:B9) refers to the same cell (Cell B9) where the formula itself is typed in.

The message says: “There are one or more circular references where a formula refers to its own cell either directly or indirectly.” Excel can’t calculate a formula that depends on its own result, so it warns you instead 😵

When you close the warning, Excel shows either 0 or the last calculated value in the cell. Here it’s zero:

In this example, we exactly know where the circular reference exists for this formula (Cell B9).

But if you don’t know what causes the circular reference, you can still find it as follows:

1. Go to the Formulas Tab > Formula Auditing.
2. Click the arrow next to Error Checking.

3. Hover over Circular References.

Excel shows you the cell reference. Click it and Excel selects the cell for you.

If there’s more than one circular reference, fix the first one and check the menu again for the next.

You can find the circular reference in the status bar as well.

If the status bar only says “Circular References” without a cell address, the circular reference is on another worksheet. Go through your sheets and check the Error Checking menu on each one.

If you still can’t find circular references in an Excel sheet, you can use trace arrows for help.

You can access them from the Formulas tab in the Formula Auditing group. These are represented by blue arrows.

There are two types of arrows, namely:

• Trace Dependents
• Trace Precedents

Pro Tip!

Not in the mood to use your mouse? Not a problem. Press these keys one after another to trace with the keyboard.

For Trace Precedents, press Alt, M, P.

For Trace Dependents, press Alt, M, D.

And to remove the arrows again, press Alt, M, A, A.

Easy, no?

Trace Dependents point toward the cell whose value would be affected in case of change in that cell.

For example, the dependent for Cell B4 is Cell B9. If cell B4 changes, the value in Cell B9 (the sum of the column) would change too.

Trace Precedents show the cells that help you form the final result.

In our case, the sum of cells B2:B8 results in the value in cell B9. So, the trace precedents for cell B9 are all the cells from Cell B2:B8 (and the blue arrow extends across them all).

How to fix a circular reference

Now that we know how to find a circular reference in Excel, let’s see how you can fix one🔧

There’s no button that removes a circular reference for you. You go to each cell Excel lists, and change the formula so it no longer depends on its own result✍

These are the most common causes:

• A total that includes itself. Like =SUM(B2:B9) in cell B9. Fix the range so it stops above the total: =SUM(B2:B8).
• A formula that refers to its own cell. Like =A1+1 in cell A1. Move the formula to another cell, or point it to the right input cell.
• An indirect loop. A1 refers to B1, B1 refers to C1, and C1 refers back to A1. Use Trace Precedents to follow the chain and break it at the cell where the logic goes wrong.
• A whole-column reference. Like =SUM(B:B) typed in column B. Sum a fixed range instead, or put the total in another column.

If your data is small, you might be able to easily spot the cell causing the circular reference and change it. Just like what we did here:

In bigger workbooks, use Error Checking > Circular References to find each cell, and the trace arrows to see which cells feed into it. Then fix the formulas one by one until the “Circular References” text disappears from the status bar.

Allow circular references: iterative calculation

Circular references are not allowed in Excel by default. That’s why Excel shows the warning❗

Some models use a circular reference on purpose, though (for example, a calculation where interest depends on a balance that depends on the interest). Then you can turn on iterative calculation.

But be warned❌ only do this if you need the loop on purpose. With iterative calculation turned on, Excel stops warning you about circular references, so accidental ones go unnoticed.

Nevertheless, to enable iterative calculation in Excel:

1. Go to File > Options > Formulas.
2. Put a checkmark in the Enable iterative calculation box under “Calculation options”.

3. If you want to disable it for some time, simply uncheck the box.

To change the maximum iterations and maximum change:

4. Go to Options > Formulas > Calculation options.
5. Specify the desired value for both.

Maximum Iterations tells Excel how many times to recalculate. The default is 100.

Maximum Change sets the precision. The default is 0.001, so Excel stops recalculating when the values change by less than 0.001 between two iterations.

Excel stops at whichever comes first: 100 iterations, or a change smaller than 0.001 💭

On a Mac, go to Excel > Preferences > Calculation and select Use iterative calculation.

In Excel for the web, formula auditing is limited. If you need to track down a tricky circular reference, open the file in the Excel desktop app.

Circular dependency detected in Google Sheets

Google Sheets has the same rule, just with a different message:

“Circular dependency detected. To resolve with iterative calculation, see File > Settings.”

It means exactly the same thing: the formula refers to its own cell, directly or through other cells. The fix is the same too. Change the formula so the range doesn’t include the cell itself.

If you want the loop on purpose, turn on iterative calculation in Google Sheets:

1. Go to File > Settings.
2. Open the Calculation tab.
3. Set Iterative calculation to On, and set the maximum number of iterations and the threshold.
4. Click Save settings.

Frequently asked questions

What is a circular reference in Excel?

It’s a formula that refers to its own cell, either directly (=A1+1 in cell A1) or indirectly through other cells that refer back to it. Excel can’t calculate it normally, so it shows a warning.

How do I find a circular reference in Excel?

Go to Formulas > Error Checking (click the arrow) > Circular References and click the cell address. The status bar shows the address too.

Why does Excel say there’s a circular reference, but I can’t find it?

It’s probably on another worksheet. When the status bar shows “Circular References” without a cell address, go to each sheet and check Formulas > Error Checking > Circular References there.

How do I remove the circular reference warning?

Fix the formula that causes it (see how to fix a circular reference). Turning on iterative calculation also stops the warning, but only do that if the circular reference is intended.

How do I allow circular references in Excel?

Go to File > Options > Formulas and select Enable iterative calculation. On a Mac: Excel > Preferences > Calculation > Use iterative calculation.

Is a circular reference an error?

Not always. Most circular references are mistakes, but some financial models use them on purpose together with iterative calculation. For other formula errors, see my guide to Excel formula errors.

That’s it! Now what?

In this article, we learned how to find and fix circular references in Excel, and how to allow them with iterative calculation when you need them.

It can be slightly challenging to locate the circular reference in Excel. Especially if you are dealing with a huge dataset.

But you can still find and fix it using the Error checking feature of Excel💪

Just like the error-checking feature of Excel, Excel functions are also of great use. Some of my favorite Excel functions include the VLOOKUP, IF, and SUMIF functions.

To get to know these (and more!) Excel functions, enroll in my 30-minute free email course now.

Other Resources

Did you enjoy reading this article? Seems like you are on the hunt for Excel errors and their solutions.

If so, do check out our other blogs on Excel errors. Like the #VALUE error, #REF Error, #NAME Error, and the IFERROR/ISERROR functions of Excel.