Circular References: The Bonus Pool That Has to Know Its Own Answer Before It Can Give One

The scheme is one sentence long, and finance has been running it for six years: the bonus pool is ten per cent of group profit after the bonus.

Write that down the way it is written down. Profit before bonus is 1,204,500. The pool is ten per cent of profit after the pool. So the pool is =0.1*(H3-H4), sitting in H4, reading H4.

Excel shows a warning, puts 0 in the cell, and draws a blue arrow with a small circle on it. The pool is now zero, profit after bonus is 1,204,500, and the model is telling you the scheme costs nothing.

Almost everybody's next move is to give up on the definition and use profit before bonus instead. That produces 120,450. The correct answer is 109,500. Sixty seconds of algebra, or one checkbox, separates them — and the difference, 10,950, is real money that a reasonable-looking spreadsheet will hand out every year without anybody noticing, because 120,450 is exactly the number a rough mental check produces.

A circular reference is not always a mistake. Sometimes it is the problem stating itself honestly, and the question is which of the three ways out you take.

What this covers. Everything here works in every version of Excel this century, plus Excel for the web with minor menu differences (the web version reads the iterative-calculation setting stored in a workbook but has no dialog to change it). LET in section 9 needs Excel 2021 or Microsoft 365. Nothing here needs VBA.

1) What Excel Actually Means by "Circular Reference"

A circular reference is a formula that depends on its own cell — directly, or through any chain of cells however long.

H4: =0.1*(H3-H4) is the direct kind, and it is obvious once you see it. The chain kind is not: B1 reads B2, B2 reads B3, B3 reads B1, and not one of those three formulas looks wrong on its own. Excel does not care about the length of the loop. It cares that it cannot find a cell to calculate first.

That is the whole problem. Excel calculates by dependency order: everything a formula needs must have a value before the formula runs. A loop has no starting point, so there is no order, so Excel stops and gives you four signals, of which you will probably only notice the first:

The second row is the one that costs money. A circular reference does not produce #REF! or #VALUE!. It produces zero, and zero is a plausible number. A bonus pool of zero looks like a bad year. A freight accrual of zero looks like a month with no shipments. Nothing about the cell says "I could not be calculated" except a line of grey text at the bottom of a window most people have their eyes above.

2) The Accidental Circle: A Total That Includes Itself

Before the interesting kind, the common kind — and it is common because it is created by a keystroke, not by a decision.

The grid has six divisions in rows 2 to 7. The total goes in E8:

E8:  =SUM(E2:E8)      → 0, and "Circular References: E8" in the status bar
E8:  =SUM(E2:E7)      → 1,204,500

Nobody types the first one on purpose. It arrives three ways, all of them mouse-driven:

1. Overshooting the drag. You type =SUM( in E8 and drag from E2 downwards, and the drag runs one row too far — into the cell you are typing in. The formula bar shows E2:E8 and looks entirely normal, because it is one character different from the thing you meant.
2. Extending an existing total to catch a new row. A division gets added, you click into the total, grab the blue range handle, and pull it down past the last division and onto the total's own row.
3. Moving the data underneath the total. Cut E2:E7, paste it starting at E3, and the total's range follows the data to E3:E8 — which is now the cell the total lives in. The reference updating is Excel doing exactly what it should; the collision is the accident.

The reason this one is worth its own section is what it does not look like. A total that includes itself does not double, and it does not error. It reads zero — so the first symptom is usually a percentage elsewhere on the sheet showing #DIV/0!, and you go and debug the percentage.

One habit kills it permanently: keep the total physically outside the block it sums, with a blank row between them, so no drag, extend or paste can bring the two into contact. Note that SUBTOTAL buys you nothing here — =SUBTOTAL(9,E2:E8) written in E8 is just as circular as the SUM, and warns the same way. Its trick of ignoring other SUBTOTAL cells inside its range is about not double-counting nested totals, not about self-reference.

3) The Chain Circle: Three Cells, None of Which Looks Wrong

Here is a real one, from an overhead allocation that had been in production for a year:

