How to Find Circular References in Microsoft Excel

To explain Excel circular references in more depth, let’s imagine a scenario. An example Excel spreadsheet has three cells with values—A2, A3, and A4.

Each of these cells has a value that’s created using a simple sum calculation. A2 adds together the values from A3 and A4, while A3 is the sum of A2 and A4, and A4 is the sum of A2 and A3. Unfortunately, these three calculations can’t co-exist without causing a circular reference.

A2 can’t find the sum of A3 and A4, because both A3 and A4 involve a calculation that includes A2.

Excel will alert you if a circular reference is causing a problem in your workbook. If you can’t spot the error yourself, you can use the “Error Checking” menu to find all of the circular references in your workbook.

To find any existing circular references, open your workbook and select the “Formulas” tab on the ribbon bar. Next, press the “Error Checking” button.

In the drop-down menu, hover over the “Circular References” option to see a list of all circular references in your workbook. To move to the cell containing it, select the cell reference from the menu.

Excel will move to highlight the cell containing your circular reference, allowing you to resolve it manually.

If you’re dealing with complex formulas, it might be tricky to identify the source of a circular reference in Excel because of a large number of precedent or dependent cells. Precedent cells are cells that change the value of a selected cell, while dependent cells are cells that require the selected cell’s value to complete a calculation.

To help you work through these, you can trace a circular reference back to the source using the “Trace Precedents” and “Trace Dependents” tools. To trace a circular reference in Excel, open your workbook and select a cell containing a circular reference.

Next, select Formulas > Show Formulas. This will switch your workbook to formula view, allowing you to view all of the formulas in use without selecting individual cells.

This isn’t strictly necessary, as Excel will visually demonstrate the circular reference loop using arrows, but it can help you quickly identify the references for problematic cells in a large workbook.

With formula view active, and with the cell containing the circular reference selected, click the “Trace Precedents” button.

An arrow will appear, pointing to other cells—note the arrow trail to help identify the source of the problem.

Once you’re done, press “Trace Dependents” and note each cell that it points to.

As before, make a note of the cells causing the issue. Excel will visually identify the circular reference loop using arrows and circular icons.

For each circular reference you have, you may need to repeat these steps to get the full answer to resolve the problem.

The only way to truly solve a circular reference in Excel is to edit the calculation causing it. If the loop is broken, Excel can complete the calculation. The quickest (and best) way to do this is to replace the formulas containing cell references with matching values.

Here’s an example. Let’s assume again that three cells in your workbook (A2, A3, and A4) all contain simple calculations that reference each other. To resolve the circular reference, you’d need to replace the calculation in A2 and A4 with a value of equal value.