Subscribe
in a reader
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:
Reasons why circular references may not be detected
Error results
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.
Calculation status
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.
Comments
All comments about this page:
Comment by: Nigel Heffernan (4-5-2012 07:08:27)
Explaining 'why', rather than 'how-to', is rather rare.
If any of your comments include additional reasons why circular references may not be detected, please put them up on the blog.
Masking by 'IF()' functions is a major headache in sheets that make heavy use of IF and CHOOSE to handle errors, or allow user overrides, or choose between external data sources.
I regret to say that the only foolproof way I've found for detecting circular refs on such a sheet is to iterate through all the precedents in a recursive VBA function, populating a dictionary with the cell addresses.
Comment by: Jan Karel Pieterse (4-5-2012 07:35:26)
Thanks for your thoughts, much appreciated.
I have written a tool that does that exact same thorough search, but it proved unusable on large models.
Unfortunately, most of the workbooks I have seen which contained circular references happened to be large :-(
My Reftreeanalyser does find most circular refrences, except the ones that the current calculation chain prohibits due to IF and CHOOSE functions:
http://www.jkp-ads.com/reftreeanalyser.asp
Comment by: Pradeep Dive (18-7-2012 07:31:12)
Good article, I was always thinking that this Circular Referencing error is some kind of Excel bug.
You explained it very well... Thanks.
Comment by: Colin Braude (17-10-2012 11:16:11)
E 20 contains the following: " = indirect( J20)"
J20 contains "= ADDRESS( $K20, 5)" and calculates to "$E$57" [ignore quotes]
E57 has the numerical value $10,580.00, which E20 displays.
There is no back-reference [e.g. from J20 back to E20] nor side references [to another route which may refer back to E20.
I am getting the correct results but have the annoying "Circular" or "Circulare E20" message displayed. This masks any genuine Circular References and confuses the recipients of my spreadsheet.
Any help will be gratefully accepted
Regards
Colin
Comment by: Jan Karel Pieterse (17-10-2012 12:05:06)
My RefTreeAnalyser can tell you which circular references there are in your sheet:
www.jkp-ads.com/reftreeanalyser.asp
The demo version will do that too.
Have a question, comment or suggestion? Then please use this form.
If your question is not directly related to this web page, but rather a more general "How do I do this" Excel question, then I advise you to ask your question here: www.eileenslounge.com.