H10: =H12*0.15          Central overhead recharged to Divisions
H11: =SUM(E2:E7)-H10    Profit after recharge
H12: =H11+H10           Profit before recharge

Read them one at a time and each is defensible. Read them together and H10 needs H12, which needs H11, which needs H10. The status bar says Circular References: H10 and names exactly one cell, which is the least useful thing it could tell you: H10 is not the mistake, it is just the cell Excel gave up on first.

The mistake is H12. Profit before recharge is =SUM(E2:E7) — it is a fact about the divisions and it has nothing to do with the recharge. Somebody derived it from the two numbers below it instead, and turned a definition into a loop.

The diagnostic question is not "which cell is circular?" It is "which cell in this loop should have been an input?" Almost every accidental chain circle has one link that was written as a derivation and should have been a direct reference to source data. Find that link and the loop opens by itself.

4) Finding It: The Status Bar, the Menu, and the Arrow

You will meet a circular reference on a sheet you did not write, with no warning dialog because somebody clicked OK on it in 2024. Three tools, in the order worth using them:

Formulas ▸ Error Checking ▸ Circular References. A submenu listing circular cells. Click one and Excel selects it. It shows only the circles on the active sheet, and in most versions only a handful at a time — so fix one, come back, and check the menu again. It is greyed out when the workbook is clean, which makes it a genuine all-clear.

The status bar. Circular References: E8 on the bottom left. Worth knowing its two blind spots:

• If the circle is on a different sheet from the one you are looking at, the status bar says Circular References with no cell address at all. That blank is not a glitch — it is Excel telling you to go and look at the other tabs.
• If iterative calculation is switched on, the status bar says nothing whatsoever, on any sheet. Every circle in the workbook goes silent. Section 7 is about why that is a bigger deal than it sounds.

Formulas ▸ Trace Precedents. Select a cell in the loop and press it repeatedly. Excel draws arrows back through the chain, and when it reaches a cell it has already drawn, the arrow gets a small circular marker. For a three-cell loop this is faster than reading the formulas; for a fifteen-cell loop across two sheets it is the only thing that works. Formulas ▸ Remove Arrows cleans up after.

5) Why One Bad Cell Can Take the Whole Workbook Down With It

This surprises people, and it is the reason a circular reference is worth fixing the hour you find it rather than the week after.

When Excel hits a circle with iteration off, it does not calculate the loop and move on. It abandons the calculation pass. Formulas that had nothing to do with the loop can be left holding whatever value they had before, and they will keep showing it — a stale number, correctly formatted, in a cell whose inputs have since changed.

You can watch it happen. Break E8 into =SUM(E2:E8), then change a division's revenue in B4. In a clean workbook every dependent number moves. In this one, some of them do not, until you force a full rebuild with Ctrl+Alt+F9.

Two practical consequences:

1. A circular reference is not a local problem. It is a workbook that is no longer guaranteed to be recalculating, and any number in it may be stale. Do not trust a figure you read off a workbook whose status bar says Circular References, even a figure on a different tab.
2. Ctrl+Alt+F9 is the honest refresh. F9 recalculates what Excel thinks is dirty. Ctrl+Alt+F9 rebuilds everything from scratch, which is what you want after fixing a circle — and is the difference between "the number changed" and "the number is right".

6) The Deliberate Circle: When the Definition Really Is Circular

Now the interesting case. Some quantities genuinely depend on themselves, and no amount of tidying makes the dependency go away, because it is in the business rule rather than in the spreadsheet:

• A bonus pool defined on profit after the bonus. The one in the grid.
• Corporation tax on profit after a charge that is itself deductible. Tax needs profit, profit needs tax.
• Interest on the average balance of a facility, where the interest is capitalised into that balance.
• Reciprocal service-department allocation: IT charges Facilities, Facilities charges IT, and each department's total cost includes a share of the other's total cost.
• A commission on net revenue after commission, which is the same shape as the bonus and appears in half the sales models ever written.

The bonus pool in the grid, written as it is defined:

