Microsoft Excel cannot calculate a formula there is a circular reference in an open workbook

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:

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.

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.

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:

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.

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.

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

Array functions are past times

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.

Avoid long formulas

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.”

Calculate formulas only once

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.

Do not merge cells

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!

Don't neglect Excel errors

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.

(Hidden) circular references

(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.

How to keep formulas readable

Sometimes a formula can spill over onto 2 or 3 lines making the standard 1 line formula bar too small to read. So how can you make formulas readable?

Iterative calculation, friend or foe?

Among the infinite number of settings in Excel, one innocent looking option fundamentally changes the way Excel calculates formula results. It goes by the name Iterative calculation. This little known feature has its uses, but it doesn’t come without risk.

Keep a formula close to its input

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.

Never use hard coded numbers in formulas

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

Prefer INDEX & MATCH over VLOOKUP

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.

Reports

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.

Risk inspections

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

Risks & suggestions

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.

Spreadsheet risk detection

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

VBA risk detection

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.

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.