Replace a Circular Reference With an Excel Formula

Formulas & functions · Updated November 4, 2026

Yes—replace a circular reference by rewriting the calculation so it uses input cells or earlier results, rather than referring back to the cell being calculated. For a running total, for example, put the source values in one column and calculate each cumulative result in another; the right replacement depends on what the circular formula was meant to do.

What a Circular Reference Means

A formula has a circular reference when its calculation depends, directly or indirectly, on its own result. A direct example is a formula entered in A1 that includes A1 as one of its inputs. An indirect loop can involve several cells: A1 depends on B1, B1 depends on C1, and C1 depends on A1. Excel cannot calculate a stable answer from that dependency chain using its normal, one-pass calculation process.

A common cause is typing a total formula into the same cell that is supposed to hold the total. With a value in B1 and the formula in A1, the following formula in A1 refers to itself:

With the formula entered in A1 and an input value in B1, the circular formula is =A1+B1.

Putting the total in a separate cell removes the direct loop. However, if a formula refers to a cell that eventually refers back to it, moving the formula alone will not necessarily resolve an indirect circular reference. The dependency chain has to be checked as well.

Find the Circular Reference in Excel

In Excel for Microsoft 365 on Windows, open Formulas > Error Checking > Circular References. If Excel identifies a circular reference, the menu can show the cell or cells involved; select an address to go to that cell. Excel may also display a circular-reference notice in the status bar. If the menu does not make the whole chain obvious, inspect the formulas in the listed cells and follow their references.

Use Formulas > Trace Precedents to show cells used by the selected formula and Formulas > Trace Dependents to show cells that rely on it. These arrows can help reveal a longer loop. Remove them with Formulas > Remove Arrows when you are done. On Mac, the ribbon’s Formulas tab provides formula-auditing tools too, although the exact layout can vary by Excel version.

Before changing anything, note what the formula is intended to calculate and which cells contain the original inputs. A circular reference can be a sign of a simple misplaced formula, but it can also reveal that the workbook’s calculation plan is unclear. Replacing it without understanding the intended result can produce a formula that calculates without error but gives the wrong answer.

Replace a Self-Referencing Total

For a straightforward total, keep source values separate from the result. If the numbers to add are in B2:B6, put the total in another cell, such as B7. With those values in B2:B6 and the result in B7, use:

=SUM(B2:B6)

This totals the five input cells without including B7 in its own calculation. If the formula is placed inside B2:B6 instead, the range includes the formula cell and recreates the problem. Check both the formula location and its referenced range when adapting the example.

Where a total needs to grow as new rows are added, consider using an Excel Table: select the data and choose Insert > Table. A Table can make formulas and ranges easier to maintain as records are added. It does not automatically fix a circular reference, though; the total still needs to be outside the source values it sums, or otherwise use a non-overlapping range.

Build a Running Total Without a Loop

A running total is a frequent reason people accidentally create a circular reference. One attempted approach is to put the previous total and the new amount into the same result cell, making the cell depend on itself. Instead, store each new amount in one column and each running result in a second column.

For example, assume the changes in quantity are in B2:B4 and the cumulative quantities belong in C2:C4. In C2, enter this formula and fill it down through C4:

=SUM($B$2:B2)

With 12, -3, and 8 in B2, B3, and B4, respectively, the results in C2:C4 are 12, 9, and 17. As the formula is filled down, the first reference stays anchored at B2 while the ending reference advances by row. This calculates each cumulative result from the original input list, without using a running-result cell as its own input.

Another valid layout uses an opening quantity in C1, movements in B2:B4, and running quantities in C2:C4. With an opening quantity of 40 in C1 and the first movement in B2, enter this in C2 and fill down:

=C1+B2

For movements of -3, 8, and -2, the resulting quantities are 37, 45, and 43. Each row refers to the previous row’s completed result and that row’s movement. This is not circular because the dependency moves in one direction: earlier row to later row.

Check the Formula’s Intended Meaning

