Hi Excellers, welcome back to another blog post in my #formulafriday series. Today let’s look at locating circular references in Excel really easily. Why would we need to do that?. Well, chances are if you use Excel regularly then you will come across some issues frustrations and circular references can very frustrating.

What is a circular reference in Excel?

Simply put it is a formula in a cell that refers to itself directly or indirectly. This is normally not intentional. There are two scenarios when this occurs in Excel.

• The formula contains a cell reference to the cell that contains the formula. An example of this can be seen below. The result of the formula will usually be zero, as well as the alert box displayed as can be seen above. Neither of the options offered by Excel will fix the problem, but simply offer a warning and some help ( if the Help button is selected.)

• A formula references another formula, which eventually references back to the cell containing the original formula. This is sometimes referred to as an indirect circular reference. An example can be seen where the blue arrows indicate circular references.

How To Identify Circular References In Excel.

So, now we understand what they are the two different types (Direct and Indirect) we can now set about finding which cells contain circular references. We can then get rid of them, and set our formulas up in a way that does not include them and calculates correctly.

If you have a very small workbook, then it maybe easy to spot the circular references, but say you have inherited a beast of an Excel workbook ( we all have haven’t we?) and you get the circular reference warning. Follow the steps below.

• Formulas Tab
• Formula Auditing Group
• Error Checking
• Cell References

The references will be displayed in a list for you.

• Click one of the references you want to check
• Click Trace Precedents to see which cells have an effect on the selected cell ( this helps if it is not immediately obvious why the cells is flagged as having a circular reference).

Why Avoid Circular References?

Examples of how they can cause problems

Tips for avoiding them in the future

The dangers of using circular references in spreadsheets that are used by others, such as an accounting system or payroll program

These are simply the Trace Dependents and Trace Precedents Arrows from the Formula Auditing Group. But it makes Excel look like it has gone a bit crazy, although it is just warning you about the presence of any circular references.

• You can now go head and fix the formula, then re-check to see if anymore exist in your worksheet, so your formulas will calculate correctly.

If you want more Excel and VBA tips then sign up to my monthly Newsletter where I share 3 Excel Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips. If you want to see all of the blog posts in the Macro Mondays Series you can do so by clicking on the link below.