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

While trying to enter a formula in your Excel sheet, it doesn’t function for some reason. Rather, it informs you of a circular reference. Millions of users are stuck with the same issue every day as a result of asking an Excel function to calculate its own cell. This quick lesson walks you through the basics of circular reference error in Excel and why you should avoid them. You’ll also learn how to analyze, find, and fix circular reference error in Excel spreadsheets, as well as how to enable and use circular formulas if none of the prior options are available.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


What is A Circular Reference?

When you ended up with a formula in a cell, you create a circular reference – which performs the calculation using the cell reference (in which it was entered) like a snake chasing its own tail.

In other words, a circular reference happens when a formula, either directly or indirectly, refers back to its own cell, resulting in an unending loop of computations. The cell’s value will be changed every time the unending reference loop is not stopped.

Here’s an example to help you understand.

In the following table, we’ve shown statistics of the world’s one of the biggest brands Apple’s revenue over the past decade. For an instance, Using the SUM Function in the C16 field, we’re attempting to compute their total revenue. However, we gave the range C5:C16 to the SUM Function.

Circular Reference Error in Excel

It creates an endless loop because the supplied range also contains the C16 cell (in which the result needs to be written). Consequently, Excel will simply keep adding the new value to cell C16, which will keep increasing exponentially.

This triggers Excel to display a circular reference warning, as shown in the image below.

Circular Reference Error in Excel

 

📝 Note.

  • This is not an error because it will not halt or prompt the user to alter the computations. It’s a notification indicating the user’s computations are likely to be incorrect.
  • In many cases, when you enter more than one formula with a circular reference, Excel doesn’t display the warning message repeatedly.

Apply Iterative Calculations to Enable/Disable Circular References Error

Iterative computations are an interesting feature in Excel. If the user disables iterative calculations, Excel shows a Circular Reference alert and shows a 0 instead of the actual result in the cell. Because it is a never-ending loop, this is the case.

If you’re working with a huge dataset and want Excel to sit back and let you get on with your work without bothering you with circular reference warnings, you may utilize Iterative Calculations.

Enable/Disable Iterative Calculations

In the below screenshot, you can see there is a circular reference in cell C16 indicated in the status bar of the worksheet. Consequently, it results in zero (0) in cell C16. Now, if you want your job to do without any troubling issues, you need to enable Iterative calculations.

Apply Iterative Calculations

Let’s look at how to enable or disable iterative calculations in Excel now that we’ve established how they function. To enable or disable iterative calculations in Excel, follow the steps below.

Steps:

  • Go to the File tab ⇨ Options Formulas.
  • Mark the check box Enable iterative calculation.
  • Press Enter.

Apply Iterative Calculations

 

Consequently, you can see that the circular reference dialog is illuminated with calculate as shown in the screenshot below. Now, there is a value in cell16 instead of zero (0).

Apply Iterative Calculations

For that, each cell previously related with circular reference will be calculated.

Apply Iterative Calculations

Maximum Iterations & Maximum Change Parameters

The Iterative Calculations have two parameters:

Maximum Iterations

This loop will be repeated when Excel calculates the final result. This is completely under your control. Keep in mind that more iterations equal more Excel operating, which implies the machine will need more materials and processing capacity. It would also take longer.

Maximum Change

The maximum change is the number that must be achieved in order for the iteration to continue. It’s all about the outcome’s precision. Make this value as low as possible so that accurate results may be achieved. By default, the maximum change is set at 0.001.

Why Should We Avoid Enabling Iterative Calculations Deliberately?

It’s not a good idea to use Circular References in Excel. When you enable iterative calculations, the deliberate usage of circular references is a tricky approach to making things work that chews up a lot of resources and computing power. When you have to fix a few of them, it can often yield unwanted or inaccurate consequences, which can be frustrating.


Find Circular References Error in Excel

