A circular reference is a formula that refers to its own cell, directly or through other formulas. Typing =SUM(B2:B7) into B7 creates one: the total includes itself. Excel shows a warning, puts 0 in the cell, and names it under Formulas > Error Checking > Circular References. The fix is to change the range so it stops before the formula's own cell, here =SUM(B2:B6).
B7: =SUM(B2:B7) circular: B7 is inside its own range, Excel shows 0
B7: =SUM(B2:B6) fixed: the range stops above the total
| A | B | |
|---|---|---|
| 1 | Month | Sales |
| 2 | Jan | 120 |
| 3 | Feb | 95 |
| 4 | Mar | 140 |
| 5 | Apr | 110 |
| 6 | May | 130 |
| 7 | Total | 595 |
Click B7: the coloured box covers B2:B6 and stops above the total. This is the most common circular reference of all. It usually appears when a row is inserted just above a total and the SUM range is extended by hand one row too far, or when the range is dragged with the mouse over the total cell.
What Excel does with a circular reference
When you enter the formula, Excel shows a message: "There are one or more circular references where a formula refers to its own cell either directly or indirectly. This might cause them to calculate incorrectly." Click OK and the formula stays, showing 0 (or the last value it had). After that:
- The status bar at the bottom of the window shows Circular References: B7 (the address of one cell in the loop) while that sheet is active.
- Excel does not repeat the message while you keep working, so a circular reference can sit in a workbook unnoticed, with the status bar as the only reminder.
- Other formulas that depend on the cell use the 0, so totals further on are wrong without any error.
The same workbook in Google Sheets shows #REF! with the note "Circular dependency detected".
How to find circular references in Excel
- Read the status bar. It names a cell on the active sheet. If it only says Circular References with no address, the loop is on another sheet.
- Go to Formulas > Error Checking, click the small arrow beside it, and point at Circular References. The submenu lists the cells in loops. Click one to select it.
- With the cell selected, use Formulas > Trace Precedents to draw arrows from the cells it reads. Follow them until one leads back to the start. Remove Arrows clears them.
- On a Mac the commands are in the same place: the Formulas tab, Error Checking, then Circular References.
Fix the cell the list shows, then check the list again: a workbook can have several loops, and Excel lists the next one once the first is gone.
Indirect circular references
A loop through two or more cells is harder to spot, because no formula mentions its own cell.
C2: =B2*10% tax on the net price in B2
B2: =D2-C2 net price = total minus tax
D2: =B2+C2 total = net plus tax
Each formula looks reasonable, but B2 needs C2, C2 needs B2, and D2 needs both. One of the three values has to be an input. Decide which number you actually know, type it in, and calculate the others from it:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Item | Net price | Tax (10%) | Total |
| 2 | Desk | $240.00 | $24.00 | $264.00 |
| 3 | Chair | $85.00 | $8.50 | $93.50 |
The net prices are typed in, and tax and total come from them: the desk costs 24.00 of tax. If the total is what you know instead, the net price is =D2/(1+10%): the formula is solved for the unknown, so nothing refers back to itself.
Percent of a total that includes itself
A share-of-total column becomes circular when the total sums the share column too, or when the total row sits inside the range the shares divide by.
| A | B | C | |
|---|---|---|---|
| 1 | Region | Sales | Share |
| 2 | North | 420 | 42% |
| 3 | South | 310 | 31% |
| 4 | East | 180 | 18% |
| 5 | West | 90 | 9% |
| 6 | Online | 0 | 0% |
| 7 | Total | 1000 | 100% |
Each share divides by B7, and B7 adds only B2:B6. Online sold nothing, so its share is 0%, and C7 adds the shares to 100%. Had B7 been =SUM(B2:B7), or =SUM(B2:C6), every share would depend on itself. The $ in $B$7 keeps the total fixed as the formula is filled down; see percentage.
A running balance that points at its own row
A running total adds each new amount to the balance in the row above. Pointing at the balance in the same row is a loop.
C3: =C3+B3 circular
C3: =C2+B3 previous balance plus this row's amount
| A | B | C | |
|---|---|---|---|
| 1 | Date | Amount | Balance |
| 2 | 2026-03-01 | 500 | 500 |
| 3 | 2026-03-04 | -120 | 380 |
| 4 | 2026-03-09 | -80 | 300 |
| 5 | 2026-03-15 | 250 | 550 |
| 6 | 2026-03-22 | -60 | 490 |
The first balance is just the first amount; every row after adds its amount to the row above. The balance ends at 490. Click C4 and the coloured boxes show C3 and B4, never C4 itself.
Commission on profit after commission
Some circular references are not typos but a calculation that really depends on its own result: a commission of 10% of the profit, where the profit is what is left after paying the commission.
B5 (commission): =B6*B4 10% of profit
B6 (profit): =B2-B3-B5 revenue minus cost minus commission
Turning on iterative calculation (File > Options > Formulas > Enable iterative calculation, or Excel > Preferences > Calculation on a Mac) lets Excel repeat the loop until the numbers settle. It works here, but it also hides every accidental loop in the workbook, and some loops never settle. Solving the equation is better. If the commission is the rate times (revenue minus cost minus commission), then the commission is (revenue minus cost) times the rate, divided by (1 + rate).
| A | B | |
|---|---|---|
| 1 | Item | Value |
| 2 | Revenue | $50,000.00 |
| 3 | Cost | $30,000.00 |
| 4 | Rate | 10% |
| 5 | Commission | |
| 6 | Profit | $20,000.00 |
| 7 | Rate of profit | $2,000.00 |
Your turn: Write the commission in B5 without referring to B5 or B6: it is 10% of the profit after commission, which works out to (revenue minus cost) times the rate, divided by 1 plus the rate.
When B5 is right, B7 (10% of the profit) equals the commission in B5: both show $1,818.18. That equality is the condition the circular version was trying to reach.
Frequently Asked Questions
What is a circular reference in Excel?
A formula that needs its own result to calculate. It can refer to its own cell, like =SUM(B2:B7) in B7, or reach it through other cells, like A1 =B1+1 with B1 =A1*2. Excel cannot finish the calculation, so it warns you and shows 0 or the last value.
How do I find a circular reference in Excel?
Look at the status bar at the bottom of the window: it says Circular References followed by a cell address. Or go to Formulas > Error Checking (the arrow beside it) > Circular References, which lists the cells; click one to jump to it.
Why does Excel say there is a circular reference but I can't find it?
The status bar only shows the circular reference on the active sheet, so switch through the sheets and check Formulas > Error Checking > Circular References on each. The loop can also run through a defined name or a cell on another sheet, so use Trace Precedents from the listed cell to follow it.
Should I turn on iterative calculation to fix a circular reference?
Only when the loop is intended, such as a model that converges on a value. File > Options > Formulas > Enable iterative calculation makes Excel repeat the calculation up to 100 times instead of warning. For an accidental loop it hides the mistake, and the result can be wrong.
