Circular Reference Excel – How to Find and Fix Them [Tutorial Video] (10:47)

Circular Reference Excel: What Is It?

A circular reference in Excel is a case where the input of a cell depends on the output of that same cell. Here’s a simple example:

The contents of cell Q14 depend on the contents of cell R14 and… cell Q14.

The input of Q14 depends on its output – what’s already there – which creates a circular reference.

We know the circular reference is there because the bottom-left part of the screen now reads “Calculate”:

If that does not appear correctly for you in Excel, or you just get a simple error message, go to the Options menu (Alt, T, O on PC or ⌘ + , on Mac), Formulas, and make sure your settings look like this:

When this happens – when the input of a cell depends on its output – Excel gets “confused” about what to do, and it has to start using approximations to make the calculations. For example, here’s what happens if we enter the number 2 into cell R14:

Excel “loops” through this circular reference 100 times – per the settings in the screenshot above – to calculate the “answer,” which is 200 (since 100 * 2 = 200).

A circular reference could be direct – as it is here, where the cell’s output directly flows into the cell’s input – or indirect.

For an example of an indirect circular reference, let’s say that Q14 = R14 + S14, and that S14 = Q14 + Q13:

Cell S14 depends on what’s in Q14, but Q14 depends on what’s in S14.

In real life, circular references in financial models occur most frequently with Interest Income and Interest Expense – specifically, when you calculate those by using average Cash or Debt balances rather than beginning-of-period balances.

A company’s Interest Income depends on its Cash balance, but its Cash balance changes over the year.

So, if you use the Average Cash balance (i.e., =AVERAGE(Beginning Cash, Ending Cash)), then the Interest Income is linked to both of those.

BUT the Ending Cash balance also depends on the Interest Income! More interest earned means the company’s Ending Cash Balance will be higher.

The same goes for Interest Expense and the Average Debt Balance during the year, but in the opposite direction (lower Interest Expense means more ability to repay Debt, which, in turn, means lower Interest Expense).

Why You Should Avoid Them

You should avoid circular references whenever possible because they make models and spreadsheets unstable and difficult to modify.

If you try to edit a model where many rows or columns are linked to circular references, for example, deleting a single row or column could cause cascading #REF! errors because of the iterations required to complete a circular calculation.

However, you will encounter circular references “out in the wild” in other peoples’ models, so you need to know how to deal with them.

How to Find Circular References

If you go back to the Options menu, Formulas, and you un-check “Use Iterative Calculations” Excel will display an error message and blue arrows that show all the circular references on your current sheet, along with the exact cells at the bottom of the screen:

You can then go into these cells and change the formulas to remove the circular references.

If your file or spreadsheet has dozens or hundreds of circular references, then it may be more difficult to do this because there will be blue arrows all over.

In that case, you will have to go line-by-line and cell-by-cell to fix these. There isn’t really a “more efficient” way to do it because deleting all the circular formulas manually will create even more problems.

Excel Circular Reference Workaround: How to Avoid Them

In financial models, the best way to avoid circular references is to build in a “switch” that lets the user toggle between Average Balances and Beginning Balances when calculating items like Interest Income and Interest Expense.

In practice, the added “accuracy” from using Average Balances is so small that it’s irrelevant in nearly all cases.

We recommend the following setup as a workaround to this problem:

Then, whenever we calculate Interest Income or Interest Expense in the model, we use an IF statement to check the value in this named cell (1 or 0) and then use the Average Balances or Beginning Balance:

Setting this named cell to 0 will force all formulas to use the Beginning Cash or Debt Balances, meaning that there will be no circular references, and the model should remain stable.

We follow this practice in the examples and exercises in our Excel & Financial Modeling Fundamentals course.

About Brian DeChesare

Brian DeChesare is the Founder of Mergers & Inquisitions and Breaking Into Wall Street. In his spare time, he enjoys lifting weights, running, traveling, obsessively watching TV shows, and defeating Sauron.