There is no single replacement formula for every circular reference. The correct structure depends on the business or calculation rule behind it. Before changing a formula, ask whether it should calculate a total, carry a previous result forward, divide a value among categories, or estimate a value that depends on itself. Those are different problems and call for different designs.

  • For a total: keep source values in a distinct range and put the sum elsewhere.
  • For a cumulative result: use one row per step and refer to the previous row, or sum the source values up to the current row.
  • For a calculation based on an output: check whether the output can instead be calculated from independent inputs, then derive dependent values from it.
  • For a deliberately repeated estimate: determine whether the calculation is meant to converge through repeated recalculation. If so, iteration may be intentional rather than an error.

Be especially careful with formulas that combine a manual adjustment and a calculated value in the same cell. A formula replaces the cell’s contents, so the cell cannot also retain a separately typed input. Put the manual adjustment in its own input cell and have the output formula refer to that cell. This makes the calculation easier to audit and prevents the next person from having to guess which part of the result was typed by hand.

When Iterative Calculation Is Appropriate

Some models intentionally use circular references to repeat a calculation until the result changes very little or a set number of calculation steps is reached. In those cases, Excel’s iterative calculation setting can allow the workbook to calculate. This is not the same as replacing the circular reference with a non-circular formula: the loop remains, and the result depends on the iteration settings and the calculation design.

In Excel for Microsoft 365 on Windows, find the setting at File > Options > Formulas, then select Enable iterative calculation. The dialog also provides controls for the maximum number of iterations and the maximum change. On Mac, look under Excel > Settings (or Excel > Preferences in some versions) and the calculation settings. Menu labels and availability can differ between releases.

Do not enable iteration simply to hide a circular-reference warning. A poorly designed loop can return an unexpected result, and changing the iteration limits can change the answer. Use iteration only when the calculation is intentionally iterative, the workbook’s author or specification explains the expected behavior, and the result has been checked against known cases. For an ordinary sum or running total, redesigning the cell references is usually clearer.

Common Problems After Replacing the Formula

  • The warning remains: another cell may still be part of the loop. Check the Circular References list and follow the references until the chain no longer returns to its starting cell.
  • The answer is too large: the sum range may include the output cell, or the same input may appear twice. Compare the referenced range with the actual source data.
  • The running total repeats the same value: the formula may have been pasted without the intended relative references, or the source range may not advance. Check how the formula changes from one row to the next.
  • A new row is missing: the formula may use a fixed range that does not include new data. Extend the range or use a Table-based layout that expands with the data.
  • A number appears as zero or an error: check that the inputs are numeric and that the replacement refers to the intended cells. A formula can be free of circular references and still have incorrect inputs or logic.

After editing, check the results against a small example whose answer you can calculate independently. For a cumulative quantity, add the movements by hand for the first few rows and compare them with the spreadsheet. Also inspect formulas in adjacent rows; one accidentally different reference can make a long column look plausible while returning an incorrect result.

Frequently Asked Questions

Can I replace a circular reference without changing the workbook layout?
Sometimes. If the issue is a formula that includes its own cell in a total range, changing the range or moving the formula to a separate result cell may be enough. If the worksheet has a multi-cell dependency loop, you may need to separate inputs, intermediate calculations, and outputs to make the dependency order clear.

Does enabling iterative calculation fix a circular reference?
It allows Excel to recalculate an intentional circular model repeatedly, subject to iteration settings. It does not remove the circular dependency. For an accidental self-reference, rewrite the calculation rather than turning on iteration as a workaround.

Why does Excel still show a circular reference after I corrected one formula?
There may be another circular reference elsewhere, or an indirect loop that still includes the edited cell. Check the Circular References menu and trace precedents and dependents for the cells it identifies.

Can a running total be calculated without referring to the previous result?
Yes. A cumulative sum can be calculated from the original input range up to the current row. Keeping the inputs separate from the result column makes that structure easy to inspect and avoids a formula that refers to its own output.

Is a circular reference always a mistake?
No. Some specialized models intentionally use iteration, but an unexplained circular reference is usually a reason to inspect the formulas. Confirm the model’s intended calculation and settings before deciding whether to preserve the loop or replace it.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.