Circular References in Excel: How to Find and Trace Them

To view a circular reference in Excel for Microsoft 365 on Windows, open the workbook and select Formulas > Error Checking > Circular References, then choose a listed cell address. You can also look for a circular-reference notice and cell address on the status bar. These routes take you to a formula involved in a loop so you can inspect it; they do not automatically explain or fix the formula.

What You’ll Need

Excel desktop for Windows and the workbook that contains the formulas. The ribbon steps below apply to Microsoft 365; the corresponding Error Checking command is also available in many recent desktop versions.
Permission to edit the workbook if you plan to correct a formula. You can view and investigate the reference without changing anything.
If the workbook is important, save a separate copy before making changes. Formula edits can affect dependent calculations.

A circular reference happens when a formula refers to its own cell, either directly or through other cells. For example, if a formula is entered in B2 and refers to B2, it refers to itself. With a formula in B2, =B2+1 is a direct circular reference. A less obvious loop can involve two or more cells: with formulas in A1 and B1, A1 refers to B1 and B1 refers back to A1. Excel may warn about a circular reference or calculate unexpected results, depending on the workbook’s calculation settings.

Step-by-Step

Open the workbook and note the active sheet. If Excel displays a circular-reference warning when the file opens or when you enter a formula, note it, but do not assume the warning identifies every loop. Choose the sheet where you were working when the issue appeared. If you are unsure, start on the first worksheet and check each sheet in turn.
Open the Circular References list. On the ribbon, select Formulas > Error Checking > Circular References. The Circular References submenu lists a cell address when Excel has detected one. Click an address to select that cell. The menu may show a sheet name as well as a cell reference, which helps distinguish cells with the same address on different sheets.
Check the status bar for a direct route. Look along the bottom of the Excel window for a circular-reference notice and an address. If Excel shows a cell address, click it to select the referenced cell. The status bar can be useful when you want to jump straight to a detected cell, while the ribbon menu is helpful for seeing the available addresses. If you cannot see the status bar, maximize the Excel window and check the bottom edge of the application.
Read the selected cell’s formula. With the cell selected, inspect the formula bar above the worksheet. You can also double-click the cell or press F2 on Windows to examine the formula in place. Look for the cell referring to itself directly, or for references to other cells that might lead back to it. The selected cell is a starting point for tracing the loop, not necessarily the only formula that needs attention.
Trace referenced cells one at a time. For a simple reference, follow each cell address in the formula. Select the referenced cell and inspect its formula bar, then continue until you find a reference that leads back to the starting cell. For example, if the selected formula points to C5, inspect C5; if C5 points to D5 and D5 points back to the original cell, those formulas form a circular chain. Note each address as you go so it is easier to keep track of a longer loop.
Use Trace Precedents or Trace Dependents for a visual clue. Select the cell, then choose Formulas > Formula Auditing > Trace Precedents to show arrows toward cells used by its formula. Trace Dependents shows cells whose formulas use the selected cell. These arrows can make a short loop easier to see, but they may not show every link clearly when formulas use other worksheets, named ranges, or complicated references. Use the formula bar to verify the actual references.
Check other worksheets if the loop crosses sheets. A formula can refer to a cell on another sheet, and that cell can eventually refer back. Follow sheet-qualified references in the formula bar and inspect each destination. The status bar generally identifies a detected circular reference in the active context; do not treat the address shown there as proof that other sheets are clear. Return to the Circular References menu after investigating and check for other listed addresses.
Decide whether the loop is accidental or intentional. Many circular references are unintended, such as a total formula accidentally including its own cell. Others are deliberately used for iterative calculations in specialized models. Before editing, identify the intended calculation and confirm whether the workbook is designed to use iteration. Do not enable iterative calculation simply to make a warning disappear.
Correct an unintended loop and verify the result. Edit the formula or its range so the calculation no longer returns to the starting cell. For instance, if a total in B10 accidentally sums a range that includes B10, revise the range to include only the intended input cells. Press Enter, check the displayed value, and reopen Formulas > Error Checking > Circular References. When the loop is removed, its address should no longer appear in the list. Check related totals and outputs as well.

Excel usually identifies a cell involved in a loop, but finding the cause can take a few steps. A circular reference may be direct, such as a cell that names itself, or indirect, where several formulas form a chain. If more than one loop exists, fixing one may reveal another. Repeat the check after each correction instead of assuming one selected address accounts for every problem.

Common Mistakes

