Below are the steps to find a circular reference in Excel:
- Activate the worksheet that has the circular reference
- Click the Formulas tab
- In the Formula Editing group, click on the Error Checking drop-down icon (little downward pointing arrow at the right)
- Hover the cursor over the Circular References option. It will show you the cell that has a circular reference in the worksheet
- 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:
- In case the iterative calculation is enabled (covered later in this tutorial), the status bar will not show the circular reference cell address
- 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
- In case you get a circular reference warning prompt once and you dismiss it, it will not show the prompt again the next time.
- 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