No circular references (when a formula or function references itsself) was one of the essentials in my Five Formulas and Functions Essentials post. Generally, if you have a circular reference, you’ll get an error message warning you about it. But the message doesn’t tell you where your circular reference is. And if you click Cancel on the message, it leaves the circular reference in the spreadsheet. So the question becomes, how do you find a circular reference in Excel?
Find a Circular Reference in Excel
Excel has built in error checking tools that will help you locate circular references.
- Go to the Formulas tab.
- In the Formula Auditing section, click the drop down arrow to the right of the Error Checking button.
- In the menu that drops down, hover over Circular References. A window will pop out to the side telling you which cells contain circular references.
Need to Use a Circular Reference?
In some cases, you may need to use a circular reference. If you do, Chandoo.org (a great Excel resource) has a really useful post all about how to use circular references. Read Chandoo’s post on circular references.