H2:  0.10                  Bonus rate
H3:  =SUM(E2:E7)           Profit before bonus     → 1,204,500
H4:  =H2*(H3-H4)           Bonus pool              → circular
H5:  =H3-H4                Profit after bonus

H4 is a legitimate formula that states the scheme correctly and cannot be computed in one pass. You have exactly three options, and it is worth being clear that they are not equally good:

Row one is how most models actually handle it, and nobody writes down that they did.

7) Iterative Calculation: The Switch, and What Its Two Numbers Mean

File ▸ Options ▸ Formulas ▸ Calculation options ▸ Enable iterative calculation. On a Mac it is Excel ▸ Preferences ▸ Calculation.

Ticking it changes what Excel does with a loop. Instead of refusing, it guesses zero, calculates the loop, feeds the result back in, and repeats — hoping the numbers settle down. Watch it settle on our bonus pool, where each pass is B = 0.1 × (1,204,500 − B):

Each pass overshoots by a tenth of the last overshoot, so the error dies by a factor of ten every time and lands on 109,500 — the same number section 9 gets algebraically. That behaviour is not luck, and section 8 is about when it does not happen.

The two boxes underneath:

• Maximum Iterations (default 100). The hard stop. Excel does at most this many passes per calculation, converged or not.
• Maximum Change (default 0.001). The soft stop. If no cell in the loop moves by more than this between passes, Excel calls it done and stops early. In the table above that happens at pass 8.

Maximum Change is a stop condition, not an accuracy guarantee, and the distinction matters when you are working in whole currency units. A value still moving by 0.0009 per pass in a slowly-converging loop can be tens of units away from its final answer — the remaining distance is roughly the last change divided by (1 − the ratio between changes), which for a loop shrinking by 1% per pass is a hundred times the change you just measured. For money, set Maximum Change to something small relative to the amounts involved — 0.0000001 on a six-figure pool costs nothing you will notice and removes the question entirely.

Three properties of this switch that catch people out:

1. It is saved in the workbook, but it applies to the whole Excel session. Open your iterative model, then open a colleague's file in the same Excel window, and their file is now iterating too — silently resolving any circular reference it happens to contain instead of warning them about it. Open the iterative model second and it takes effect for both. This is the single most under-appreciated fact about the feature.
2. It silences the status bar completely. With iteration on there is no Circular References indicator anywhere, on any sheet, ever. The one warning that catches accidental circles is gone for every file open in that session.
3. The starting point is whatever the cells already held, so a workbook that has been recalculated can converge to slightly different last digits than one just opened. Deterministic enough for money, not deterministic enough for a hash or a strict file-comparison test.

8) When Iteration Does Not Converge — and How It Lies About It

The bonus loop converged because each pass multiplied the error by 0.1. Turn that multiplier up and the same machinery does something else entirely.

A1: =A2*2      Each pass doubles the error
A2: =A1+1000

Every pass makes the numbers bigger. After 100 iterations Excel stops, because Maximum Iterations says so, and leaves whatever enormous number it had reached sitting in the cell. There is no error, no warning, and no indication that the answer is not an answer. With iteration on, a divergent loop looks exactly like a converged one: a number, in a cell, correctly formatted.

Three failure modes, all of which end with a plausible-looking number:

• Divergence. The gain around the loop is greater than 1 and the values run away. Usually recognisable because the result is absurd, but not always — a loop that grows 3% per pass over 100 passes lands nineteen times too high, which on a cost line just reads as "expensive".
• Oscillation. The values flip between two states forever and Maximum Change is never met. Excel stops at 100 iterations on whichever half of the flip-flop it happened to be on. ROUND inside a loop is a common cause: =ROUND(H2*(H3-H4),-2) can bounce between 109,500 and 109,400 indefinitely, because rounding keeps re-injecting the error the iteration is trying to remove.
• Stalling. The loop is converging, but too slowly to finish in 100 passes. The result is a partly-converged number — closer than the first guess, not equal to the answer.

The test that separates all three from a real result takes ten seconds. Set Maximum Iterations to 1000, recalculate with Ctrl+Alt+F9, and look at the number. If it moved, it had not converged and you were reading a value that just happened to be where Excel ran out of passes. If it is identical, you have a genuine fixed point. Do this once for every iterative model you inherit, before you believe any number in it.

