Home Article Index Links Newsletter

Deze pagina in het NederlandsHome > Article Index > Circular References >

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.



All comments about this page:

Comment by: Nigel Heffernan (4-5-2012 07:08:27) deeplink to this comment

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 (4-5-2012 07:35:26) deeplink to this comment

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:


Comment by: Pradeep Dive (18-7-2012 07:31:12) deeplink to this comment

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 (17-10-2012 11:16:11) deeplink to this comment

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


Comment by: Jan Karel Pieterse (17-10-2012 12:05:06) deeplink to this comment

Hi Colin,

My RefTreeAnalyser can tell you which circular references there are in your sheet:


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, will only be used to inform you when your comment is published or to respond to your question directly):

Your request or comment (max 2000 characters):

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

I give permission to process this data and display my name and my comment on this website accoring to our Privacy Policy.