What is a circular reference in Excel? Definition and how to fix it

Microsoft's example is as small as the problem gets: a circular reference happens "when a formula refers to itself, directly or indirectly. For example, if cell D3 contains =D1+D2+D3, Excel can't finish the calculation because D3 depends on D3." Almost nobody writes that on purpose. What people write is a SUM that reaches one row too far — Ablebits describes the classic: "you want to add up values in column A with a usual SUM formula, and when doing this you inadvertently include the total cell itself."

What happens when a workbook contains one

Excel

The first one gets a dialog. Microsoft: "The first time Excel finds a circular reference, it displays a warning message." After that the workbook goes quiet — "later circular references might not show the same warning dialog", so a second loop created ten minutes later may arrive with no announcement.

The cell itself shows no error value. Ablebits records the actual behaviour: Excel "displays either a zero (0) or the last calculated value in the cell." That is what makes circular references more dangerous than #REF! or #VALUE! — a zero looks like an answer, and a total silently reading 0 because the formula includes its own cell will be copied into a deck with nothing saying "broken".

The persistent signal is the status bar, which shows "Circular References" and one cell address. Microsoft flags its limit: "if circular references exist on another worksheet, you might only see Circular References without an address."

Google Sheets

Sheets does not use Excel's wording. It reports the loop as a circular dependency and returns #REF! in the cell — Ben Collins' guide to Google Sheets errors lists exactly that among the causes of #REF!, alongside deleted cells and out-of-bounds lookups. The loop surfaces as a reference error rather than as a calculation warning, so the same broken model reads differently depending on which application opened it.

Finding the loop

Excel has a built-in list. Exceljet gives the path plainly: "navigate to Formulas → Error checking → Circular references", and Microsoft documents the same menu. The submenu names the offending cells; clicking one jumps you there. Fix it, reopen the menu, and the next appears — a blank menu means the workbook is clean.

The direct cases are easy. The chained ones cost an afternoon: as Exceljet puts it, a circular reference occurs when a formula "refers directly to its own cell, or refers to another cell that depends on the original cell" — for instance, "if cell A1 contains a formula that refers to B1, and B1 contains a formula that refers to A1." Nothing in either formula looks wrong on its own; the loop exists only between them.

That is the case for arrows. Ablebits points at the two tools built for it: "Trace Precedents — traces cells that provide data to a formula" and "Trace Dependents — traces cells that are dependent on the active cell." Run precedents from the cell the menu named, follow the arrows outward, and the loop closes visibly.

Google Sheets has no equivalent menu. Finding the cycle there means reading the chain the #REF! cell points at by hand — which is why writing formulas you can still read a month later matters more in Sheets than in Excel.

Breaking it

Most fixes are one of three edits.

Shrink the range. The self-including SUM is the majority of real cases: =SUM(B2:B11) written in B11 becomes =SUM(B2:B10). Check the totals row of every block in the sheet, not just the flagged one.

Move the calculation. When two cells genuinely need each other's output — a fee that depends on a total that includes the fee — the answer is usually a third cell holding the intermediate value, so the chain runs forward instead of round. Algebra is often cheaper than iteration: solve for the fee once and the loop disappears.

Delete the leftover. Chains appearing after a restructure are frequently a formula nobody uses any more, still pointing back into the block it was copied out of. If nothing depends on the cell, delete it rather than repair it.

When the loop is deliberate: iterative calculation

A few models are legitimately circular — interest capitalised into a balance that determines the interest, a bonus pool computed from profit after the bonus. For those, Excel can solve the loop by repeated approximation instead of refusing to calculate it. Microsoft documents the switch under File → Options → Formulas on Windows, or Excel → Preferences → Calculation on a Mac.

Two numbers control it: Maximum Iterations caps how many passes Excel makes, and Maximum Change sets how small the difference between passes has to be before it stops. Microsoft states the defaults — "Excel stops after 100 iterations, or when values change by less than 0.001."

Turn it on with your eyes open. Microsoft's own advice is to leave it off — "in most worksheets, keep iterative calculation turned off" — because the setting suppresses the warning for every circular reference in the file, including the accidental ones you have not found yet. Ablebits adds the performance cost: the recalculation repeats until the stopping condition is met, felt on every edit in a large model.

Google Sheets exposes the same idea under File → Settings → Calculation, described in Google's help as: "Iterative calculation: Sets the number of times a formula with a circular reference can occur." The same caution applies, for the same reasons.

How Formula Foundry handles this

Formula Foundry does not detect circular references. That job belongs to the calculation engine, and only Excel and Google Sheets have one — the add-on parses, formats, explains and translates formulas, and never evaluates your workbook. Use the error-checking menu or the status bar to find the loop; nothing here replaces them.

What the add-on changes is the part that takes the time: reading the formulas once you have found them. Click the flagged cell and its formula opens in the sidebar on multiple lines, indented along the nesting, with functions, ranges, constants and named references each in their own colour and matching parentheses highlighted. A range reaching one row too far is visible in a way it never is inside a 200-character line in the formula bar. For chained cases, the AI assistant explains a formula step by step in plain English — often faster than tracing arrows through a nested IF you did not write.

When the fix is to restructure rather than trim a range, the constants are usually the obstacle: a rate typed into six formulas, one of which closes the chain. Global @@variables pull those out into a single named value, and the snippet library keeps the corrected pattern so the next person building the block starts from the version that does not loop. The audit walkthrough shows the sequence on an inherited file.

FAQ