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.

## What is A Circular Reference?

When you end 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 biggest brands Appleâ€™s revenue over the past decade. For 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**.

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.

**📝 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 be done without any troubling issues, you need to enable 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**.

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 cell 16 instead of zero (**0**).

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

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

**Step 2:**

- Select
**Circular References**from the option.

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

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

**Read More:** How to Find a Circular Reference in Excel

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

**📝 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.

**Read More:** How to Allow Circular Reference in Excel

## 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**.

**Step 2:**

- Press
**Enter**in cell**C16**to see the results.

**Read More:** How to Remove Circular Reference 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**.

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 **to 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**.

Therefore, you will see arrows that indicate the cells affect the active ones.

**📝 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 trace 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**.

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

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

**Download Practice Workbook**

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

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

Stay with us & keep learning.

## Related Articles

- Fix Circular Reference That Cannot be Listed in Excel
- [Fixed!] Iterative Calculation Not Working in Excel

**<< Go Back to Circular Reference |Â Cell Reference in Excel |Â Excel Formulas | Learn Excel**