Find Circular References in Excel

To find a circular reference in Excel for Microsoft 365 on Windows, open Formulas > Error Checking > Circular References, then select a listed cell. You can also check the status bar for a circular-reference warning and use formula-auditing arrows to follow the cells involved.

What a circular reference means

A circular reference occurs when a formula depends on its own result, either directly or through other cells. For example, a formula in cell D10 might add the values in D2 through D10; because the total includes D10 itself, Excel cannot calculate it as a straightforward sequence. A less obvious loop can run across several cells: one formula refers to a second cell, that cell refers to a third, and the third eventually refers back to the first.

Most spreadsheet formulas flow from inputs to results. A circular reference breaks that one-way flow, so Excel normally warns that it cannot calculate the formula. The warning does not necessarily mean the workbook is damaged. It means Excel has found a dependency loop, which may be accidental or deliberately used for a calculation that repeats until it settles.

Use Excel’s Circular References command

Start with Excel’s built-in error-checking list. It is usually the quickest way to jump to a cell that belongs to a loop:

Open the workbook in the Excel desktop app and select the Formulas tab.
In the Formula Auditing group, select the arrow beside Error Checking.
Point to Circular References in the menu.
Select a cell address in the submenu to go to that cell.

When Excel detects a circular reference, it may also display the affected cell address in the status bar at the bottom of the window. If the menu lists more than one address, check each one. A listed cell is a useful starting point, but the formula may be part of a longer chain: the cell you need to change could be elsewhere in that chain.

If the Circular References menu is unavailable or empty, check whether iterative calculation is enabled. With iteration allowed, Excel can calculate some circular formulas instead of showing the usual blocking warning. You can review the setting in File > Options > Formulas on Windows. Do not switch it off automatically in a workbook that may rely on intentional iteration; first find out how the model is meant to work.

Check the whole workbook, not just one sheet

A circular reference can cross worksheet boundaries. A formula on a summary sheet might refer to a department sheet, whose formula in turn links back to the summary. Follow the Circular References menu entries and check the sheet name as well as the cell address. If you are unsure whether you have accounted for every affected sheet, select each worksheet tab and inspect the error-checking menu again.

For a broader inventory, use Excel’s Find feature to look through formulas. On Windows, press Ctrl+F; on Mac, press Command+F. Select Options, set Within to Workbook and Look in to Formulas, then search for an equals sign. Use Find All to review matches. This can help locate formula cells, but it does not identify which formulas form a circular loop, so treat it as a way to investigate rather than a circular-reference detector.

Hidden sheets, named ranges and formulas that refer to other sheets can make a loop less obvious. If the listed cell appears harmless, inspect its formula and any referenced cells, including cells on other worksheets. Also check names used in formulas through Formulas > Name Manager; a defined name can hide the location of a reference.

Trace the formula chain

Excel’s auditing tools help you see which cells feed a formula and which cells depend on it. Select the cell Excel identified, then use Formulas > Trace Precedents to show cells used by that formula. Use Trace Dependents to show cells that use its result. Follow the arrows through the relevant cells and worksheets until you find the path that returns to the starting point.

These arrows are most useful for short, visible chains. If a connection crosses worksheets, Excel can show a worksheet indicator rather than a simple arrow to the other cell. Double-clicking a dashed auditing arrow opens a Go To list of referenced cells, where you can select a destination. Remove arrows with Formulas > Remove Arrows when you have finished; the arrows are visual aids, not edits to the workbook.

When the chain is long, inspect each formula rather than assuming the first flagged cell is the cause. A loop might be created by a total, a lookup result, or a formula that feeds a separate calculation. Check whether the references are intentional and whether a range accidentally includes the formula cell itself.

Fix an accidental circular reference

The right fix depends on the calculation the workbook is supposed to perform. Do not delete a formula just to clear the warning; doing so may remove a result that other cells need. First identify the intended inputs and outputs, then break the loop at the point where a formula refers back to a result that should not be part of its own input.