Confusing a referenced cell with the whole cause. The address in the menu is a place to start. Trace its formula and the formulas it uses; the error may be in another cell in the chain.
Checking only the visible worksheet. Loops can cross between sheets, and a formula may refer to a distant tab. Review the formulas and sheet names along the chain, not just nearby cells.
Assuming an empty submenu proves the workbook is clean. The list reflects what Excel currently detects in the workbook and calculation context. Recalculate, inspect relevant sheets, and confirm that calculation is not being handled through iterative settings.
Deleting a formula just to clear the warning. Removing a formula can break a model or leave a result blank. First establish what the cell is intended to calculate, then adjust the reference or formula structure.
Turning on iterative calculation without checking the model. Iteration can be appropriate for a deliberately circular financial or engineering model, but it changes how Excel calculates. It is not a general-purpose fix for an accidental reference.

Tips

Keep a short written trail of cell addresses while following a long chain. A list such as A1, C5, D5, A1 makes the return path obvious.
Use the Name Box to jump to a known address: it is to the left of the formula bar. Type the address and press Enter. For a cell on another sheet, use the sheet tab and then enter the address, or select the cross-sheet reference from a formula.
After changing a formula, check both the circular-reference list and the result cells that depend on it. A formula can stop looping and still produce an incorrect total.
If you are investigating a workbook you did not create, avoid changing calculation options until you have recorded their current state. This makes it easier to preserve the workbook’s intended behavior.
On Windows, F2 edits the active cell and Esc cancels an in-progress edit. On Mac, use Control+U to edit the active cell; the circular-reference ribbon route is broadly similar, though ribbon layout and shortcuts can vary by Excel version.

Troubleshooting

The Circular References command is missing or has no addresses. First, make sure the workbook is open in the Excel desktop app and that you are checking the workbook where the warning appeared. Recalculate with Formulas > Calculate Now, then revisit Formulas > Error Checking > Circular References. Check the status bar and inspect formulas on the sheets involved. If Excel is set to use iterative calculation, it may calculate an intentional loop instead of presenting it as an ordinary blocking error; review the setting before changing it.

The address takes you to a cell, but the formula looks normal. Look at every referenced cell rather than only the formula itself. A formula such as a sum or lookup can depend on a cell that eventually leads back to the original. Follow cross-sheet references and named ranges too. If a formula uses a defined name, open Formulas > Name Manager to inspect what cell or range the name refers to.

You fixed one loop, but Excel still reports a circular reference. Open the submenu again and select the current address. Excel may now be pointing to another loop or another cell in the remaining chain. Repeat the trace-and-correct process. Recalculate after changes, and check all listed addresses before concluding the workbook is clear.

You are using Excel for the web. The web app may show a circular-reference warning, but its formula-auditing and navigation options can be more limited than the desktop app. For the full ribbon workflow and easier tracing, open the workbook in the Excel desktop app using the available Open in Desktop App command. On Mac desktop Excel, look for the equivalent Circular References entry under the Formulas tab; exact ribbon placement can differ by release.

The warning returns after reopening the file. Check whether the circular formula remains in the workbook and whether the workbook is designed to use iterative calculation. If the file is shared, another person or an automated process may have restored the formula. Save a separate copy, inspect the relevant cells, and confirm the calculation settings before making a lasting correction.

Frequently Asked Questions

Where does Excel show a circular reference?
Excel can list an address under Formulas > Error Checking > Circular References. It may also show a circular-reference notice and cell address on the status bar.

Can I find a circular reference without changing the workbook?
Yes. Open the list, select the reported cell, and inspect its formula and referenced cells. Viewing formulas and tracing a loop does not require editing them.

Why does Excel show only one circular-reference address?
The address is a useful starting point, not necessarily a complete inventory of every formula involved. A loop can contain several cells, and other loops may become apparent after you resolve the one Excel currently reports.

Does a circular reference always mean the workbook is wrong?
No. It can be intentional in a model that uses iterative calculations. However, an unintended loop can produce a warning or unexpected results, so confirm the workbook’s design before deciding to keep it.

Should I turn on iterative calculation to remove the warning?
Not unless the workbook is specifically designed to calculate iteratively. Enabling it changes calculation behavior and can mask an accidental loop. Find and understand the formulas first.

Can Excel for the web trace the same references?
Some viewing and calculation behavior is available in the web app, but desktop Excel provides the more complete formula-auditing workflow described here. Open the workbook in the desktop app when you need to follow references in detail.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

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

Spotted an error in this guide? Let us know.