Summary

A circular reference warning means one of your formulas is asking for its own answer. This article shows you how to read the cell address Excel is already giving you, fix the two everyday causes, and decide whether Enable iterative calculation belongs anywhere near your workbook.

Symptoms

• A dialog appears reading There are one or more circular references where a formula refers to its own cell either directly or indirectly.
• The bottom-left of the status bar shows Circular References: B12, naming the cell.
• A cell that holds a total displays 0 while the numbers above it add up to a real figure.
• Blue arrows with a small circle at one end appear on the grid, linking a formula to itself.
• The warning reappears every time you open the file, even though you changed nothing.
• Formulas elsewhere in the workbook stop updating when you edit their source cells.

In this guide

• 1. Understand What a Circular Reference Is
• 2. Find the Cell Excel Is Complaining About
• 3. Fix Cause One — a SUM That Includes Its Own Cell
• 4. Fix Cause Two — a Chain of Formulas That Loops Back
• 5. Trace the Loop with Trace Precedents
• 6. Decide Whether Iterative Calculation Belongs Here

Before You Start

• Edit permission on the workbook. A file opened in Protected View or as read-only shows the warning without letting you repair it.
• The desktop app for Steps 2 and 5 — Error Checking and Trace Precedents are not in Excel for the web. In the web app, click Editing > Open in Desktop App to get them.
• No admin rights required.

Instructions

1. Understand What a Circular Reference Is

A circular reference is a formula that needs its own answer before that answer exists.

That is the whole concept. The classic version is a column of expenses with a total at the bottom, where the total's SUM range accidentally includes the total cell itself. To work out what goes in B12, Excel needs to know what is already in B12, which is what it was trying to work out. There is no starting point, so it stops and tells you.

Excel refuses to guess. It leaves 0 in the cell, shows the warning once, and then keeps a permanent reminder in the status bar until you clear it. Nothing is corrupted and no data is lost — one formula is unanswerable as written.

The two shapes this takes in real workbooks:

• Direct — the formula names its own cell, usually because a SUM range was dragged one row too far.
• Indirect — cell B11 depends on B12, and B12 depends on B11. Neither formula mentions its own address, so nothing looks wrong on inspection. These loops can run through five or six cells and across sheets.

2. Find the Cell Excel Is Complaining About

Excel has already told you where the problem is. Two places to read it.

From the status bar:

1. Look at the bottom-left of the Excel window, to the right of the word Ready.
2. Read the text Circular References: B12. That address is the cell to open.
3. Click the address. Excel selects the cell and shows its formula in the formula bar.

From the ribbon, when you want the full list:

1. Click the Formulas tab.
2. In the Formula Auditing group, click the small arrow to the right of Error Checking.
3. Hover over Circular References in the menu. A submenu lists every circular cell on the active sheet.
4. Click an address to jump to it. Fix that one, then reopen the menu — the list refreshes, and a workbook with several loops reveals them one at a time.

Note: If the status bar shows the words Circular References with no address after them, the loop is on a different sheet than the one you are looking at. Click each sheet tab in turn; the address appears as soon as you land on the sheet that holds it.

Verification: Once the last loop is gone, the status bar reverts to showing only Ready, and the Circular References entry in the Error Checking menu turns grey and unclickable. That grey entry is your confirmation the workbook is clean.

Read that cell's formula in the formula bar to choose what comes next: a range that swallows the cell's own address, like =SUM(B2:B12) sitting in B12, is Step 3; a formula that names no part of itself is a loop running through other cells, so go to Step 4 — or Step 5 when the chain passes through more than two.

3. Fix Cause One — a SUM That Includes Its Own Cell

This accounts for most warnings and takes about ten seconds to repair.

Here is an expenses sheet on a tab named Expenses, with the total in B12:

Row	A	B
2	Taxi to client site	38.50
3	Hotel, two nights	214.00
10	Parking	12.00
11	Printing	26.40
12	Total	0

Click B12 and read the formula bar:

=SUM(B2:B12)

The range runs to B12, which is the cell holding the formula. Shorten it:

1. Double-click B12 to edit it in place. Excel outlines the referenced range in blue on the grid, and the outline visibly includes the cell you are standing in.
2. Change the end of the range from B12 to B11, or drag the bottom edge of the blue outline up one row.
3. Press Enter. The total appears immediately and the status bar stops mentioning circular references.

=SUM(B2:B11)

This usually happens after someone inserts a row directly above the total and then re-drags the SUM range to pick it up, overshooting by one. A blank spacer row between the last item and the total prevents the repeat: leave row 12 empty, put the total in row 13, and =SUM(B2:B12) has room to be right.

Tip: Select the cell above the total and press Alt + = to insert AutoSum. Excel chooses the range for you and never includes the cell it is writing into.

4. Fix Cause Two — a Chain of Formulas That Loops Back

The indirect version is the one that costs an afternoon, because every formula in the loop looks correct on its own.

An invoice on a tab named Invoice:

Row	A	B	Formula in B
10	Subtotal	4,820.00	=SUM(B2:B9)
11	Volume discount	0	=B12*0.1
12	Total due	0	=B10-B11

Read it in order and the loop appears. B12 needs B11. B11 asks for 10% of B12. B12 is what the sheet is trying to calculate, so neither cell can go first.

The mistake is a business one, not a typing one: the discount was described as "10% off the total", and the total was taken to mean the final amount payable rather than the subtotal it is calculated from.

