Many a time, while entering any formula in excel, you may have come across an error that says something about “Circular Reference”. In this blog, we would learn bits and pieces of “Circular Reference” in Excel.
Let us start with understanding how Microsoft defines the term “Circular Reference”.
Definition and Meaning
Circular Reference– Explaining the term Circular Reference in a few words, it is nothing but, the situation which occurs when a formula in a cell, directly or indirectly, takes the reference to the same cell in which the formula is written.
For instance, if you select cell F4 and type =F4 in it, and then press the “Enter” key, you would get an error stating something about circular reference.
To close this warning message, click on the “OK” button. As soon as you click on the ‘OK’ button, the Excel changes the value to zero or returns the latest value.
One more example of circular reference:
Finding a Circular Reference in Excel Worksheet
To resolve circular references, you’ll need to find the cell(s) that contain incorrect cell references. Like any other formula error (eg: #N/A, #NAME), the circular reference does not throw any error message in the cell that has circular reference in it. Excel, instead, enters zero (0) in those cells.
So, searching for the cells that contain a circular reference is a bit tedious task when your excel file consists of many formulas and calculated results.
Excel has provided functionality to find those cells that contain circular references in it.
Follow the below steps to achieve the same.
In the Excel ribbon bar, click on the tab ‘Formula’. Under the “Formula Editing” group, click on the downward-facing small arrow just beside the option “Error Checking”.
A drop-list of options would appear. Hover your mouse cursor to the option “Circular References”.
You would notice that the last entered circular reference displays in Cell $A$5.
Click on this cell $A$5 and excel will take you to that cell.
Removing the Circular References
Unfortunately, there is no method to remove all the circular references at a single click. To eliminate all the circular references in the excel worksheet, you need to perform the above steps again and again and then correct the erroneous cell formulas.