If you often see the circular reference error message whenever you open a worksheet in Microsoft Excel but don’t know how to find circular references in Excel, nothing to worry about. Read this quick and effortless Excel tutorial to discover the methods to locate circular references in Excel.

To prevent inaccurate results in your data analysis, Excel shows error messages when you use incorrect arguments in a formula syntax. While all the formula errors display easy-to-notice error messages like DIV/0!, SPILL!, VALUE!, REF!, etc., a circular reference error doesn’t show any such indications.

Unlike other formula errors that are readily apparent, detecting a circular reference error in Excel isn’t as straightforward. The error isn’t immediately highlighted when you open a worksheet. Instead, you need to be familiar with specific methods to uncover circular references in Excel.

What Is a Circular Reference in Excel?

A circular reference in Excel occurs when a formula refers to its own cell directly or indirectly, forming a loop that hinders Excel’s ability to calculate a precise result.

This situation arises when a cell depends on its own value, creating a continuous cycle. Excel usually displays a warning when a circular reference is detected, as it can lead to inaccurate calculations.

Resolving circular references involves adjusting formulas or cell references to break the loop, ensuring accurate and stable computations. However, there could be mathematical reasons to use circular references in your worksheet. For example, look at the following situations:

• When estimating loan repayments or iteratively adjusting variables to reach a desired outcome.
• A cell’s value affects its own calculation based on dynamic external factors, creating a circular reference might be a workaround to capture these dynamic relationships.

Excel only shows a 0 in the cell that contains a circular reference. Which you can perceive as a result of certain formulas. If you know how to find a circular reference in Excel, you can test if the 0 value in a cell is legitimate or a result of a circular reference.

Reasons to Find Circular References in Excel

The primary reason to find circular references using manual intervention is to avoid 0 values in cells that should contain a valid numerical or string as a value.

When you’re working on an Excel worksheet containing hundreds and thousands of calculated fields and references, you can easily miss circular references disguised as zeros.

The issue often becomes a critical one if you’ve enabled iterative calculation from the Excel Options dialog. Excel won’t show the initial circular references error message when the iterative calculation is active.

Find Circular Reference Excel on the Status Bar

The Excel Status Bar is the most obvious place to look for any circular reference in the worksheet you’ve opened.

Excel Status Bar explicitly shows where the circular reference is, by showing the cell address, like C2 in the above example.

However, this method isn’t suitable if you need to find out more than one circular reference in an Excel worksheet. You must resolve the displayed circulate reference error and then Excel Status Bar shall show the next error.

Find Circular Reference Excel Using Tracing Arrows

You can use the Trace Precedents and Trace Dependents tool to highlight cells containing circular references visually. If your worksheet contains more than one circular reference, you can use this method to locate all of them quickly. No need to resolve one to get to the next error.

After highlighting a suspected cell range, you need to activate arrows for both the Trace Precedents and Dependents tool. If you see a line with two arrows and solid dots at the edge of the line, the circulate reference is in any of the two cells you’ve selected.

Highlight a cell range by clicking on a cell that contains a formula. If you select a cell range by clicking on a cell that contains a text string or non-calculated numerals, this method won’t work.

Now, go to the Formulas tab and click on the Trace Precedents button inside the Formula Auditing block. Also, click on the Trace Dependents button.

You should now see a line with arrows and solid dots on the highlighted cell range. The cells where you get arrows and solid dots contain circular references.

To remove the arrows from your worksheet, click on the Remove Arrows button.

Find Circular Reference Using Error Checking

The Error Checking tool is another visual and interactive method to find circular references in Excel.

This method is suitable for worksheets where you’ve resolved a circular reference and then accidentally added a new one. So, Excel won’t show the circular reference error but the Error Checking tool will directly take you to the cell containing the error.

Open your worksheet and go to Formulas > Formula Auditing block. Click on the Error Checking drop-down menu and hover the cursor over the Circular References option. You should see the cell addresses linked to the circular reference errors.

Find Circular Reference Using Evaluate Formula Tool

The Evaluate Formula tool also shows whether the selected cell contains a circular reference or not.

Simply, highlight the suspected cell and press Alt to activate the ribbon menu. Then, press M to go to the Formulas tab, and finally press the V key to open the Evaluate Formula dialog.

Above the Evaluate button, you should see a message if the formula has a circular reference error.

