Hi Excellers, 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, if you want to learn some more about Excel formulas then you can check out Chandoo’s awesome Excel Formula Course. Click here to view more details
Let’s start at the start with…..
What Is A Circular Reference?
Simply put it is a formula in a cell that refers to itself directly or indirectly, so there are two scenarios when this occurs.
- 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.
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.
So now we have understand what they are the two different types (Direct and Indirect) we can now set about finding which cells contain circular references if we want to 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. Well here is how to check for them-
- Formulas Tab
- Formula Auditing Group
- Error Checking
- Circular 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).
- 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.