A circular reference is a formula that refers, directly or indirectly, to its own cell, and you can find it in Excel by going to Formulas > Error Checking > Circular References or by reading the status bar. Excel then typically shows 0 or the last calculated value in the cell, so trace the loop with Trace Precedents and either fix the formula or turn on iterative calculation if the loop is deliberate. Practice with the free Excel practice test on PracticeTestGeeks (500+ questions with answer explanations).
You opened a workbook, hit Enter, and Excel popped up a yellow warning about a circular reference. Maybe a cell shows 0 when it shouldn't. Maybe the status bar at the bottom of the screen shows "Circular References" and you're not sure what to do next. Don't panic. Circular references are one of the most common Excel hiccups, and once you know where to look, they are straightforward to find.
This guide walks you through every way to track down a circular reference in Excel, fix the accidental ones, and even use the intentional kind safely with iterative calculation. We'll cover the menu path, the status bar trick, Trace Precedents arrows, multi-sheet workbooks, VBA detection, and how circular references stack up against errors like #REF! or #NAME?.
The steps below follow Microsoft's documented menu paths for desktop Excel. Excel for the Web has more limited circular reference troubleshooting tools than the Windows and Mac apps.
Facts verified Oct 17, 2026 against Microsoft (Microsoft Office Specialist program), delivered by Certiport, a Pearson VUE business. Dates that have passed are removed automatically.
Facts verified Oct 17, 2026 against Microsoft (Microsoft Office Specialist program), delivered by Certiport, a Pearson VUE business. Dates that have passed are removed automatically.
| Item | Detail | Official source |
|---|---|---|
| Number of questions (Excel Associate (MO-210)) | 33-37 questions | certiport.pearsonvue.com |
| Number of questions (Excel Expert (MO-211)) | 23-27 questions | certiport.pearsonvue.com |
| Time limit | 50 minutes (60 minutes seat time) | Microsoft (Microsoft Office Specialist program), delivered by Certiport, a Pearson VUE business |
| Passing score | 700 on a 1–1000 scale | certiport.pearsonvue.com |
| Retake policy | Wait 24h after 1st fail, 48h before each later retake; no annual limit | Microsoft (Microsoft Office Specialist program), delivered by Certiport, a Pearson VUE business |
| Validity | Valid 5 years (earned on/after Sept 23, 2025) | certiport.pearsonvue.com |
| Delivery | Proctored live-in-the-application exam via Certiport Authorized Testing Centers (in-center or remote-proctored) | Microsoft (Microsoft Office Specialist program), delivered by Certiport, a Pearson VUE business |
| Eligibility | No formal prerequisites; about 150 hours of instruction and hands-on experience recommended | Microsoft (Microsoft Office Specialist program), delivered by Certiport, a Pearson VUE business |
| Score release | Score report at end of exam; also in Certiport profile | certiport.pearsonvue.com |
Facts verified Oct 17, 2026 against Microsoft (Microsoft Office Specialist program), delivered by Certiport, a Pearson VUE business. Dates that have passed are removed automatically.
Interactive Excel resources on PracticeTestGeeks, free to start:
A circular reference happens when a formula refers, directly or indirectly, to the cell it lives in. Type =A1+1 into cell A1 and you've made one. Set A1=B1, B1=C1, and C1=A1 and you've made an indirect loop across three cells. Excel can't finish the math because each calculation depends on a value it hasn't worked out yet.
Most circular references are accidents. You typed =SUM(A1:A11) into cell A11 instead of A12, or you copy-pasted a formula one row too far. Excel handles these by warning you the first time the workbook opens and by typically showing 0 or the last calculated value in the offending cell. The warning is the easy bit. Tracking down which cell triggered it, especially in a 30-tab workbook, is where people get stuck.
The reason Excel pops the warning is simple: spreadsheets evaluate formulas in dependency order. To calculate cell A1, Excel needs the values of every cell A1 depends on. If one of those cells is A1 itself, there's no order Excel can pick that produces a stable answer. So it typically shows 0 (or the last calculated value), flags the issue, and lets you decide whether to fix it or accept the loop with iterative calculation.
The simplest type. Cell A1 contains a formula that mentions A1. Example: =A1+1 or =SUM(A1:A10) typed into A10. Excel spots these instantly and pops the warning dialog as soon as you press Enter. The fix is usually to delete the cell from the range or move the formula one row down.
The loop runs through one or more cells before coming back. A1=B1+1, B1=C1*2, C1=A1/3. Each formula looks fine on its own, which is why these hide longer. The status bar still flags them, but Trace Precedents is your best friend here because the visual arrows show the chain rather than a single bad cell.
The chain crosses worksheets. A summary cell on Sheet1 references a total on Sheet2, which pulls a value from Sheet1. The status bar only shows circular references on the active sheet, so you have to click each tab to see the full picture. Cross-sheet loops are the most common cause of "silent" zeros in dashboards.
Some financial and engineering models actually need a feedback loop, like calculating interest on a balance that includes the interest itself. For these, you turn on iterative calculation so Excel loops a fixed number of times and accepts the answer once it stops changing. We'll cover the settings and convergence pitfalls below.
When you create a circular reference, Excel shows a warning dialog explaining that a formula refers to its own cell, either directly or indirectly, and may calculate incorrectly. The exact wording and buttons can vary by Excel version. Whichever way you dismiss it, investigate before relying on the result.
The fastest way to find a circular reference in a single-sheet workbook is the menu. Go to the Formulas tab on the ribbon, click Error Checking, then point to Circular References, and Excel lists a cell address. Click any address and Excel jumps straight to that cell.
Once you land on a flagged cell, you'll need to follow the formula to see which cell it depends on, then keep tracing until you find where the chain bites its own tail. That's where Trace Precedents comes in, and we'll get to it shortly.
These reference facts come from Microsoft Support's guidance on removing or allowing a circular reference.
| Item | Detail |
|---|---|
| Find circular references | Formulas > Error Checking > Circular References |
| Status bar text | "Circular References", with an address only in some cases |
| Value shown in the cell | 0 or the last calculated value |
| Iterative calculation (Windows) | File > Options > Formulas > Enable iterative calculation |
| Iterative calculation (Mac) | Excel > Preferences > Calculation > Use iterative calculation |
| Default Maximum Iterations | 100 |
| Default Maximum Change | 0.001 |
| Tracing tools | Formulas > Trace Precedents / Trace Dependents |
The status bar is the unsung hero here. Look at the very bottom-left of your Excel window. Excel displays Circular References and, when the loop is on the active sheet, a cell address. If the loop is on another worksheet, you might see only the words with no address.
Once you're parked on the suspect cell, the next step is mapping the loop. Go back to the Formulas tab and hit Trace Precedents. Microsoft recommends Trace Precedents and Trace Dependents for following relationships across multiple cells.
Trace Dependents does the opposite. It draws arrows to every cell that depends on your active cell. To remove the arrows when you're done, click Remove Arrows in the same Formula Auditing group.
Excel warns you when a formula creates a circular reference.
Look bottom-left for "Circular References". Click the cell address, if shown, to jump there.
Open Error Checking and point to Circular References to see a cell address.
Use Trace Precedents and Trace Dependents to follow the chain back to where it closes.
Either rewrite the formula to break the loop or enable iterative calculation if the loop is intentional.
Multi-sheet workbooks make life harder. The status bar only displays circular references on the sheet you're viewing. So if your loop crosses tabs, you might see only the words "Circular References" with no address even though Sheet2 is the source of the problem.
Walk through every sheet in the workbook one tab at a time and watch the status bar update. As soon as it lights up with a cell reference, you've found the sheet that hosts at least one end of the loop.
The SUM trap is the single most common cause we see in real spreadsheets. You build a column of numbers in A1:A10 and want a total in A11. You type =SUM(A1:A10) and it works fine. Later, you insert a row, drag the formula, or extend the range, and suddenly the SUM reads =SUM(A1:A11), including its own cell. Result: a circular reference and a stubborn 0 in your total row.
Fixing it is simple. Click the SUM cell, look at the highlighted blue range, and shrink it back to exclude the formula's own cell. If you frequently insert rows above totals, consider switching to an Excel Table (Insert > Table). Tables auto-extend their formulas without dragging the SUM range over its own row, and they make most accidental circular references impossible to create in the first place.
Sometimes you actually want a circular reference. Classic examples include calculating compound interest where the interest is part of the balance, depreciation models where book value feeds tax calculations that adjust book value, and engineering systems with feedback loops. For these, Excel offers iterative calculation, a setting that tells the calculation engine to loop a fixed number of times instead of giving up.
Turn it on under File > Options > Formulas (or Excel > Preferences > Calculation on Mac). Tick Enable iterative calculation (labelled Use iterative calculation on Mac). You'll see two settings: Maximum Iterations (default 100) and Maximum Change (default 0.001). Higher iteration counts take Excel more time to recalculate.
In desktop Excel versions, changing these options affects all open workbooks, so accidental circular references elsewhere may stop being flagged. That's risky. If you enable iteration, document why, and consider isolating the iterative model in a dedicated workbook so accidental loops elsewhere stay loud and visible.
Convergence is the other thing to watch. A well-designed iterative model converges, meaning each pass produces a smaller and smaller change until the answer stabilises. A divergent model doesn't, so Excel stops at the iteration limit. If your output looks suspiciously round or shifts every time you press F9, your model probably isn't converging and the answer can't be trusted. Restructuring the formula so each pass narrows the gap usually helps.
For most modelling problems, there's a cleaner workaround than circular references. Use Goal Seek to solve a single-cell target. Use Solver for multi-variable optimisation. Or rewrite the formula algebraically to avoid the loop entirely. A balance-with-interest formula like balance = principal + balance * rate rearranges to balance = principal / (1 - rate), no iteration needed. The closed-form version is both faster and easier to audit later.
Excel for the Web has more limited circular reference troubleshooting than the Windows and Mac desktop apps, according to Microsoft. For a heavy multi-sheet workbook, open the file in desktop Excel, where Trace Precedents and the Formulas > Error Checking > Circular References menu are available.
If you maintain workbooks for a team, the Worksheet.CircularReference property returns a Range for the first circular reference on a sheet, or Nothing if there is none, so a short VBA loop over ThisWorkbook.Worksheets can audit every sheet.
It helps to know how a circular reference differs from Excel's other classic formula errors. They look similar in casual use but they're caused by very different problems. A quick comparison saves you from chasing the wrong fix. For a deeper walkthrough of formula syntax and the full list of Excel errors, the Excel formulas cheat sheet covers the essentials in one place, and the Excel formula basics guide is a good refresher if you're newer to the application.
Notice that a circular reference doesn't show an error code in the cell itself. That's what makes it sneakier than #REF! or #NAME?. Those errors scream at you. A circular reference typically shows 0 or the last calculated value and lets you publish a bad number. The status bar is the only visible clue inside the workbook, and it's easy to miss, especially when the loop is on another sheet.
Spreadsheets also throw newer errors like the Excel #SPILL! error, which appears when a dynamic array formula can't expand into the cells it needs. The #SPILL! error guide explains how to clear blocking cells. While we're talking about formula auditing, if you build charts from your spreadsheets, you might also want to add error bars in Excel to visualise uncertainty in your data, since uncertainty visualisation often goes hand in hand with the kinds of models that flirt with circular references.
One last tip: when you finish editing a workbook with intentional circular references, leave a clear note for whoever opens it next. Add a cell at the top of the model with a yellow fill that reads something like "Iterative calculation enabled. Do not disable." Future-you, or the colleague who inherits the file, will thank you when the totals stop matching after a casual settings tweak. Documentation cells like this cost nothing and prevent hours of debugging.
That's the toolkit. The Formulas menu gives you the list. The status bar gives you the location. Trace Precedents gives you the path. Iterative calculation handles the loops you actually want. With those four moves, you can find and fix every circular reference Excel will ever throw at you, in seconds rather than hours. The next time a workbook lands on your desk with a yellow warning, you'll know exactly where to click and what to look for.