How to find a circular reference
If you are dealing with a small set of data, you can easily find circular references. But what if you have a report to finalize in an hour, and the data is spread over hundreds of rows?
You must be sweating already, but don’t worry. There’s a trick to finding circular references in Excel seconds⌛
Let’s see it through an example.
In the data set below, we have applied the SUM function to sum each row.
Now, if we change the formula entered in Cell B9 from SUM(B2:B8) to SUM(B2:B9), here’s what happens.
A circular reference error appears. This is because the formula SUM(B2:B9) refers to the same cell (Cell B9) where the formula itself is typed in.
If you click Ok, the SUM function will enter an endless loop and perform hundreds of calculations😵
It will then return an approximate result of the calculation i.e. zero – like this:
In this example, we exactly know where the circular reference exists for this formula (Cell B9).
But if you don’t know what causes the circular reference, you can still find it as follows:
- Go to the Formulas Tab > Formula Auditing.
- Click Error Checking.
- Hover over Circular References.
Excel will show you the cell reference and even select it for you.
You can find the circular reference in the status bar as well.
If you still can’t find circular references in an Excel sheet, you can use trace arrows for help.
You can access them from the Formula Tab under the Formula Auditing group. These are represented by blue arrows.
There are two types of arrows, namely:
- Trace Dependents
- Trace Precedents