If your Excel worksheet contains multiple formula cells, highlight all those using the Go To dialog. Press Ctrl + G to bring up the Go To dialog and click on the Special button to open more conditions.

There, click on the Formulas option to highlight all the cells containing formulas in the worksheet.

While the cells are selected, go to Home > Font and click on the Fill Color command to highlight all the formula cells in your worksheet. Now, evaluate these formula cells one by one using the Evaluate Formula tool as described earlier.

Disable an Excel Feature to Find Circular Reference

When the iterative calculation feature of Excel is active, Excel won’t show the circular reference error message when you open the workbook.

So, if you suspect a workbook contains circular reference errors but there are no warning messages, open the Excel Options dialog from File > Options.

On the Excel Options dialog, go to the Formulas category on the left and uncheck the checkbox for the Enable iterative calculation feature. Close the dialog by clicking the OK button.

Now, close and reopen the Excel workbook to see the circular reference warning message.

Audit Select Formulas to Find Circular Reference

Formulas like IF, OFFSET, INDIRECT, SUM, VLOOKUP, INDEX, etc., often cause circular references in your Excel worksheet. So, it would help if you located all the cells that contain the above formulas and inspected them closely with the Evaluate Formula tool to locate circular references.

Bring up the Find and Replace dialog box and look for the IF formula in your worksheet. To do this, enter IF in the Find what field and click Find All.

A menu will show up below the Find and Replace dialog with all the cell address references that have the IF formula.

Highlight all the cell references by pressing the Ctrl + A keys inside the Find and Replace results menu. Excel will highlight all the referenced cells on the worksheet.

Now, highlight these cells with the Fill Color tool by navigating to the Home tab > Font commands block.

Now, repeat the above process for other suspected formulas like OFFSET, INDIRECT, SUM, etc.

Follow the steps mentioned in the Evaluate Formula Tool-based method to find out circular references in Excel.

Find Circular Reference Excel Using Excel VBA

Among all the methods explained so far, this Excel VBA-based method is the quickest. You just need to run a macro and Excel will highlight all the cells containing the circular reference error.

To create a VBA macro to highlight all circular references, press Alt + F11 to bring up the Excel VBA Editor. There, click on the Insert button and select Module. In the blank module that pops up, copy and paste the following VBA script:

Sub HighlightCircularReferences()
    Dim cell As Range
    Dim formula As String
    Dim circular As Boolean
    
    ' Loop through each cell in the active sheet
    For Each cell In ActiveSheet.UsedRange
        ' Check if the cell has a formula
        If cell.HasFormula Then
            ' Get the formula of the cell
            formula = cell.Formula
            
            ' Check if the formula contains circular references
            If HasCircularReference(cell) Then
                ' Highlight the cell in light red
                cell.Interior.Color = RGB(255, 200, 200)
            End If
        End If
    Next cell
End Sub

Function HasCircularReference(cell As Range) As Boolean
    Dim circular As Boolean
    Dim precedent As Range
    
    ' Check each precedent of the cell
    For Each precedent In cell.Precedents
        ' Check if the precedent refers back to the original cell
        If precedent.Address = cell.Address Then
            circular = True
            Exit For
        End If
    Next precedent
    
    ' Return whether circular references were found
    HasCircularReference = circular
End Function

Click the Save button on the Excel VBA Editor interface.

The Microsoft Excel dialog will open. Click No to get to the Save As dialog.

On the Save As dialog, click the Save as type drop-down, choose Excel Macro-Enabled Workbook (XLSM) file type, and click Save.

You can now close the Excel VBA Editor.

Press Alt + F8 to launch the Excel Macro dialog and select the HighlightCircularReferences macro. Hit Run to execute the script.

Excel will automatically highlight all the cells containing circular reference errors. The macro will only work on the active worksheet of the Excel workbook. To find circular references in another worksheet, go to that worksheet and run the macro again.

Conclusions

You can follow any of the methods mentioned above to find cells containing circular references in your worksheet to resolve formula errors.

If you’re a beginner and intermediate-level Excel user, try the manual methods that involve the Status Bar, Tracing Arrows, Error Checking, Evaluate Formula, and
Disable Iterative Calculation.

I’ve also mentioned a few formulas that you must closely track for circular reference errors.

Finally, if you’re an expert user or Excel VBA enthusiast, you should give the VBA macro-based method a try.

Not all circular references are errors. Mostly, you should resolve circular references that generate a zero value.

If you’ve found the article helpful, don’t forget to comment below!