Fix Circular Reference That Cannot be Listed in Excel (4 Easy Ways)

In this article, we will demonstrate how to fix the excel circular reference that cannot be listed in excel. It’s very intimidating if you get circular reference errors while working on excel. While working on a large dataset that contains thousands of cells it can be very difficult to identify the cells that contain circular reference errors by checking each cell one by one. So, in this tutorial, we will explain how we can list circular reference errors easily from any size of the dataset.


Download Practice Workbook

You can download the practice workbook from here.


What is Circular Reference?

A circular reference is a formula that returns the same or another cell numerous times in its sequence of calculations, resulting in an infinite loop that severely slows down your spreadsheet.

To illustrate circular reference more clearly we will use the following dataset. The dataset consists of “Sales Amount” for six months. Suppose, we need to calculate the total amount of sales in cell C11.

What is Circular Reference?

Now, we have to select the cell range (C6:C10) in the SUM formula to get the result. If we accidentally select the cell range (C6:C11) you might not get the result that you are expecting.

What is Circular Reference?

The above formula in cell C11 gives us a warning of circular reference error. This happens because the formula in cell C11 is trying to enumerate itself too.

We can categorize circular reference errors into two types:

1. Direct Circular Reference:

A direct circular reference error shows up when a formula in a cell directly refers to its cell.

2. Indirect Circular Reference:

An indirect circular reference occurs when a formula in a cell doesn’t refer to its cell directly.


4 Easy Ways to Fix Excel Circular Reference That Cannot be Listed

When we will get a circular reference error in the time of calculation we must fix that or immediately. To fix that error first we need to locate them. So, in this article, we will use 4 different methods to list the circular reference error, and then we will fix the errors by modifying the formula.


1. Fix Circular References That Cannot be Listed with Error Checking Tool in Excel Ribbon

First and foremost, will use the ‘Error Checking’ tool from the excel ribbon to identify the circular reference errors that cannot be listed. To explain this method we will use the following dataset that contains a circular reference error in cell C11. The following dataset is just an example to make you understand better. When you work with a real-time dataset you have to find out circular references from thousands of cells.

Fix Circular References That Cannot be Listed with Error Checking Tool in Excel Ribbon

Let’s see the steps to list circular reference errors by using the ‘Error Checking’ tool.

STEPS:

  • Firstly, go to the Formulas tab.

Fix Circular References That Cannot be Listed with Error Checking Tool in Excel Ribbon

  • Secondly, from the excel ribbon under the Formulas tab select the drop-down “Error Checking”. From the drop-down menu click on the option “Circular References”.
  • The above action is showing in a sidebar that circular reference is happening at cell C11 in our worksheet.
  • In short: Go to Formulas > Error Checking > Circular References

Fix Circular References That Cannot be Listed with Error Checking Tool in Excel Ribbon

  • Thirdly, select cell C11. The formula in that cell is trying to calculate itself too.

Fix Circular References That Cannot be Listed with Error Checking Tool in Excel Ribbon

  • After, that modify the formula of cell C11 like the following one:
=SUM(C5:C10)

  • Press Enter.
  • Lastly, we can see that there is no circular reference error in cell C11. So, the amount of total sales in cell C11 is $17000.

Read More: How to Fix Circular Reference Error in Excel (A Detailed Guideline)


2. Use Status Bar to Fix Circular References in Excel That Cannot be Listed

Finding circular reference errors by using the “Status Bar” is the easiest way. To explain the process of how to list the excel circular reference that cannot be listed with the “Status Bar” we will continue with the same dataset that we used in the previous example.

Use Status Bar to Fix Circular References in Excel That cannot be Listed

Let’s see the steps to list and fix circular references with the “Status Bar”.

STEPS:

  • First, open the worksheet that contains circular reference errors.
  • Next, look at the “Status Bar” below the worksheet names.
  • From the “Status Bar”, we can see that there is a circular reference error in cell C11.

Use Status Bar to Fix Circular References in Excel That cannot be Listed

  • After that, modify the formula of cell C11 by changing the range from (C5:C11) to (C5:C10).
=SUM(C5:C10)

  • Press Enter.
  • Finally, the above commands fix the circular reference error in cell C11 and return the total amount of cells.

NOTE:

If any worksheet has two or more cells that contain circular references the “Status Bar” will only show the latest one.


3. Apply Iterative Calculation to Fix Circular References in Excel

We can also fix the excel circular reference that cannot be listed from our worksheet by using iterative calculations. We can list and fix the circular references in our worksheet by enabling iterative calculation in our excel worksheet. To illustrate this method will use the dataset of our previous example this time also.

