Understanding the mechanism by which Excel parses sheets and determines circular refs

Hi everyone

Warning: some will find this question spurious as it is not related to the regular, rational use of Excel. Stop reading if that troubles you!

A slightly obscure question for you all. When there is a circular reference in a sheet (Excel 2013), a blue arrow appears between the pair of cells. If there are multiple circular references in a sheet, only one pairing is indicated in this way - the remaining circular references remain unindicated. However, the pairing indicated by the blue arrow is not static - as unrelated edits are made to a sheet, every now and then the indicated pairing will lose its blue arrow and another pair will be indicated instead. This appears to be an unpredictable behaviour - both the timing of the change, and which new pairing gets indicated. Does anyone have any insight into what's going on under the hood here? Is there a predictable way to control this behaviour? And is there a way to have blue arrows show up for All the circular references at once?

My interest in this has nothing to do with managing circular references in the regular sense. I play around with spreadsheets attempting to exploit various functions, quirks, glitches etc to artistic ends - so I'm dabbling with what can be achieved with circular references.

Many thanks,
Jordan

Hi

From what I understand Excel usually shows errors or highlights items based on where it assumes you will be going next (by default right then down), depending on your selected cell. So if you have a circ ref to the left of the currently selected cell and none to the right, it will move down to the next row and start moving right to highlight the next one.

In terms of showing circ references you can do this manually (although it will be cell by cell), by selecting the a cell with a circular reference, the goto formulas tab and click Trace Predendents and/or Trace Descendents to show the cells that the selected cells formula calculates from/to (see image). These blue lines will stay up permanently until you clear them.
Circ Ref tracing.jpg

you can sometimes speed the process up by setting iteration limits for Circular references and look for returned faults or errors, but really depends on whether or not you're looking for faults.

I often use Circular references in worksheets because one or other of the cells will be overwritten with data and the other will calculate based on that.

For instance a value vs percentage. Sometimes the information is supplied as a percentage of the cost, other times as a specific value and I want both to be displayed, so rather than have blank cells and calulate each time I set circular references between them and overwrite as required.

Thanks for the tips everyone. =D