MRI Scan by Damadian // PerfectXL Spreadsheet Validation

PerfectXL Risk Eliminator

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:

How to find a circular reference in your spreadsheet // Excel popup // 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.

How to find (hidden) circular references // PerfectXL Risk Eliminator

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

How to find circular references in your Excel file // 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:

How to find a circular reference in your Excel file // Excel ribbon // 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:

A self-referencing cell // Circular References in Excel // 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:

How to find circular references in Excel // PerfectXL Add-in

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.

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

Finding circular references in Excel files // Structure issues // PerfectXL

When you click it, all structure related issues will appear in this list.

How to find circular references in Excel files // PerfectXL Risk Eliminator

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:

Details of structure issues in spreadsheets // PerfectXL Risk Eliminator

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

  • Avoid >70 types of risks
  • Improvement suggestions
  • Quality reports

Read more about risks in Excel files

On this page you will find a step-by-step walkthrough of the PerfectXL Risk Eliminator.

Here you will find information like system requirements, release notes, and answers to most questions about our software.

The VLOOKUP and the combination of INDEX and MATCH are well known, and to our great frustration, VLOOKUP is used a lot more often. The combination of INDEX and MATCH is less error prone and a lot more efficient.

Our software inspects spreadsheets on all kinds of spreadsheet risk. Read more about some of the risks we detect in spreadsheets here.

Excel horror stories are often related to accidentally changing, shifting or changing units or number formats.

Long ago, Excel was not as clever and advanced as today, but the so-called array functions have been around for a long time. However, most people do not know about them, and do not understand them.

Even Thomas Jefferson, 19th century president of the United States, remarked: “The most valuable of all talents is that of never using two words when one will do.”

The use of fixed numbers is a bad idea. A future user will not know where the number came from, and fixed numbers don’t change automatically, and thus might be overlooked when a change is made.

Do not hide anything except some sheets. Excel has too many attractive options that look good but may be risky in the long run.

You might need to run a calculation for inflation, the result of which is the needed in several other formulas. You might be tempted to calculate this formula twice, but resist the temptation.

It is so tempting to merge cells in Excel so that they form a header above two or more columns. Yes, we must admit, it looks nice, but resist the temptation because it can be dangerous!

It’s a bad idea to leave standard Excel errors in your workbook. Take the time to clean up, because after a while, you no longer know whether you left a mistake consciously or if there is something wrong with your spreadsheet.

Place a formula close to its input variables. You reduce the chance of mistakes, you make optimal use of Excel’s support, and your spreadsheet becomes easier to carry over to someone else.

To be able to trust a spreadsheet, a thorough check for errors and mistakes is essential. There are lots of different ways a simple mistake can destroy the validity of a spreadsheet.

Reporting is an integral part of Excel. Reports are generated in Excel, reports are built in Excel, and in many cases reports are a form of documentation required to properly use certain models in Excel.

Good, error-free spreadsheets are essential for using them reliably in business. This is why risk detection is an important aspect of spreadsheet validation.

Excel is a powerful tool. And when it can’t do what you need, VBA can fill the gap. Unfortunately, using VBA can lead to unpredictable behaviour, slow performance, and corrupt Excel files.

Do you wish to refine PerfectXL Risk Eliminator’s analysis to cater for your specific requirements? PerfectXL allows for full customization of your user interface.

How can PerfectXL help you?

Share your questions with us, we are more than happy to help you. We will get back to you within 48 hours.