To spot a circular reference in Excel for Microsoft 365 on Windows, open the Formulas tab, select Error Checking, then point to Circular References. Choose a listed cell to jump to it. You can also check the status bar for a circular-reference message. These checks identify formulas that depend on their own result, either directly or through other cells; tracing the dependency chain helps you find what to correct.
What You’ll Need
- The Excel workbook where the warning appeared, or the workbook that is not calculating as expected.
- Access to the desktop version of Excel if you need to use formula-auditing tools. Excel for the web has fewer auditing features.
- A saved copy of the workbook before making substantial changes, especially if it contains complex formulas or shared calculations.
Step-by-Step
- Save a copy and note the symptoms. Use File > Save As to make a working copy if you are going to edit formulas. Note whether Excel showed a circular-reference warning when the workbook opened, whether a result stopped updating, or whether the status bar shows a circular-reference message. A warning can appear when a formula is first entered, but an existing workbook may have a loop that is easy to miss if you dismissed the dialog earlier.
- Check the status bar. Look along the bottom of the Excel window, usually at the left side, for text identifying a circular reference and a cell address. If a cell is named, write down the sheet and address. If the status bar is not showing a useful message, continue with Error Checking; the status bar is a clue, not a complete inventory of every problem in the workbook.
- Open the Circular References list. On the ribbon, select Formulas > Error Checking, then point to or open Circular References. The submenu lists cell addresses Excel has identified. Select an address to move to that cell. If the command is unavailable or the list appears empty, check whether iterative calculation is enabled in Step 8 and try the other checks below.
- Record the sheet and cell before editing. A cell address such as B12 is not enough if several sheets have cells with that address. Note the sheet name shown in the workbook and read the formula in the formula bar. If the listed cell is on another sheet, use the sheet tab or the address selected from the menu to inspect its formula. Keep a short list of each flagged cell and its formula as you investigate.
- Look for a direct self-reference. A direct circular reference occurs when a formula refers to its own cell. For example, with a running total intended for cell A2, entering
=A2+5in A2 makes the result depend on itself. The formula cannot produce an ordinary answer because the value of A2 is needed to calculate A2. Check whether the intended input or previous total was actually supposed to come from a different cell. - Follow indirect references. A loop can pass through two or more cells. For example, with A2 and B2 intended as separate calculation cells, putting
=B2+1in A2 and=A2+1in B2 makes each cell depend on the other. Start at the flagged formula and follow every referenced cell, including cells on other worksheets, until you reach the starting cell again. That path is the circular chain. - Use formula auditing to follow the chain. Select the flagged cell, then choose Formulas > Trace Precedents to show cells that feed into it. Select a precedent and trace its precedents in turn. Use Formulas > Trace Dependents to see which cells rely on the selected cell. These arrows help reveal the route through the sheet, but they may not show every dependency clearly when formulas use names, external workbooks, or functions that build references dynamically.
- Inspect names, tables, and other sheets. If the formula looks harmless, check whether a referenced name points back to the formula cell or to another cell in the loop. Open Formulas > Name Manager to review defined names and their references. Check formulas in related sheets and table columns too: a structured table formula can refer to a column that ultimately depends on the same calculated column. Also look for references to other open workbooks.
- Check the iterative calculation setting. On Windows, go to File > Options > Formulas and look under Calculation options for Enable iterative calculation. If it is on, Excel may allow a circular formula to calculate repeatedly rather than show the usual warning. Do not turn it on merely to hide a warning. First determine whether the loop is intentional and whether the workbook was designed to use iterative calculations.
- Correct the formula, then check again. Decide what the formula should depend on, and replace the accidental self-reference or break the unintended chain. For example, a total usually needs to refer to input cells or a prior-period value, not to the cell containing the total itself. After editing, press Enter, revisit Formulas > Error Checking > Circular References, and inspect the status bar. Recalculate with Formulas > Calculate Now if needed, then confirm that the displayed results make sense.
On Excel for Mac, the ribbon generally offers the same Formulas and Error Checking tools, though the exact placement can vary by release. Calculation preferences are under Excel > Settings or Excel > Preferences, then Calculation. If a command is missing in Excel for the web, open the workbook in the desktop app to use its formula-auditing features.
Common Mistakes
Assuming the first listed cell is the only problem. The Circular References menu can show more than one address, and fixing one loop can expose another. Check the menu again after each correction rather than stopping at the first cell.
Changing a formula before understanding its purpose. A circular formula may be deliberate in a model that uses iterative calculation. Replacing it without checking the workbook’s design can change results elsewhere. Review nearby labels, instructions, and dependent formulas before editing.
Confusing a circular reference with an ordinary error. Errors such as #VALUE! or #NAME? do not by themselves prove there is a circular reference. Use Excel’s Circular References list and follow the cell links. Conversely, a formula that displays a number is not necessarily safe: iterative calculation can permit an intentional or accidental loop.
Looking only at the visible worksheet. The loop may pass through a hidden sheet, a defined name, a table formula, or another workbook. Inspect the references behind the formula, not just nearby cells. Avoid deleting a sheet or name simply because it appears in the chain; first find out what uses it.
Expecting Trace Precedents to explain every formula automatically. Arrows are useful for ordinary cell references, but they can be less informative when a formula uses a defined name, an external link, or a dynamically constructed reference. Read the formula itself and inspect the referenced name or source workbook where necessary.
Tips
Work from the cell Excel identifies, then map the dependency path one link at a time. A simple note such as A2 points to B2, B2 points to D5, and D5 points back to A2 makes an indirect loop much easier to discuss and repair. If cells are on different sheets, include sheet names in the note so similar addresses do not get mixed up.
When you find the loop, identify the intended direction of calculation. For instance, decide which cells are user inputs, which are intermediate calculations, and which display final results. A formula should normally flow from inputs toward outputs. If an output is being fed back into an input by mistake, moving or correcting that reference may resolve the loop without changing the intended calculation.
Use Formulas > Show Formulas when you need to scan a worksheet’s formulas rather than results. Select it again to return to the normal display. You can also use Formulas > Remove Arrows after tracing so auditing arrows do not clutter the sheet. These tools do not fix a loop themselves, but they make it easier to compare formulas and confirm that the corrected references follow the intended pattern.
Be cautious with iterative calculation. It is useful only when a workbook is intentionally designed to repeat calculations until a result stabilizes, and its iteration limits affect the outcome. If you inherit a workbook with the setting enabled, document that fact and ask the workbook owner what the circular calculation is meant to do before changing either the setting or the formula.
Troubleshooting
The Circular References menu is empty, but you still suspect a loop. Confirm that you are in the workbook with the issue and that you are checking the correct open file. Look at the status bar, then inspect the formula you suspect and follow its references manually. If iterative calculation is enabled, the usual warning behavior may differ. You can review the setting, but do not disable it in a production workbook until you know whether the model relies on it.
Excel identifies a cell, but the formula appears normal. Follow references beyond the first cell, including across worksheets. Check Name Manager for names used in the formula, and inspect any cells that those names refer to. If the formula uses an external workbook, make sure the source is available and examine the linked cell there. A self-reference can be several steps away from the address Excel initially lists.
The warning returns after you fix a formula. Reopen the Circular References submenu and check the new address. There may be another loop, or the edit may have redirected the chain rather than broken it. Undo immediately with Ctrl+Z on Windows if the change was unintended; on Mac use Command+Z. Then compare the original and edited formulas and repair one dependency at a time.
Formula arrows do not appear or are difficult to follow. Select the formula cell first and use the commands on the Formulas tab. Remove existing arrows and trace again if the sheet is crowded. For cross-sheet or indirect references, inspect the formula bar and navigate to each referenced cell manually. If the workbook is open in a browser and auditing controls are missing, use Open in Desktop App.
The value changes unexpectedly after a repair. Check whether calculation is set to Automatic under Formulas > Calculation Options, then recalculate. Compare the changed result with the intended business logic and dependent cells; breaking a loop can legitimately change values that previously relied on iterative calculation. If the workbook is important, ask its owner to confirm the intended result before distributing the edited copy.
Frequently Asked Questions
What is a circular reference in Excel?
A circular reference occurs when a formula depends on its own result. The dependency can be direct, such as a formula referring to its own cell, or indirect, where a chain of formulas eventually leads back to the starting cell.
Where does Excel show circular references?
On Windows desktop Excel, check the status bar and select Formulas > Error Checking > Circular References. The menu can list cells Excel has identified; select an entry to navigate to that cell.
Why does Excel not show a circular-reference warning?
The warning may have been dismissed, or iterative calculation may be enabled. Check the Error Checking menu and the calculation setting under File > Options > Formulas. A formula can also be difficult to spot if its dependency runs through other sheets or defined names.
Can a circular reference be intentional?
Yes. Some models deliberately repeat a calculation and use iterative calculation to approach a result. That setup should be documented and configured deliberately. Do not enable iteration simply to suppress an error, because it can make an accidental loop harder to notice.
How do I find an indirect circular reference?
Start at the cell Excel lists, inspect its formula, and follow each referenced cell until the chain either ends or returns to the starting cell. Use Trace Precedents and review names, other sheets, tables, and linked workbooks when the chain is not obvious.
Does the same process work in Excel for the web?
The underlying formulas work the same way, but the web app may not provide all the desktop formula-auditing commands. If you cannot find the Circular References or tracing controls, open the file in desktop Excel and follow the ribbon steps there.
Related
- How to Add the Same Header in Excel on Every Sheet
- Add a Header in Excel for the Web
- Add a Header in Excel for Microsoft 365: Page Setup
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Data Validation: Add Criteria and RulesTo add criteria in Excel Data Validation, select the cells, go to Data > Data Validation > Data…
- Google Sheets Checkbox X Symbol: Show an X InsteadGoogle Sheets’ built-in checkbox cannot be changed to draw an X instead of its usual check mark. To…
- Google Sheets Iframe: How to Embed a SpreadsheetTo put a Google Sheets spreadsheet in an iframe, open it in a browser and choose File >…
- How to Add Mail Merge Fields in Word: Step-by-StepTo add mail merge fields in Word, connect a recipient list, place the cursor where personalized information should…
- Does Excel Goal Seek Automatically Update?No. Excel Goal Seek finds an input value that makes a formula reach a target, then leaves that…
- Excel Trendlines: How to Add, Format, and Read ThemTo add a trendline in Excel for Microsoft 365 on Windows, select a chart, choose the plus-shaped Chart…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.