A circular reference in Excel happens when a cell containing a formula is dependent on its own result in some way, creating a loop that can’t be resolved. If you want to stop this error, you’ll need to find and remove these references to allow Excel to complete the calculation. Here’s how.
What Are Circular References in 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.
This is because a cell containing a formula or calculation can’t refer back to itself (either directly or indirectly) without causing a circular reference error. An infinite loop of calculations is created that can’t be processed.
RELATED: The Basics of Structuring Formulas in Microsoft Excel
This means that you can’t see the value you’re looking for—Excel is stuck and won’t proceed.
For most Excel users, this is an unintentional result, as you’ll want the calculation to complete. To resolve the issue, you’ll need to hunt down the references and fix them.
Finding Circular References in Excel
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.
Using the Error Checking Menu
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.
Tracing a Circular Reference
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.
Fixing Circular References in Excel
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.
The conditions for the calculation in A3 would be met, allowing Excel to calculate the value without creating a loop. If you don’t want to use a direct value, you’d need to refer to a different cell reference—one that didn’t have any existing link to your selected cell.
This is a simple example, but the same principle is applicable in larger and more complex Excel workbooks.
RELATED: How to Hide Error Values and Indicators in Microsoft Excel