Correct an oversized range. A total placed at the bottom of a column should generally sum the data above it, not include its own cell.
Separate inputs from results. If a formula uses a value that is also calculated from that formula, move the original assumption or input to a dedicated cell.
Review copied formulas. A relative reference may have shifted when a formula was filled down or across. Compare it with neighboring formulas and correct the reference if needed.
Check cross-sheet links. Decide which sheet should provide the source value and which should display the result; remove the unintended return link.

After changing a formula, revisit Formulas > Error Checking > Circular References and check the status bar. Recalculate the workbook and verify important totals against their source data. Clearing the warning confirms that Excel no longer sees that loop, but it does not prove the revised calculation is logically correct.

When a circular reference is intentional

Some financial and engineering models use iteration: a calculation feeds back into itself, and Excel repeats calculations until the results change by less than a chosen amount or until it reaches a maximum number of iterations. For instance, a model may calculate interest based partly on a balance that itself includes interest. In that situation, the loop may be part of the design rather than an error.

On Windows, iterative calculation settings are in File > Options > Formulas. You can enable Enable iterative calculation and set Maximum Iterations and Maximum Change. The iteration limit controls how many calculation cycles Excel can perform; the change limit controls how small the difference between successive results must become before calculation can stop. A smaller maximum change can require more work, and a low iteration limit may leave a result short of the intended tolerance.

Before changing these settings, ask the workbook’s owner or inspect its documentation. Iteration can affect more than one formula in the workbook, and different settings can produce different results. If you enable it, verify that the model converges to sensible values and that its assumptions and tolerance are appropriate. For a workbook that should calculate without iteration, correct the loop instead of using the setting to hide it.

Common reasons a reference is hard to spot

A circular reference is not always a formula that visibly names its own cell. It can pass through multiple formulas, worksheets or defined names. A workbook may also contain several separate loops, so resolving one does not guarantee that all have been found. Return to the error-checking menu after each repair and continue until Excel no longer lists a circular reference.

Another common misconception is that every circular reference is an error that must be removed. Excel’s warning identifies a dependency cycle; it does not know whether the cycle is intentional. Conversely, enabling iterative calculation is not a general-purpose repair. It can make a deliberately iterative model work, but it can also conceal an accidental loop and allow unintended values to calculate.

If a warning appears after opening a workbook, check whether calculation settings or formulas were changed, and confirm that you are looking at the current saved version. If the workbook has external links or complex defined names, investigate those dependencies before replacing formulas. Keep an untouched copy before making substantial changes, especially in a shared or business-critical model.

Excel for Mac and Excel for the web

In Excel for Mac, the Formulas tab provides error-checking and formula-auditing tools similar to the Windows desktop app, though the ribbon layout and settings labels can vary by release. Use Command+F rather than Ctrl+F to open Find. If you need to change calculation settings, look in Excel’s calculation preferences or settings; the exact wording can depend on the Mac version.

Excel for the web has a different interface and may not expose the same desktop auditing and calculation-setting controls. If you cannot find the Circular References command or need to review iterative-calculation settings, open the workbook in desktop Excel. When working in a shared workbook, coordinate repairs with other editors so that a change to a formula does not overwrite someone else’s work.

Frequently Asked Questions

Where does Excel show a circular reference?
Excel may show an affected cell in the status bar. On Windows desktop, you can also open Formulas > Error Checking > Circular References and select a listed cell address.

Why can’t I see the Circular References option?
There may be no detected loop, or iterative calculation may be enabled and allowing a circular formula to calculate. Check the workbook’s calculation settings and use desktop Excel if you are working in the web app.

Can a circular reference involve two worksheets?
Yes. A formula on one sheet can depend on a cell on another sheet, whose formula eventually refers back to the first. Use the error-checking list and Trace Precedents or Trace Dependents to follow the chain.

Should I enable iterative calculation to fix the warning?
Only if the circular calculation is intentional and the workbook is designed to use iteration. For an accidental loop, correct the formula or dependency instead; iteration can make an unintended calculation appear to work.

Does finding one circular reference mean I found them all?
Not necessarily. A workbook can contain multiple loops. After repairing a reference, check the Circular References menu again and continue investigating any remaining entries.

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.