When a circular reference error occurs, you will receive a warning, but you will still need to find out which cell the issue occurred in. It will be easier to handle and solve the mistake once you know the particular cell position of the error.

These techniques are very useful when dealing with big datasets.

1. Use Error Checking Drop-down to Find Circular Reference Error in Excel

Here’s how to use the Ribbon in Excel to find circular references.

Step 1:

  • Go to the Formulas tab
  • Click on the Error Checking drop-down menu.

Find Circular Reference Error in Excel

Step 2:

  • Select Circular References from the option.

Excel will display all of the worksheet’s circular references in this section.

Find Circular Reference Error in Excel

As you can see in the screenshot above, the spreadsheet we’re utilizing has one circular reference in cell C16.

2. Use Status Bar to Find Circular Reference Error in Excel

It’s fairly simple to find circular references using the status bar. If the worksheet has a circular reference, the user will be able to view it in the status bar below the worksheet names.

Find Circular Reference Error in Excel

 

📝 Note.

  • This feature is disabled when the Iterative Calculation option is turned on, so you need to turn it off before you start checking the workbook for circular references.
  • It only displays the most recent circular reference and allows the user to go backward from there.

Fix Circular Reference Error in Excel

Circular references cannot be fixed in Excel with a single click. To deal with circular references, you must remove them one by one.

For example, after finding the error in cell C16, you need to rewrite the correct formula or select the correct range.

Step 1:

  • Select the correct range to fix the error in cell C16.

Find Circular Reference Error in Excel

Step 2:

  • Press Enter in cell C16 to see the results.

Find Circular Reference Error in Excel


Trace Relationships Between Formulas and Cells

When an Excel circular reference isn’t apparent, you may use the Trace Precedents and Trace Dependents features to draw one or more lines to demonstrate which cells impact or are affected by the selected cell.

In the below screenshot, we have shown the prospected revenue of Apple’s in 2022. Now we want to Sum up the total revenue from the years 2011 to 2022. To do this, we type the following formula in cell D10.

Trace Relationships Between Formulas and Cells

But as we have a circular reference error in cell C16, there will error value in cell D10. In this issue, we use Trace Precedents and Trace Dependents trace relationships between formulas and cells.


Trace Precedents to Relate Circular Reference Error in Excel

Traces the cells that supply data to a formula, drawing lines to show which cells have an impact on the selected cell. To do this follow the steps below.

Steps:

  • Go to the Formulas tab in Excel.
  • Select Trace Precedents.

Trace PrecedentsTherefore, you will see arrows that indicate the cells affect the active one.

Trace Precedents

📝 Note. shortcut to Trace Precedents: Alt+T U T


Trace Dependents to Relate Circular Reference Error in Excel

Draw lines indicating which cells are influenced by the selected cell and traces cells that are dependent on the active cell. To do this follow the steps below.

Steps:

  • Go to the Formulas tab in Excel.
  • Select Trace Decedents.

Trace DependentsAs a result, It displays which cells have formulas that refer to the currently active cell.

Trace Dependents

📝 Note. shortcut to Trace Decedents: Alt+T U D


Conclusion

To conclude, I hope this article has provided detailed guidance to fix circular reference error in Excel. All of these procedures should be learned and applied to your dataset. Take a look at the practice workbook and put these skills to the test. We’re motivated to keep making tutorials like this because of your valuable support.

If you have any questions – Feel free to ask us. Also, feel free to leave comments in the section below.

We, The Exceldemy Team, are always responsive to your queries.

Stay with us & keep learning.

Bhubon Costa

Bhubon Costa

Myself, Bhubon Costa, an engineer, is currently employed with Exceldemy as an Excel & VBA Content Developer. I appreciate solving analytical difficulties, finding practical answers, and, for the most part, I enjoy taking on new tasks. I write articles on Exceldemy about various ways to get out of Microsoft Excel's stuck conditions. My passion is to leave my mark on the world through my work and to have an impact on the community who benefit from it.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo