If you are an avid Excel user, you might have come across circular reference error. This is a widespread error and can be solved quickly.
In this article, we explain this error and how can you find the error in Excel.
What is a circular reference in Excel?
Circular reference, in essence, is an error that occurs when the formula in a cell calculates itself. When this happens, you will probably see this message:
There are more or one circular references where a formula refers to its own cell either directly or indirectly. This might cause them to calculate incorrectly. Try removing or changing the references, or moving the formulas to different cells.
When you press the OK button, the cell will return 0 (zero) because of the error.
Find the circular reference in Excel
If you are having difficulties in finding the circular reference, follow the steps mentioned below to locate the error.
Step 1: Click on the Formulas tab.
Step 2: Now, click on the Error-checking option.
Step 3: Select the Circular Reference option from the drop-down menu.
Now, if you cannot determine that the cell is the cause of the circular reference error, click on the next cell in the Circular reference option in Step 3. Multiple errors can occur if you are using many formulas in your sheet. However, in my sheet, there is only one error.
You will have to repeat the process until the status bar is no longer showing the circular reference error.
The status bar on the bottom left also shows the cell where the error has occurred. Take a look at the screenshot below to get an idea. You will notice that the cell G4 of the sheet has the error.
Also read:Â DDR3 vs DDR4 RAM
How to fix this error?
To fix this error, you will have to ignore the cell where you have applied the formula. For instance, I have applied the summation in the cell G4, which caused the error. So, to avoid the error, I will not include the cell G4 while adding the values from cell B4 to F4.
Some important points to remember
- While in most cases, the returning value will be 0 (zero), Excel can also display the last calculated value in some cases. For example, when you are using the IF function, Excel displays the last calculated value in case of the error.
- You will see the warning message only on the first instance of the error and not on the successive instances. However, if you have opened a workbook that already has this error, then you will see the circular reference error message.
- If you have a circular reference in other worksheets but not on the one that you are currently working, then the Status bar will not display the cell number of the error.
Also read:Â What is a CSV file and how can you open it?
