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!

## 0 Comments