How to find (Hidden) Circular References in your Spreadsheet
(Hidden) circular references occur when Excel tries to compute a result of a cell that’s already been visited during the calculation round. Excel doesn’t warn us of conditional circular references. In this video we’ll show you how PerfectXL detects any circular reference in your spreadsheet.
Many spreadsheet developers will recognize this message of Excel:
Excel detects a circular reference when a chain of calculations visits the same cell more than once.
I received a spreadsheet from a colleague once. Everything looked perfectly alright, until I changed one of the input values.
The nasty message popped up complaining about circular references. When I called my colleague, he told me I must have messed with his formulas.
So how did this happen? First of all, let me explain what circular references are and how Excel handles them.
Types of circular references
Simplistically speaking there are only two types of circular references to discern:
- Deliberate circular references
- Accidental circular references
Intermezzo: Excel’s circular reference detection mechanism
Excel is capable of detecting circular reference chains. As soon as you click OK on the Circular reference warning dialog, you can find the first circle Excel detects in the Excel ribbon:
There is a snag however. If the current values cause the calculation to take a different path, it may very well be that Excel does not warn about that circular reference anymore. If for example any cell within the circle contains an IF function which uses different cells in its TRUE argument than it uses in its FALSE argument, it will depend on the result of the TEST argument whether or not the actual calculation will form a circle (This technique is used on purpose here).
The third and nasty type of circular reference
So, this means there is actually a third circular reference category:
- Hidden circular references
Let’s have a look at these three types.
Deliberate circular references
These are the realm of the experienced Excel developer, the Excel whizz kid who (thinks he) knows what he is doing.
Some people deliberately create (sets of) circular reference chains to have a calculation model do iterative calculations. Examples may include models of a chemical facility where (part of) an input stream of a process is a recycled output stream of the same process. Distillation plants often have such streams and if you want to calculate a model of such plant, using a circular reference may be a good way to model your process.
If -and only if- you are convinced you really need circular references to solve your problem, meticulously document your model, stating clearly what your intentions are and how the model functions. Especially important: Devise a way to clearly indicate the circular reference chain.
The most important thing to decide up front is whether the model you are trying to build actually is of a circular nature. Very often there is a way to rewrite your calculations in such a way that no circular references are needed. This is always the preferred method to use.
Accidental circular references
More often, people inadvertently create a circular reference, for example by having a SUM function which includes the cell the SUM function itself resides in.
Example: In cell A10, you write this function: =SUM(A1:A10)
Of course, this formula is intended to sum the values in the cells above the cell with the sum function, so the argument for the SUM function should be A1:A9.
Hidden circular references
Suppose you would like to record the date someone entered a value into column A. One frequently described method is to create a self-referencing cell with a formula like this one:
So as long as cell A1 is empty, the condition in the IF function in cell B1 evaluates TRUE and Excel only calculates the TODAY function and places that result into cell B1. So, whether or not Excel warns you about a circular reference depends on input values! As you can imagine, these hidden circular references make it very hard to detect potential problems in your Excel file.
There is a way my colleague could have prevented his spreadsheet model from displaying this annoying surprise message.
Finding Circular References
PerfectXL is capable of finding all these types of circular references even before Excel itself highlights them.
To find them, analyze your model and click the “Risk Overview” button:
Let’s look at the section called “Structure Issues”:
When you click it, all structure related issues will appear in this list.
Currently, there is only one: a circular reference. Clicking on one of the entries in the subsequently opened table will show more details about the issue:
As you can see this is one of those calculation-driven circular reference chains. Depending on the result of the MATCH function, the formula in cell C7 either points to cell C8 or cell C9. Given that cell C8 contains the formula =C7, only if the MATCH function returns 1 as its result will Excel warn you.
PerfectXL will warn you regardless of the intermediate results. It finds all potential circular references, even the hidden ones.
More Excel Add-in Video Tutorials
Unhide and Rehide Hidden Worksheets, Rows & Columns
Hiding sheets, rows and columns can be a neat solution to present the spreadsheet to an end-user, but very annoying to the spreadsheet developer. In this video we’ll show you how you can easily unhide and rehide hidden worksheets in Excel with the PerfectXL add-in.Learn how to unhide and rehide hidden elements
How to Spot Recorded Macros in Your Spreadsheet
VBA is very powerful, but when not properly programmed they can also pose a risk. Recorder macros in particular are unreliable and highly context-specific. In this video we’ll explain to you how to find them with the PerfectXL Add-in for Excel.Learn how to spot recorded macros in your spreadsheet