How to Find a Circular Reference in Excel Step by Step

To find a circular reference in Excel for Microsoft 365 on Windows, open Formulas > Error Checking > Circular References, then select a listed cell address. Excel will take you to a formula involved in the loop. You can also check the status bar for a circular-reference notice. A circular reference happens when a formula refers, directly or through other cells, back to itself.

What You’ll Need

The workbook in which Excel reports a circular reference, or a workbook whose results seem unexpectedly blank or incorrect.
Access to the formulas and cells involved. If the workbook is protected, you may need permission to select or edit the relevant cells.
Excel for Microsoft 365 on Windows for the exact ribbon path below. Excel for Mac has similar formula-checking tools, although ribbon placement can vary by build. Excel for the web has fewer auditing options; if you cannot locate a loop there, open the workbook in the desktop app.

Step-by-Step

Look for Excel’s warning. If you have just entered or edited a formula that creates a circular reference, Excel may display a warning explaining that one or more formulas contain circular references. Note the cell you were editing and the formula bar contents. The warning is a clue, but it may not tell you every cell in a longer loop. If you do not see a warning, continue: iterative calculation may be enabled, or the circular reference may already have been in the workbook.
Check the status bar. Look along the bottom edge of the Excel window. When Excel detects a circular reference, the status bar can show a message such as “Circular References” followed by a cell address. Select that cell to inspect it. The status bar may identify only one location, so do not assume that the first address is the only formula involved.
Open the Circular References list. Select the Formulas tab, then choose Error Checking in the Formula Auditing group and point to Circular References. If Excel has identified a loop, the submenu lists a cell address. Select the address to jump to that cell. If the command is unavailable or the submenu is empty, check that the correct workbook is active and move on to the settings and troubleshooting checks below.
Inspect the selected formula. Read the formula in the formula bar and identify every cell or range it uses. A formula is circular if one of its references eventually leads back to the formula’s own cell. For example, with a running total intended for B2 and source values in B2:B10, the formula =SUM(B2:B10) in B2 includes B2 itself. Excel flags that formula as circular; it cannot provide a dependable total from that setup. The likely correction is to keep source values in a separate range and put the total outside that range.
Follow the references in the loop. If the selected formula points to another cell, select that cell and inspect its formula too. Continue until you reach a cell that refers back to the original cell. A loop does not need to be in one formula: for example, A1 can depend on B1, while B1 depends on A1. A longer chain can pass through several sheets. Write down the addresses as you follow them so you can see the full path rather than changing a cell that is merely nearby.
Use formula-auditing arrows when the path is unclear. Select the formula cell, then use Formulas > Trace Precedents to show cells feeding into it. Use Trace Dependents to see cells that rely on it. Follow the arrows one cell at a time, including references on other sheets. Select Remove Arrows in the Formula Auditing group when you are finished. These arrows help explain relationships, but they may not draw a neat, complete circle for a complicated formula or a chain that crosses worksheets.
Check for more than one loop. Return to Formulas > Error Checking > Circular References after inspecting a listed address. If another address is available, select it and investigate that formula as well. Fixing one circular reference can reveal another, so repeat the check until the menu no longer lists a circular reference and the status bar no longer reports one.
Correct the cause, then recalculate. Decide what the formula is meant to calculate. Move a total outside its source range, correct an accidental cell reference, or revise the calculation flow so that inputs feed into outputs without pointing back. Press F9 to recalculate after making the change, then check the result and repeat the Circular References check. Do not delete a formula just to make the warning disappear unless you know what that formula is for.

Common Mistakes

Assuming a circular reference must be in one formula. A direct loop occurs when a formula refers to its own cell. An indirect loop involves two or more formulas. For instance, one cell might calculate from a second cell, while the second calculates from the first. Inspect the whole chain; editing only the first cell you find may leave the loop intact.

Including the result cell in its own input range. This often happens when a total is placed inside the range being totaled. Check the range boundaries carefully, especially after inserting rows or copying a formula. A formula may look reasonable while its range quietly includes the formula cell.

Confusing a circular reference with a different formula error. Errors such as #REF! or #VALUE! have different causes. A circular reference is about a formula dependency returning to itself; it is not simply any error displayed in a cell. Use the Circular References submenu and status bar to confirm before changing formulas.

Turning on iterative calculation as a quick fix. Iterative calculation allows Excel to recalculate circular formulas repeatedly under specified limits. It is useful for deliberate models designed to converge, but it can hide an accidental loop and make results depend on calculation settings. Do not enable it just to remove a warning.

