No Ticket Needed

Appearance

6 min fix 11 min read Recently reviewedBeginner

By , IT support technician

Quick Answer

Look at the bottom-left of the status bar for Circular References: B12 and click that address to jump to the cell. Remove the cell's own address from its formula — a total in B12 reads =SUM(B2:B11), not =SUM(B2:B12).

How to Fix Circular Reference Warnings in Excel ​

Applies to: Microsoft 365 (Excel for Windows, Excel for Mac, Excel for the web); Excel 2016, 2019, 2021
Article Type: Troubleshooting
Last Updated: 2026-07-29

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.

Prerequisites ​

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

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:

RowAB
2Taxi to client site38.50
3Hotel, two nights214.00
10Parking12.00
11Printing26.40
12Total0

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:

RowABFormula in B
10Subtotal4,820.00=SUM(B2:B9)
11Volume discount0=B12*0.1
12Total due0=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
  1. 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 / ErrorPotential CauseSolution
Status bar reads Circular References with no cell addressThe loop is on another sheetClick 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 zeroThe SUM range includes the total cellShorten the range by one row so it ends above the formula: =SUM(B2:B11) (Step 3).
Warning appears but every cell looks correctIterative calculation is already onOpen 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 backA second loop elsewhere in the fileReopen Formulas > Error Checking > Circular References — the list refreshes after each repair and shows the next one (Step 2).
Trace Precedents draws no arrowsThe precedent is on a different sheetLook 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 editThe loop was saved into the workbookRead 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 rangeThe edited formula lost a referenceSee How to Fix Common Excel Formula Errors for what each error code means and how to rebuild the reference.