Cellwise Try it free

Excel guide · 3 min read

How to find and fix a circular reference in Excel

Quick answerA circular reference is a formula that refers to its own cell, directly or through other cells, such as =SUM(B2:B6) typed into B6. To find it, go to Formulas > Error Checking (click the arrow) > Circular References and click the cell listed. Fix it by changing the range so it stops before the formula's own cell, here =SUM(B2:B5).

Sample data

Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.

AB
1MonthSales
2Jan18400
3Feb21250
4Mar19800
5Apr23600
6Total

What a circular reference is

Excel calculates a cell from the cells it points to. If a formula points back at itself, the answer depends on itself and Excel cannot finish. It shows a warning ("There are one or more circular references...") and usually leaves 0 in the cell.

The most common cause is a total that includes its own row. You type =SUM(B2:B6) in B6, or you insert a row and the range grows to include the total.

Find the cell in two clicks

Excel keeps a list of circular cells. Fix them one at a time; after each fix, the next one appears in the list. The status bar at the bottom left also shows "Circular References: B6" for the active sheet.

  1. Go to the Formulas tab.
  2. Click the small arrow next to Error Checking.
  3. Point to Circular References and click the cell address shown.
  4. Look at that cell's formula and find the reference to itself (Formulas > Trace Precedents draws arrows to help with longer chains).

Fix: stop the range before the formula

For a column total, the range should end on the row just above the total. Once B6 only reads B2:B5, the loop is gone and the real total appears.

=SUM(B2:B5)

Result: 83050

Four months of sales add up to 83,050 once the total stops including itself.

Indirect loops: A depends on B, B depends on A

Sometimes no single formula looks wrong. For example, B6 holds the total and B7 holds a share calculation, =B5/B6, and then someone edits the total to =SUM(B2:B5)+B7. Now B6 needs B7 and B7 needs B6.

Trace Precedents on the listed cell and follow the arrows until one leads back. The fix is usually to calculate one of the values from the raw data instead of from the other result.

=B5/SUM(B2:B5)

Result: 0.28416616496 shows as 28.4% with Percent format

April is about 28.4% of the four-month total, calculated straight from the data so nothing loops.

When a circular reference is on purpose

A few models need one, such as interest that depends on an average balance that includes the interest. Excel can solve these by recalculating repeatedly until the numbers settle.

Only do this when you understand why the loop exists. With iteration on, Excel stops warning you, so accidental loops elsewhere in the workbook go unnoticed.

  1. Go to File > Options > Formulas.
  2. Tick Enable iterative calculation.
  3. Leave Maximum Iterations at 100 and Maximum Change at 0.001 unless you need more precision.
  4. Click OK.

Common mistakes

Including the total row in the SUM range

End the range on the row above the formula, or put totals above the data or in a separate column.

Turning on iterative calculation to make the warning go away

This hides real mistakes. Fix accidental loops instead; use iteration only for models designed to loop.

Using whole-column references next to a total

=SUM(B:B) placed anywhere in column B is circular. Use a fixed range or place the total in another column.

FAQ

How do I find a circular reference in Excel?

Formulas > Error Checking arrow > Circular References lists the cell. The status bar also shows it for the active sheet.

Why does Excel show 0 for a circular reference?

It cannot finish the calculation, so it stops and returns the last value it had, usually 0, after showing a warning.

Can a circular reference be on another sheet?

Yes. A loop can run across sheets. If the status bar says Circular References without a cell address, the loop is on another sheet: switch sheets until the address appears.

Functions used

Related guides

Updated