How to Find Circular References in Excel

Spreadsheets and graphs on a desk
xfgiro/istock

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.

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.

There are currently no replies, be the first to post a reply.