Excel - unable to find cell circular reference error

Cam12345 What if you do all these things and still cannot find that error.

Cam12345 

Below are the steps to find a circular reference in Excel:

1. Activate the worksheet that has the circular reference
2. Click the Formulas tab
3. In the Formula Editing group, click on the Error Checking drop-down icon (little downward pointing arrow at the right)
4.  
5. 

    

6. Hover the cursor over the Circular References option. It will show you the cell that has a circular reference in the worksheet
7. 

    

8. Click on the cell address (that is displayed) and it will take you to that cell in the worksheet.

Once you have addressed the issue, you can again follow the same steps above and it will show more cell references that have the circular reference. If there is none, you will not see any cell reference,

Another quick and easy way to find the circular reference is by looking at the Status bar. On the left part of it, it will show you the text Circular Reference along with the cell address.

There are a few things you need to know when working with circular references:

1. In case the iterative calculation is enabled (covered later in this tutorial), the status bar will not show the circular reference cell address
2. In case the circular reference is not in the active sheet (but in other sheets in the same workbook), it will only show Circular reference and not the cell address
3. In case you get a circular reference warning prompt once and you dismiss it, it will not show the prompt again the next time.
4. If you open a workbook that has the circular reference, it will show you the prompt as soon as the workbook opens.

Removing or Allowing Circular Reference 

The code below identify the first circular reference on each worksheet and backcolor it in red.

Sub ShowCircularReference()
    Dim wb As Workbook
    Dim ws As Worksheet

    Set wb = ActiveWorkbook
    
    For Each ws In wb.Worksheets
    
        ws.Select
        ws.CircularReference.Select
        Selection.Interior.Color = vbRed
        
    Next ws
    
    
End Sub