Worth stating plainly: IFERROR does nothing here. A circular reference is not an error value, so there is nothing for IFERROR to catch, and wrapping the formula in it hides no problem while adding one more layer to read through. The same goes for IFNA. If you have written =IFERROR(0.1*(H3-H4),0) hoping to tidy the zero away, you have written a formula that returns zero for a completely different reason.

9) The Algebra Is Usually Better Than the Switch

The bonus rule, as one line of school algebra. Let B be the pool, P profit before bonus, r the rate:

B = r × (P − B)
B = rP − rB
B + rB = rP
B(1 + r) = rP
B = rP ÷ (1 + r)

So:

H4:  =H2*H3/(1+H2)         → 109,500.00   exactly, one pass, no settings
H5:  =H3-H4                → 1,095,000.00

Check it against the definition: ten per cent of 1,095,000 is 109,500. The scheme is satisfied exactly, by a formula with no loop in it.

This is the answer for the overwhelming majority of deliberate circles, because the shape x = a + b·x — a quantity that is some base plus a proportion of itself — covers almost all of them, and it rearranges to x = a ÷ (1 − b) every time. Interest capitalised into a balance, commission on net-of-commission revenue, a management charge levied on costs including the charge: same algebra, different labels.

With LET, the derived version documents itself:

=LET(profit, SUM(E2:E7),
     rate,   H2,
     pool,   rate*profit/(1+rate),
     ROUND(pool, 2))

Three reasons to prefer this to the checkbox, none of them stylistic:

1. It computes in one pass, so the number does not depend on how many times the sheet has been recalculated, and it cannot be a partly-converged value.
2. It leaves the circular-reference warning armed. The next accidental self-referencing total in this workbook still gets caught, because you never turned the alarm off.
3. It survives the file being opened somewhere else. Excel for the web, a viewer, a colleague who has iteration off — the closed form gives everyone the same number, and the iterative version gives them zero, or a warning, or a silently different value.

Where algebra genuinely runs out — reciprocal allocations across four or five departments, a debt schedule where interest depends on an average balance that depends on a repayment that depends on cash after interest — the honest answers are a small matrix solve, a manually unrolled schedule of a dozen passes down the sheet, or iteration used deliberately, documented on the sheet, with the convergence test from section 8 written into the model as a check cell.

10) The Two Circles Iteration Cannot Fix

A genuine mistake. This is the real cost of leaving the switch on, and it is worth being blunt about it: with iteration enabled, a typo that makes E8 sum itself no longer reads zero and no longer shows a status bar warning. It converges — to some number, calmly, without complaint. A self-including sum is a loop with a gain of 1, which does not converge at all, but Excel will stop after 100 passes and leave a figure there anyway. Iteration turns a loud, obvious bug into a quiet, plausible one. That is why the recommendation is not "turn it on and forget it" but "turn it on for the model that needs it, and turn it off again".

If you must ship a workbook with iteration on, put the fact on the sheet. A cell that reads Iterative calculation: ON (100, 0.0000001) — required by H4 costs one row and answers the question the next person will otherwise spend an afternoon on.

A circle that runs through a closed workbook. If Summary.xlsx!A1 reads a cell in Detail.xlsx that reads back into Summary.xlsx, Excel can only see the loop when both files are open. With Detail.xlsx closed, the external reference is served from the value cached inside your own file, the loop is invisible, and everything looks fine — until somebody opens both, at which point a warning appears in a file that has been "working" for months. Iteration does not fix this; it just decides which of the two cached versions wins. The fix is structural: one direction of flow between files, always.

11) The Timestamp Trick, and Why It Is Fragile

The one iterative formula that ends up in workbooks belonging to people who have never heard of iterative calculation. Somebody types a division's revenue in B4, and I4 records when:

I4:  =IF(B4<>"", IF(I4="", NOW(), I4), "")

