Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Find a Circular Reference in Excel (2 Easy Tricks)

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+B7

This 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.

Error Checking Drop-Down to Find a Circular Reference in Excel

Steps:

  • First of all, in Cell C8, we can type the formula:
=SUM(C5:C8)
  • Here the Excel returns the result as ‘0’.

Apply SUM Function to Check Circular Reference

  • 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’.

Find a Circular Reference Using Status Bar in Excel

  • 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.

how to find a 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-

  1. Trace Precedents
  2. 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.

Fix Circular Reference in Excel


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.

Use Trace Precedents


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.

Use Trace Dependents


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.

Remove Circular Reference in Excel

Steps:

  • First, go to the File tab.
  • Next select Options.

Remove Circular Reference in Excel

  • 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.

Remove 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


Related Articles

Nuraida Kashmin

Nuraida Kashmin

Hi Everyone! I am Nuraida. Cordially welcome you to my profile. I am a Team Leader of Excel and VBA Content Developer in ExcelDemy. Here I will also post articles related to Microsoft Excel. With a strong educational background in Mechanical Engineering through experimental learning from Rajshahi University of Engineering & Technology. Skilled in Microsoft Word, Creative Writing, Microsoft Excel, Project Management, Support to Organize Different Events, Reporting, Monitoring & Documentation, Online Advocacy and Event Management Related to SAP and Youth Leaders.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo