How to Use Circular References in Excelby
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.
Figure 1: The formula displays zero because you have created a circular reference.
In every version of Excel the words Circular Reference will appear in the status bar, along with a cell reference. All of these indications go away once you correct the formula.
However, there can be situations where you may wish to intentionally create a circular reference. My go-to example when teaching circular references harkens back to my days when I worked in commercial real estate. Let’s say that a real estate developer has lined up the financing to build a $100 million building. The developer will earn a developer fee for its efforts; in this case let’s say 3 percent. However, the contracts get written so that it’s not 3 percent of the building cost, but rather 3 percent of the total project cost. The total project cost is the $100 million plus the developer fee. Therein lays the circular reference, as the fee is part of the calculation. Figure 2 shows the formulas involved.
Figure 2: Cell H3 contains the formula SUM(H1:H2), so the developer fee initially doesn’t calculate.
Notice that initially the developer fee doesn’t calculate. In this situation we need to instruct Excel to figure out the developer fee for us by setting an Excel option, as shown in Figure 3:
- Choose File, Options, and then Formulas.
- Click the checkbox for Enable Iterative Calculations, and then click OK.
Figure 3: Enable Iterative Calculations to calculate the developer fee.
Note that this is a workbook-specific setting, so you’ll have to enable this setting for each individual workbook that will involve circular references. Once you close the Options dialog box you’ll see that Excel calculates the developer fee of $3,092,784. Writing the contract so that the development fee is a based on the total cost gives the developer an extra $92,784, since 3 percent of the building cost itself would have been $3,000,000.
If we refer back to Figure 3, you’ll notice that there are two additional settings related to iterative calculations:
- Maximum Iterations: The default value is 100, which means Excel will try 100 times to resolve the calculation before it gives up. This setting protects you from putting Excel in a state of perpetual calculation.
- Maximum Change: Excel tries up to 100 times to resolve the calculation, and stops when the difference between iterations is no more than .001.
I can only think of a couple of times in my career that I’ve needed to tweak either of these options. In my experience changing the Maximum Change to .01 should suffice for most instances. Make sure that you save your workbook before you tinker with the Maximum Iterations or Maximum Change in case you accidentally put Excel in a state that you can’t get out of without crashing the program.
In an upcoming article I’ll describe a hidden feature in Excel that makes it easy to find all circular references within a workbook. Keep in mind that circular references must be used with care in Excel. If you simply enable iterative calculations to make the prompts and arrows go away you can end up with misleading results. For instance, that simple sum function that I created returns 60,000 instead of 600 if Enable Iterative Calculations is turned on, as shown in Figure 4.
Figure 4: Be careful enabling Iterative Calculations because they can lead to misleading results.
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.