How to Find Circular References in Excelby
In a recent article I explained the concept of circular references and how users sometimes intentionally or unintentionally incorporate such types of formulas into spreadsheets. In that article I used an extremely simple example of calculating a fee that involved a circular reference between two cells. However, sometimes complex forecasting models require circular references that can be scattered throughout a large spreadsheet. Further, Excel sometimes puts itself in a state where it can’t resolve the circular reference. The solution is to temporarily disable the formulas and then enable them again once the formulas work. However, tracking down all of the tendrils of a circular reference was always tricky for me until I discovered a hidden feature in Excel.
As illustrated in Figure 1, we’ll set up another simple example to set the stage for the feature that I’ll unveil. In a blank workbook, let’s replicate the simple example I used in my first article, but this time across three worksheet tabs:
- Sheet1: In cell A1 enter the words Building Cost and in cell B1 enter 100,000,000.
- Sheet2: In cell A1 enter the words Developer Fee and in cell C1 enter 3%. Leave cell B1 blank for the moment.
- Sheet3: In cell A1 enter the words Total Building Cost and in cell B1 enter this formula: =SUM(Sheet1:Sheet2!B1)
- This creates what is known as a drill-through formula, where Excel drills through two or more worksheets and sums the same cell or cells on each sheet.
- This sets the stage for our circular reference, so back in Sheet2 enter this formula in cell B1: =Sheet3!B1*C1
- When you press Enter, depending upon your version of Excel, any number of prompts and indications may appear on the screen.
To enable circular references, turn on Excel’s Iterative Calculation option:
- Excel 2007 and later: Choose File, Options, Formulas, and then click Enable Iterative Calculations, as shown in Figure 2.
- Excel 2003 and earlier: Choose File, Properties, Calculation, and then click Iteration.
- Excel 2011 for Mac: Click on the Excel Menu, choose Preferences, Calculations, and then click Limit Iteration.
This should cause Excel to resolve the formula, such that cell B1 of Sheet3 returns $103,092,784. At this point our formula works the same as the previous simple example. However, let’s imagine that our circular reference involves dozens of cells, perhaps scattered across numerous worksheets. Finding the starting point of a circular reference amidst a sea of formulas can take a huge chunk of your time, particularly in a spreadsheet that you didn’t write. Fortunately, in Excel 2007 and later, there is a feature that will allow you to easily track down the location of any circular references:
- Choose Formulas.
- Click the arrow next to Error Checking.
- Choose Circular References.
As illustrated in Figure 3, this feature is presently disabled because we turned the Iterative Calculation option on.
If we use the steps from above to turn off this feature, and then return to the Circular References menu command, you’ll see that this time Excel gives you a road map to the cells that are related to each other in a circular fashion, as illustrated in Figure 4. You can use this information to rewrite the formula in a non-circular fashion, or to place an apostrophe in front of certain cells to disable the formulas. Doing so can force Excel out of a situation where all circularly related formulas return #VALUE!. Once you’ve broken the cycle, you can enable each formula again to restore the circular references if needed.
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.