TABLE OF CONTENTS A circular formula is one which directly or indirectly refers to itself. Most of the time they are produced in error, and Excel wastes no time in filling your screen with dialog boxes, Help windows, toolbars and blue audit lines. Quite often it is a simple matter to locate and rectify a circularity, but in some cases it can be rather difficult, especially if the predecessor trail extends over multiple sheets. However, sometimes we are faced with calculations that are inherently circular. In this section, we will review some techniques for locating the accidental circularity, and then consider how to solve the deliberate or intentional circularity.
You should recognise that a circular model is fundamentally broken. Somewhere you have a calculation that is no longer recalculating, and dependent formulae similarly fail to recalculate. At this point, your model has the functionality of a table in Microsoft Word.
We have all had occasions when we have written a trivial formula and suddenly Excel fires up the circular warning. If you are lucky, the blue audit lines which then fill your workbook are meaningful and you can locate the source of the problem. But sometimes the problem is rather more intractable it is hard to believe but some users continue working on the model despite the warning. Unfortunately, Excel has never had something as useful as a #CIRC! error, which would make life so much simpler. Instead we have to examine the model carefully.
