Excel Circular References: Repair the Loop, Not the Warning

A circular reference is a formula dependency that returns to its starting cell. The return can be immediate, or pass through several other formulas. Repair the unintended dependency rather than replacing the displayed result with a number.

This guide uses ordinary formula inspection and editing in Excel for the web, which Microsoft offers free online. It does not require a paid desktop app. It is deliberately not a promise that the web app can locate every loop in a complex workbook.

Preserve the model before changing it

Use a disposable workbook for the exercise. For a real file, preserve an authorized working copy and record the suspect formula, its address, intended inputs and expected result. Avoid changing a live shared workbook while others are relying on it.

Microsoft’s circular-reference guidance distinguishes direct self-references from indirect loops and warns that auditing tools differ by platform. Its desktop warning and status-bar behavior is not a guaranteed browser diagnostic. A zero, an old value or the absence of a fresh warning is not proof that a formula is sound.

Exercise 1: exclude the total from its own range

In a blank worksheet enter these values. Leave B5 empty initially.

Cell	Entry	Purpose
A1	Item	Heading
B1	Cost	Heading
A2	Paper	Input label
B2	18	Input
A3	Labels	Input label
B3	7	Input
A4	Envelopes	Input label
B4	15	Input
A5	Total	Output label
D1	Unrelated control	Control label
D2	6	Control input
D3	9	Control input
D4	`=D2+D3`	Control result: 15

The intended total is 18 + 7 + 15 = 40. Consider the mistaken formula =SUM(B2:B5) in B5. Its range includes B5 itself. You can diagnose that from the text without deliberately inserting a broken formula into the application.

Select B5. If repairing the mistake, inspect its formula in the formula bar; do not rely on the displayed number.
Identify the intended source rows: B2, B3 and B4. Row 5 is the output, not a fourth cost.
Enter =SUM(B2:B4) in B5 and commit the edit.
Expect B5 to show 40, while D4 remains 15.
Change B3 to 12. Expect B5 to become 45, with D4 still 15. Restore B3 to 7 and expect 40 again.

The changed-input check matters: replacing B5 with the constant 40 could pass the first check but fail the second. Excel for the web supports cell/range references and ordinary formula calculations as described in Microsoft’s web formula overview.

Exercise 2: follow a two-cell loop

Use a separate blank sheet with this intended rule: base cost plus a fixed fee equals total. Put 80 in B2, label A2 Base, label A3 Fee and label A4 Total. Keep the independent control D2 = 6, D3 = 9 and D4 = =D2+D3.

Inspect this hypothetical faulty pair before entering any formulas:

Cell	Faulty formula	Direct dependencies
B3	`=B4-B2`	B4 and B2
B4	`=B2+B3`	B2 and B3

Start at B4: it needs B3. B3 then needs B4. That closed path is B4 → B3 → B4. B2 is a valid input but does not break the cycle. Algebraically the pair reduces to an identity, not a unique fee; it cannot determine the missing fixed charge.

The exercise specification supplies a fee of 12. Enter 12 in B3, then =B2+B3 in B4. Expect 92. Change B2 to 100 and expect 112, while B3 remains 12 and D4 remains 15. Restore B2 to 80 and verify 92.

In a real model, obtain the fee from an authoritative input or business rule. Do not guess 12 simply because it makes the warning disappear. If the fee was meant to be a percentage or an output rather than a fixed input, this repair is not appropriate.

When manual inspection is not enough

For a short chain, write down each referenced formula cell and follow it until you reach an input or return to a cell already on the current path. A repeated cell on that path identifies a loop. Inspect other worksheets when formulas reference them. Do not claim a workbook-wide clearance after examining only one cell.

For a large model with names, indirect references or external dependencies, stop when you cannot establish the intended graph. Preserve the formula text and ask the workbook owner for a sanitized minimal example and intended input/output rules. Do not break links, delete worksheets or replace broad ranges experimentally.

Desktop distinction: Microsoft documents Formulas > Error Checking > Circular References and tracing tools for desktop Excel. They are not required steps in this free-web exercise. The separate desktop dependency-tracing guide is explicitly desktop-scoped.

Why iteration is not the repair here

Iteration repeatedly recalculates a loop under stopping conditions; it does not establish the correct business relationship. The self-including total and underdetermined fee pair above are modeling mistakes, not intentionally convergent models.

Microsoft’s calculation settings reference says the web app cannot change iteration counts or calculation precision. It does document web Formulas > Calculation Options, including Automatic and Calculate Workbook. If a corrected formula appears stale, inspect that setting and recalculate; recalculation alone cannot remove a loop. Do not enable desktop iteration merely to silence a browser symptom.

Confirm the corrected formula still references the intended inputs, not a hard-coded answer.
Run the changed-input case and the unrelated control.
Restore the practice inputs and check all known loops, not just the first.
If the issue is a deleted address rather than a cycle, use the #REF! repair guide.
For a broader diagnosis, start with Excel troubleshooting or review SUM.