Read the loop: if B4 has something in it, and I4 is empty, put the current time in I4; otherwise keep whatever I4 already has. It needs iteration on, because it reads itself, and it works — it stamps once and then holds.

It is also one of the most fragile things you can put in a spreadsheet, for reasons worth knowing before you rely on it:

• NOW() is volatile, so it re-evaluates on every recalculation. The IF(I4="") guard is the only thing stopping the stamp from following the clock, and the guard is a formula like any other.
• Clearing B4 clears the stamp, permanently. Re-entering the value writes a new one. The audit trail this looks like is not one.
• It carries iteration with it. The workbook now enables iteration for every file open beside it, per section 7.
• Copying the cell copies the loop, and a paste into the wrong place makes a circle nobody intended — which is now silent, because iteration is on.

For a shared tracker, a Power Automate flow, a form, or the change-tracking built into a shared workbook all beat it. For a personal sheet where a wrong timestamp costs nothing, it is fine, and worth understanding as the clearest small example of an intentional self-reference there is.

12) Nine Traps, Briefly

1. A circular reference returns 0, not an error. No red, no #, nothing to filter on. The status bar is the only signal, and only when iteration is off.
2. The status bar shows no address when the circle is on another sheet. The word without a cell reference means "go and look at the other tabs".
3. Iteration silences the warning for every workbook open in that session, not just yours.
4. Maximum Change is a stop condition, not a precision guarantee. At the default 0.001 a slowly-converging loop can stop tens of units short.
5. A divergent loop looks identical to a converged one. Test by raising Maximum Iterations to 1000 and checking whether the number moves.
6. ROUND inside an iterative loop can cause permanent oscillation. Round the result outside the loop, not inside it.
7. IFERROR cannot catch a circular reference, because it is not an error value. Neither can IFNA or ISERROR.
8. One circle can leave unrelated formulas stale, because Excel abandons the calculation pass. Ctrl+Alt+F9 is the honest rebuild.
9. A conditional-format or data-validation formula can be circular too, and Excel's warning for those is even quieter than the one for cells.

13) Mini Exercises

Copy the grid into a blank sheet starting at A1. Divisions in rows 2 to 7, so E2:E7 is the profit column.

1. Break it on purpose. Put =SUM(E2:E8) in E8. Write down what the cell shows, what the status bar shows, and what =E8/H3 shows two cells away. Then fix it.
2. The pool, three ways. Compute the bonus pool as 10% of profit before bonus, then with iteration on and =H2*(H3-H4), then with the closed form. Give all three numbers and say which one the scheme actually specifies.
3. Prove the closed form. Write one formula that returns TRUE if the pool from section 9 really is ten per cent of profit after that pool. (Beware exact float comparison — decide what tolerance you want and use ROUND.)
4. Break the convergence. Wrap the iterative pool in =ROUND(H2*(H3-H4),-2) and recalculate ten times with F9. Describe what happens and explain why in one sentence.
5. A rate that will not settle. At what bonus rate does the iterative version stop converging, and what does the closed form give at that rate? (The answer says something about which method you should trust.)
6. Open the loop. Given H10: =H12*0.15, H11: =SUM(E2:E7)-H10, H12: =H11+H10, identify which single cell should have been a direct reference to source data, and rewrite it.

Summary

A circular reference is Excel refusing to guess. It has no starting point, so it stops, and it tells you by putting a zero in a cell and a line of grey text at the bottom of the window — the quietest failure in the application, and the only one that produces a number you might use.

Most of them are accidents with one bad link: a total inside its own range, or a definition written as a derivation. Find the cell in the loop that should have been an input, and the loop opens.

The rest are real. Some quantities genuinely are defined in terms of themselves, and for those the checkbox is the famous answer and the wrong default. Iteration is a numerical method with a stop condition, silently applied to every file open beside yours, converting the alarm for accidental circles into silence. The algebra takes two minutes, gives an exact answer in one pass, and leaves the alarm armed.

The bonus scheme was never ambiguous. Ten per cent of profit after the bonus is 109,500, and every year the model paid 120,450, because the honest formula showed a zero and the wrong one showed a number.