Apply Iterative Calculation to Fix Circular References in Excel

Let’s take a look at the steps to perform this action.

STEPS:

  • In the beginning, go to the File tab.

Apply Iterative Calculation to Fix Circular References in Excel

  • Next, select Options.

  • Then, a new dialogue box named “Excel Options” will appear.
  • From that box select Formulas and check the option “Enable iterative calculation”. Set the value 1 for “Maximum iterations”. The value 1 indicates that the formula will iterate only once through the cells C5 to C10.
  • Now, press OK.

Apply Iterative Calculation to Fix Circular References in Excel

  • Finally, we do not get any circular reference errors in cell C11. It returns the total sales amount in cell C11.

Read More: How to Enable Iterative Calculation in Excel (with Easy Steps)


4. Find & Fix Circular References in Excel with Tracing Methods

We can not find and fix circular references with a single click. To fix the excel circular reference that cannot be listed we will trace them one by one. After tracing we will modify their initial formula to fix the circular reference errors. The tracing methods we will use in this section are “Trace Precedents” and “Trace Dependents”.

4.1 ‘Trace Precedents’ Feature to Fix Circular Reference

The “Trace Precedents” feature traces the cells that are dependent on the current cell. This feature will tell us which cells are affecting the active cell by drawing an arrow line. In the following dataset, we will return the sum of cell cells (C5:C10) in cell C11. So, cell (C5:C10) is affecting cell C11.

“Trace Precedents” Feature to Fix Circular Reference

So, let’s see the use of “Trace Precedent” step-by-step.

STEP:

  • Firstly, select cell C11.

“Trace Precedents” Feature to Fix Circular Reference

  • Secondly, go to the Formulas tab.
  • Then, select the option “Trace Precedents”.

“Trace Precedents” Feature to Fix Circular Reference

  • The above action draws an arrow line. It shows that cells (C5:C11) are affecting Cell C11. As cell C11 is trying to enumerate itself so it returns a circular reference error.

“Trace Precedents” Feature to Fix Circular Reference

  • Thirdly, modify the formula of cell C11 by changing the range in the formula to (C5:C10) from (C5:C11). The formula in cell C11 will be:
=SUM(C5:C10)

  • After that, press Enter. The above command removes the circular reference from that cell.
  • Finally, use the “Trace Precedents” option in cell C11 We will see that this time cells (C5:C10) are affecting cell C11 whereas in the previous step the cells affecting cell C11 were (C5:C11).

NOTE:

The keyboard shortcut to find Trace Precedents: ‘Alt + T U T


4.2 ‘Trace Dependents’ Feature to Fix Circular Reference

The “Trace Dependents” feature is used to find the cells that are dependent on the active cell. The feature will show us the cells that are depending on the active cell by drawing a line arrow. In the following dataset, we will list circular reference errors with the “Trace Dependents” option.

‘Trace Dependents’ Feature to Fix Circular Reference

So, let’s take a look at the steps to list circular references by using the “Trace Dependents” option.

STEPS:

  • First, select cell C11.

‘Trace Dependents’ Feature to Fix Circular Reference

  • Next, go to the Formulas tab.
  • Select the option “Trace Dependents” from the ribbon.

‘Trace Dependents’ Feature to Fix Circular Reference

  • Then, the above action shows that cells (C5:C10) are depending on cell C11 by drawing a line arrow.

‘Trace Dependents’ Feature to Fix Circular Reference

  • After that, adjust the formula of cell C11 by changing the range in the formula to (C5:C10) from (C5:C11). The formula in cell C11 will be:
=SUM(C5:C10)

  • Press Enter.
  • Lastly, we can see that there is no circular reference in cell C11.

NOTE:

The keyboard shortcut to find Trace Precedents: ‘Alt + T U D

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


Conclusion

In the end, this tutorial will show you how to fix excel circular references that cannot be listed. Use the practice worksheet that comes with this article to put your skills to the test. If you have any queries, please leave a comment below. We will try our best to respond to you as quickly as possible. Keep an eye out for more creative Microsoft Excel solutions in the future.


Related Articles

Mukesh Dipto

Mukesh Dipto

Hi there! I am Mukesh Dipto. I graduated with a bachelor's degree in engineering. Currently, I am working as a technical content writer in ExcelDemy. You will find all my articles on Microsoft Excel on this site. Outside of the workplace, my hobbies and interests include sports and meeting new people. I also enjoy sports. My favorite sports are football (to watch and play) and Badminton (play).

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo