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.
About David Ringstrom, CPA
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.