Circular Reference Warning in Excel: Find and Fix It
By ExcelReady TeamUpdated 5 min read
The short answer
A circular reference is a formula that refers to its own cell, directly or through other cells, such as =SUM(B2:B8) typed in B8. To find it, go to Formulas > Error Checking > Circular References, or read the status bar. Fix it by changing the range so it stops before the formula's cell, here =SUM(B2:B7).
Key takeaways
- A circular reference means a formula depends on its own result, directly or through a chain of cells.
- Formulas > Error Checking > Circular References lists the cells involved; the status bar shows one too.
- The most common cause is a total whose range includes the total cell itself.
- Whole-column references like =SUM(B:B) typed in column B are circular.
- Iterative calculation can allow a circular reference on purpose, but most of the time it hides a mistake.
On this page+
A circular reference in Excel is a formula that refers to its own cell, either directly or through other formulas. To calculate it, Excel would first need the answer, so it shows a warning and usually a 0. This guide shows how to find the cell causing the loop, the mistakes that create one, and the rare cases where a circular formula is intentional.
What is a circular reference in Excel?#
A circular reference happens when a formula depends on its own result. There are two kinds.
Direct: the formula includes its own cell. In B8:
=SUM(B2:B8)To add up B2:B8, Excel needs B8, which is the total it's trying to calculate.
Indirect: two or more formulas form a loop. A1 holds =B1+10, and B1 holds =A1*2. Neither formula
mentions its own cell, but each needs the other's answer first.
When you enter one, Excel shows a warning that a formula refers to its own cell, directly or indirectly, and may calculate incorrectly. Click OK and the cell usually shows 0. The warning doesn't come back every time, so a circular reference can sit unnoticed in a file for a long time.
How to find a circular reference#
Excel tells you where the loop is, if you know where to look.
Use Error Checking#
- Go to the Formulas tab.
- Click the arrow next to Error Checking (in the Formula Auditing group).
- Point to Circular References. A list of cells appears.
- Click a cell in the list. Excel selects it so you can read its formula.
If the menu item is greyed out, the active sheet has no circular reference.
Read the status bar#
The status bar at the bottom of the Excel window shows Circular References followed by a cell address, such as
Circular References: B8, when the current sheet has one. If the loop is on another sheet, it shows only Circular
References without an address.
Follow the arrows#
For an indirect loop, click one of the cells and use Formulas > Trace Precedents. Arrows show which cells feed the formula. Follow them until you're back at the start, and you've found the loop. Remove Arrows clears them. Our guide to checking formulas with Trace Precedents and Evaluate covers these tools.
How to fix a circular reference#
The fix is always to break the loop: change one formula so it no longer depends on its own result.
| A | B | |
|---|---|---|
| 1 | Expense | Amount |
| 2 | Rent | 1,200 |
| 3 | Utilities | 185 |
| 4 | Internet | 60 |
| 5 | Supplies | 142 |
| 6 | Travel | 310 |
| 7 | Software | 99 |
| 8 | Total | 0 |
The total in B8 is =SUM(B2:B8). End the range one row above the total:
=SUM(B2:B7)The total is now 1,996 and the warning is gone. For an indirect loop, decide which cell should be the input and type a value there, or rewrite one of the formulas so it uses a different cell. For more on writing ranges, see our beginner's guide to the SUM function and the SUM function reference.
Common causes of circular references#
These patterns cause most circular references:
- A total that includes itself. The range was dragged or typed one row too far, so it ends on the total's own row.
- Whole-column references in the same column.
=SUM(B:B)placed in column B adds itself. Put the total in another column, or use a range that stops above it, like=SUM(B2:B100). - A percentage of a total that includes the percentage column. If a total adds up columns B to D, and column D holds each row's share of that total, the share and the total depend on each other.
- Commission, tax or bonus based on a figure that includes it. For example, a bonus calculated as 10% of profit, where profit is already after the bonus. Work out the formula without the loop, such as profit before bonus.
- Cross-sheet loops. A summary sheet that reads from a detail sheet that reads back from the summary.
A circular reference makes a total wrong without an error code, so it belongs on the same checklist as numbers stored as text and the other causes in why your Excel total is wrong.
When a circular reference is intentional: iterative calculation#
Some models use a loop on purpose, such as an interest calculation that depends on an average balance. For those, Excel can repeat the calculation a set number of times until the result settles. This is called iterative calculation.
- Windows: File > Options > Formulas, tick Enable iterative calculation.
- Mac: Excel > Preferences > Calculation, and turn on the iteration option.
Set Maximum Iterations (how many times Excel recalculates, 100 by default) and Maximum Change (how small the difference between rounds must be before it stops, 0.001 by default).
Be careful with this setting. It applies to the whole workbook, and once it's on, Excel stops warning you about every circular reference, including accidental ones. Unless you built the loop deliberately, fix the formula instead.
What not to do#
Don't wrap a circular formula in IFERROR: a circular reference isn't an error value, so IFERROR doesn't fix or hide it. And don't ignore the warning because the number "looks about right". The cell may show an old value that no longer updates. If a cell shows the formula text instead of any number, that's a different issue; see Excel shows the formula instead of the result.
Practise it#
Fix the circular total in the exercise on this page. Then work through the Debugging SUM lesson, where a circular total is one of three hidden bugs to find and fix in a live sheet.
Try it yourself
This is a live spreadsheet. Type the formula in the green cell and press Enter.
The total in B8 was =SUM(B2:B8), which includes B8 itself, so Excel warned about a circular reference. Fix it: in B8, add up only the expenses in rows 2 to 7.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Amount | |||
| 2 | ||||
| 3 | ||||
| 4 | ||||
| 5 | ||||
| 6 | ||||
| 7 | ||||
| 8 | Total | |||
| 9 | ||||
| 10 | ||||
| 11 |
Tip: End the range on the last expense row, B7, so the total doesn't add itself.
Practise this in a lesson
Frequently asked questions
What is a circular reference in Excel?
It's a formula that uses its own cell, either directly, like =SUM(B2:B8) in B8, or indirectly, when A1 uses B1 and B1 uses A1. Excel can't finish the calculation, so it warns you.
How do I find a circular reference in Excel?
Go to Formulas > Error Checking, point to Circular References and click a cell in the list. The status bar at the bottom of the window also shows a circular reference cell address.
How do I remove a circular reference?
Change the formula so it no longer includes its own cell. For a total, end the range on the row above the total, or move the total outside the range it adds up.
Why does my circular reference show 0?
Excel can't calculate a formula that depends on itself, so it stops and usually shows 0 or the last value it had. The result is not reliable until you remove the loop.
Should I enable iterative calculation?
Only if you built a circular model on purpose and understand it. Turning it on to make the warning go away hides real mistakes in every formula in the workbook.