Working with Circular references in Excel
Finding the circular reference chains
Excel does try to help you locate a circular reference. Unfortunately it only shows the first one it encounters in the current calculation chain. You can access that information by Clicking on the Error checking dropdown which is on the Formulas tab of the ribbon:
If you are looking for a way to view all circular reference chains in the current workbook, I recommend downloading my RefTreeAnalyser utility, it lists all of them in one convenient window:
Sometimes, if cells within a circle have an Error result, the circle may no longer be detected by Excel as being a circular reference (unfortunately, I could not reproduce the problem when I tried to create a demonstration file)
Values affecting whether a circle is really working as a circle
If one of the cells inside a circle contains a function which may affect which arguments are in use (an IF function, a CHOOSE function, ...), then whether Excel detects a circle depends on that function's proceedings. This is what I used to create switch cells which can break your circles and set initial values.
Use of arguments in a UDF
Excel is smart. Very smart. Suppose you have a written a VBA User Defined Function (UDF) with two arguments. Excel is smart enough to detect which arguments are actually used in the calculation. So the argument values of the functions used in the circle may affect whether Excel decides it *IS* a circle, because one of the values might cause an argument of a function in the circle not to be used, hence potentially breaking the circle.
#Name error caused by missing UDF
If any cell within a circle contains a reference to a UDF that isn't available, the circle is not detected.
When a workbook is opened, calc settings depend on whether or not another workbook is already open in Excel. If you FIRST open a workbook with iteration turned off and THEN open the file with circular references, the circular reference warning shows up. If Iteration is turned on for the first workbook, opening subsequent workbooks does not trigger the warning.