1. Click B11 and read its formula in the formula bar.
2. Decide which figure the discount is genuinely a percentage of. On an invoice that is the subtotal, in B10.
3. Type the corrected formula and press Enter:

=B10*0.1

4. B11 now returns 482.00 and B12 returns 4,338.00. The warning clears.

The general rule for breaking any loop: find the one cell in the chain that is asking for a figure calculated later than itself, and repoint it at a figure calculated earlier. Every circular chain has at least one such cell, and repointing it is the whole fix.

Warning: Deleting the formula in one looping cell also stops the warning, and it is not a fix — it removes a calculation the sheet needs and leaves a blank where a number belongs. Repoint the reference rather than clearing the cell.

5. Trace the Loop with Trace Precedents

When the chain runs through more than two cells, arrows beat reading formulas.

1. Click the cell named in the status bar.
2. On the Formulas tab, in the Formula Auditing group, click Trace Precedents. Blue arrows appear on the grid, running from every cell that feeds this formula into the selected cell.
3. Click Trace Precedents again. Excel goes back another level, adding arrows into those feeder cells.
4. Keep clicking. The loop is the point where an arrow arrives back at a cell that already has one leaving it — the arrows visibly form a closed shape.
5. Click Remove Arrows in the same group to clear the grid when you have found it.

Tip: Trace Dependents, directly below Trace Precedents, draws the arrows the other way — everything that relies on the selected cell. Running both on a suspect cell shows you the full loop in two clicks rather than walking it a level at a time.

A dashed black arrow ending in a small worksheet-grid icon means the precedent sits on another sheet. Double-click that dashed arrow to open the Go To dialog, which lists the off-sheet address. Cross-sheet loops are the reason a warning sometimes survives what looks like a complete fix.

6. Decide Whether Iterative Calculation Belongs Here

Excel can be told to accept loops and calculate them repeatedly until the numbers settle. The setting exists for a real purpose and gets misused constantly, so be deliberate about it.

Where to find it:

1. Click File > Options. On a Mac, click Excel > Preferences > Calculation.
2. Select Formulas in the left sidebar of the Excel Options window.
3. Under Calculation options at the top, tick Enable iterative calculation.
4. Maximum Iterations (default 100) sets how many passes Excel makes. Maximum Change (default 0.001) tells it to stop early once a pass moves the answer by less than that amount.
5. Click OK.

When this is the correct answer. Some calculations are genuinely circular by design, and the answer is the value the loop converges on:

• Interest calculated on a closing balance that includes the interest.
• A bonus pool set at a percentage of profit after the bonus has been deducted.
• Engineering and financial models that solve by repeated approximation.

In those cases the loop is the method, and iteration is how Excel runs it.

When it is the wrong answer. Turning it on to make a warning go away costs you three things at once:

• It applies to the entire workbook, not the one cell. Every accidental loop you create from that point onward is silently tolerated.
• The number left on the sheet is whatever the loop happened to settle on after 100 passes. It is not flagged, not marked, and looks exactly like a correct figure to the next person who reads the file.
• The setting travels with the file. Everyone who opens that workbook gets the same silence, on their machine, for every sheet in it.

The warning is the only thing telling you a formula is unanswerable. Fix the formula and keep the warning.

Note: To confirm whether iteration is already hiding a problem in a file someone sent you, open File > Options > Formulas and clear Enable iterative calculation. Any loop that was being tolerated announces itself in the status bar within a second.

Troubleshooting

WARNING

Turning on Enable iterative calculation to stop the warning replaces a visible problem with an invisible one. The cell keeps showing a number, that number is whatever the loop settled on rather than a correct result, and nothing on the sheet indicates the difference. Reserve the setting for loops you built on purpose.

Symptom / Error	Potential Cause	Solution
Status bar reads Circular References with no cell address	The loop is on another sheet	Click each sheet tab in turn — the address appears when you reach the sheet holding it (Step 2).
A total displays 0 and the numbers above it are not zero	The SUM range includes the total cell	Shorten the range by one row so it ends above the formula: =SUM(B2:B11) (Step 3).
Warning appears but every cell looks correct	Iterative calculation is already on	Open File > Options > Formulas and clear Enable iterative calculation. The offending cell names itself in the status bar (Step 6).
Fixed one cell and the warning came straight back	A second loop elsewhere in the file	Reopen Formulas > Error Checking > Circular References — the list refreshes after each repair and shows the next one (Step 2).
Trace Precedents draws no arrows	The precedent is on a different sheet	Look for a dashed arrow ending in a worksheet-grid icon and double-click it to open Go To with the off-sheet address (Step 5).
Warning fires the moment the file opens, before any edit	The loop was saved into the workbook	Read the address from the status bar and repair it once. Save, and the warning does not return for the next person (Step 2).
#NAME? or #REF! appears while fixing the range	The edited formula lost a reference	See How to Fix Common Excel Formula Errors for what each error code means and how to rebuild the reference.

Related Articles

• How to Fix Common Excel Formula Errors
• Understanding Absolute and Relative Cell References in Excel
• How to Use Named Ranges in Excel
• How to Use Goal Seek in Excel
• How to Use SUMIF and COUNTIF in Excel
• How to Use IF and IFS Functions in Excel
• Understanding Excel Features — A Quick-Reference Guide to What Excel Can Do

Did this solve your problem?