Nuraida Kashmin
Sometimes we input an Excel formula in a worksheet but it’s not working for some reason. It shows a warning prompt instead which mentions Circular Reference. In this article, we are going to learn about how to find a circular reference in Excel. We also learn about fixing it or removing it with some beautiful examples and explanations.
Practice Workbook
Download the following workbook and exercise.
Circular Reference in Excel
When a formula is directly or indirectly mentioned to its own cell, a Circular Reference happens. It can be tricky. In the time of calculation, when the formula uses its own cell, circular reference occurs. Let’s say we have a dataset in the B4:B6 range. In Cell B7, we apply a formula:
=B4+B5+B6+B7This shows a warning prompt of circular reference as it creates an endless loop.
There are two types of circular reference-
1. Direct Circular Reference in Excel
If the formula is directly mentioned to its own cell, a direct circular reference occurs.
2. Indirect Circular Reference in Excel
If the formula is indirectly mentioned to its own cell, an indirect circular reference occurs.
2 Quickest Methods to Find a Circular Reference in Excel
1. Error Checking Drop-Down to Find a Circular Reference in Excel
Excel has some built-in options to find a circular reference. Assuming we have a dataset (B4:C8) of employees.
Steps:
- First of all, in Cell C8, we can type the formula:
=SUM(C5:C8)- Here the Excel returns the result as ‘0’.
- After hitting Enter, a circular reference warning box pops up. Then click on OK.
- Now to avoid this, we can go to the Formulas tab and click on the Error Checking drop-down. Then select the Circular References and it is showing the specific cell to solve in the worksheet.
2. Find a Circular Reference Using Status Bar in Excel
We can easily find a circular reference by using the Status Bar. Let’s say we have a dataset (B4:C8) of employees.
Steps:
- First, in Cell C8, we can apply the same formula as before and it will return the result as ‘0’.
- Now go to the status bar on the left part of the worksheet. There, under the worksheet name, we can see the circular reference along with the address of the cell.
Read More: How to Find and Handle Circular Reference in Excel
Fix Circular Reference in Excel
We can’t fix circular references easily with just one or two clicks as it is dependent on the formulas. We need to fix this one by one. To fix circular references, there are two methods-
- Trace Precedents
- Trace Dependents
We can find these features in the Formulas tab. Go to the Formulas tab from the Ribbon. Then select these two options from the Formula Auditing group.
1. Trace Precedents
This feature helps us trace the cell which is dependent on the current cell. We can see a line between the cells that are influencing the active cell. The keyboard shortcut to find it is ‘Alt + T U T’.
From the below dataset, we can see a blue line between the cells. That means cells C5, C6 & C7 are influencing cell C8.
2. Trace Dependents
This feature also helps us in tracing the cells dependent on the active cell. A blue line appears between the cells. The keyboard shortcut to find it is ‘Alt + T U D’.
From the below dataset, we can see that cell C8 is influenced by cells C5, C6 & C7.
Read More: Fix Circular Reference That Cannot be Listed in Excel (4 Easy Ways)
Remove Circular Reference in Excel
Sometimes circular reference may create problems in the calculation. In that case, we have to remove it. Assuming we have a dataset (B4:C8). Here we can see that for the circular reference it is showing the result ‘0’. We are going to remove it.
Steps:
- First, go to the File tab.
- Next select Options.
- Now we can have an Excel Options window. Click on the Formulas.
- Here from the Calculation Options group, check on the Enable iterative calculation box.
- Then click on OK.
- This will enable the iterative calculation and also remove the circular reference in Excel.
Read More: How to Remove Circular Reference in Excel (2 Ways)
Conclusion
By using these ways, we can find a Circular Reference in Excel. There is a practice workbook added. Go ahead and give it a try. Feel free to ask anything or suggest any new methods