Checking only the visible worksheet. A formula can refer to another sheet, a defined name, or a range that is not currently on screen. Follow the formula bar references and check relevant sheets before deciding the loop is elsewhere or gone.

Tips

Save a copy of the workbook before changing a complex model. That gives you a safe comparison point if a correction affects totals or other dependent cells. If several people edit the file, note the original formula and the cell address before making a change so the intended calculation can be reviewed.

Use a simple dependency sketch for longer loops: write each cell address, then draw an arrow from a formula cell to the cells it reads. When the arrows eventually return to the starting address, you have traced a circular path. This is particularly helpful when formulas span worksheets or when a workbook contains many similar rows.

Check named ranges as well as visible cell references. A formula may use a name instead of an address, and that name can refer to a range that includes the formula cell or leads to another formula. Open Formulas > Name Manager to review defined names and their “Refers to” entries. Make changes only when you understand how the name is used elsewhere in the workbook.

If a workbook intentionally uses circular formulas, document that design and its calculation settings. In Windows desktop Excel, iterative calculation settings are under File > Options > Formulas, in the Calculation options section. Record the maximum iterations and maximum change values if the model depends on them. Those settings affect how Excel calculates the model, so changing them can alter results.

Keyboard shortcuts can help you inspect cells, but there is no single shortcut that identifies and repairs every circular reference. On Windows, Ctrl+` toggles the display of formulas on a worksheet, which can make references easier to compare. On Mac, the equivalent is commonly Control+`, though keyboard layouts can affect the key. Use the ribbon’s Circular References list as the main locator rather than relying on visual scanning alone.

Troubleshooting

The Circular References submenu is empty. Confirm that the workbook containing the formulas is active, and check the status bar. Then inspect File > Options > Formulas for the “Enable iterative calculation” setting. If it is on, Excel may be calculating intentional circular formulas instead of presenting the usual warning. Turning it off can change workbook results, so save a copy first and consult the workbook’s design notes before changing it.

Excel shows a circular reference, but you cannot find the address. Select the address shown in the status bar or submenu, then inspect its formula bar entry. If the cell is on another sheet, Excel may switch sheets when you select it. Check hidden sheets and defined names when the visible formula does not explain the loop. Also consider formulas that refer to a whole column or a range that includes their own row.

The warning remains after you edit a formula. The edited cell may not be the only part of the cycle. Reopen the Circular References submenu and follow each listed address. A second formula may still lead back to the first, or the formula may refer to a named range that includes it. Recalculate with F9, then check again rather than relying only on the displayed value.

The displayed number changes when you alter calculation settings. That is a sign the workbook may be using iterative calculation. Restore the prior settings if you recorded them, or use your saved copy to compare. Do not assume a changed number is a correct fix: determine whether the circular calculation is intentional and what iteration limits the model requires.

You are using Excel for Mac or the web. On Mac, look on the Formulas tab for Error Checking and its Circular References option; the exact arrangement can vary by version and ribbon configuration. In Excel for the web, the desktop auditing commands may be unavailable or limited. Open the workbook in desktop Excel when you need the full circular-reference list and formula-auditing tools.

Frequently Asked Questions

What does circular reference mean in Excel?
A formula has a circular reference when it depends on its own result, either directly or through one or more other formulas. Excel cannot calculate an ordinary one-way result from that dependency without iterative calculation.

Where does Excel show circular references?
On Windows desktop Excel, check the status bar and open Formulas > Error Checking > Circular References. The submenu can take you to a cell involved in a detected loop.

Why is the Circular References list empty?
The active workbook may not contain a currently detected loop, or iterative calculation may be enabled. Confirm the workbook and check its calculation settings before concluding that a warning you saw is unrelated.

Can a circular reference involve multiple worksheets?
Yes. A formula on one sheet can refer to a cell on another sheet, and that second sheet can lead back to the first. Follow the references across sheets until the path returns to its starting cell.

Should I enable iterative calculation to fix the warning?
Only if the workbook is intentionally designed to use a circular calculation. Iteration changes how Excel calculates formulas; it does not remove the loop. For an accidental circular reference, correct the cell or range dependency instead.

How do I remove a circular reference without breaking the workbook?
Identify the intended inputs and output first, then revise the formula flow so the output is not included among its own inputs. Save a copy, make one change at a time, and verify dependent results after recalculating.

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.