Most Valuable Professional


View Jan Karel Pieterse's profile on LinkedIn subscribe to rss feed
Subscribe in a reader

Subscribe to our mailing list

* indicates required

File crashes Excel!!

Save my workbook!
The best tool for salvaging problematic workbooks.

Trainings

Excel VBA Masterclass (English)
Excel VBA for Financials (Dutch)

Third party tools

Speed up your file

FastExcel
The best tool to optimise your Excel model!

Repair your file

Stellar Phoenix Excel Repair
Best tool to repair corrupt Excel sheets and objects
Home > English site > Articles > Circular References > Detecting
Deze pagina in het Nederlands

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:

Circular references dropdown on 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:

RefTreeAnalyser showing all circular references

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 (5/4/2012 7:08:27 AM)

Good article, and well worth bookmarking: Googling for "Excel + circular references + not + detected" gives a load of "Here's the circular references toolbar" how-to guides, and nothing that I'd recommend to the advanced user.

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 (5/4/2012 7:35:26 AM)

Hi Nigel,

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 (7/18/2012 7:31:12 AM)

Hi Jan,

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 (10/17/2012 11:16:11 AM)

I am getting a circular in cell E20
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 (10/17/2012 12:05:06 PM)

Hi Colin,

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.

Please enter your name (required):

Your e-mail address (optional but if you want me to respond it helps!; will not be shown, nor be used to send you unsolicited information):

Your request or comment:

To post VBA code in your comment, use [VB] tags, like this: [VB]Code goes here[/VB].