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.

Introduction

Many spreadsheet developers will recognize this message of Excel:

Excel Pop-up Message :: How to find Hidden Circular References in your Spreadsheet :: PerfectXL

Excel detects a circular reference when a chain of calculations visits the same cell more than once.

Case

I received a spreadsheet from a colleague once. Everything looked perfectly alright, until I changed one of the input values.

Change Input Value :: How to find Hidden Circular References in your Spreadsheet :: PerfectXL

The nasty message popped up complaining about circular references. When I called my colleague, he told me I must have messed with his formulas.

Excel Pop-up Message :: How to find Hidden Circular References in your Spreadsheet :: PerfectXL

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:

Excel Ribbon :: How to find Hidden Circular References in your Spreadsheet :: PerfectXL

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:

Self-referencing Cell :: How to find Hidden Circular References in your Spreadsheet :: PerfectXL

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:

Risk Overview :: How to find Hidden Circular References in your Spreadsheet :: PerfectXL

Let’s look at the section called “Structure Issues”:

Structure Issues :: How to find Hidden Circular References in your Spreadsheet :: PerfectXL

Structure Issues :: How to find Hidden Circular References in your Spreadsheet :: PerfectXL

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:

Spreadsheet Structure Issues Details :: How to find Hidden Circular References in your Spreadsheet :: PerfectXL

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.

Text & narration: Jan Karel Pieterse, Microsoft Excel MVP since 2002, JKP Application Development Services

More Excel Add-in Video Tutorials

How to Unhide/Rehide Cells, Columns & Worksheets with the PerfectXL Add-in

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 :: Tutorial :: PerfectXL Add-in

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