How to Use Circular References in Excel

Circular references
iStock/Andrey Prokhorov
Share this content

If you’ve used Microsoft Excel for any length of time then most likely you’ve inadvertently created a formula that results in a circular reference. This means that the formula you created includes the cell where the formula resides. In most every instance in Excel when we create formulas we refer to cells other than the one where our formula resides. However, there are situations where you may want to purposely create a circular reference.

You’ve most likely created an accidental circular reference by creating a SUM that includes the total row itself, as shown in Figure 1. The results that you see will vary based on your version of Excel:

  • Excel 2013—A simple prompt will appear informing you that you created a circular reference, giving you the option to choose OK or Cancel. If you click OK, the formula appears in the cell but might display zero, as shown in Figure 1.
  • Excel 2010 and earlier—You might have the impression that your computer is about to crash. Not only does Excel give you a warning prompt, but it also draws one or more arrows on the screen and displays a help screen.

Please Login or Register to read the full article

To access all of the content on our site, register (it's free!) or login to your existing account.

BONUS: If you register now you can opt to receive a digital copy of "Transform!" , Richard Francis' new book for growing firms [US/Canada ONLY].

About David Ringstrom, CPA

David Ringstrom

David H. Ringstrom, CPA, is an author and nationally recognized instructor who teaches scores of webinars each year. His Excel courses are based on over 25 years of consulting and teaching experience. His mantra is “Either you work Excel, or it works you.” David offers spreadsheet and database consulting services nationwide.

Replies

Please login or register to join the discussion.

avatar
By Col D
Jun 26th 2015 01:12

In regard to the building development example, why not just use a simple algebraic solution rather than add risk to your spreadsheet by enabling circular references?

The formula in cell H2 (to calculate the developer's fee @ 3%) needs to be:
=$H$1/(1-$I$2)*$I$2 (Note that I've made the references absolute just so they can be easily distinguished as the column reference I looks a lot like a 1)

Thanks (0)
avatar
By Michael
to Old Cat Woman
Jun 26th 2015 01:12

Probably because a lot of people don't think in algebraic terms, but your solution is elegant. For those of us that might want the steps involved to how Col D got it, do the following:
We know that Developer fee X + 100,000,000 = Y (total cost).
We know that the developer fee is 3% of Y
So we can rewrite equation in terms of Y
.03Y + 100,00,000 = Y
subtract .03y from both sides
100,000,000 = 0.97Y
Divide by .097
Y = 103,092,784
Times by .03 to get developer fee or subtract 100,000,000.
Developer fee = 3,092,784.
Once you do that and understand it, you can make the equation that Col describes easily.

Thanks (0)
avatar
Sep 16th 2017 20:31

As a long-time daily user of Excel, I strongly recommend against allowing circular references in your spreadsheets (and against enabling iterative calculations). As you note, it can lead to errors, which would be pretty embarrassing if you're analyzing a $100 million transaction. In the example you cited I agree with Col D that it is less risky to use algebra so that there is no circularity.

Plus since spreadsheets are commonly shared, you would have to make sure that everybody you sent it to knows how to enable the iterative calculation.

However, sometimes circular references are harder to avoid. Let's look at your building example. Suppose the lender charges a 1% loan fee and provides an 80% loan to cost. That part you can still handle with the algebra, but now add another wrinkle: because it's new construction, the lender will be funding the interest as well. Thus the interest itself is another part of the cost of the project, and so the loan fee is in part based on the interest, and at least some of the interest is based on the loan fee.

What I strongly recommend is essentially doing the iterative calculation yourself. Plug in a number for interest in calculating the total cost, then check that against the number the spreadsheet has calculated elsewhere. Not right? Try again. I'll usually put a checker in the next cell. If the two interest amounts are close enough it displays "Ok" and if not it says "Error".

It's annoying to have to do it, and it gets even worse when you add additional layers like mezzanine financing; sometimes you need to go back and forth between two plugged numbers several times before both are within the rounding tolerance. But it's better than having errors creeping in from circular references.

Thanks (0)