# How to Find Circular References in Excel (+ How to Fix Them)

So, you tried applying a formula to a data set, and instead of getting an answer, excel showed a circular reference warning message. Yeah, we’ve been there too.

Hundreds of thousands of people face this problem simply because they use direct circular reference.

Well, worry not. It is not difficult to find a circular reference error. And we’re here to teach you how to do that in no time. 😀

So without further ado, let’s jump right into the details.

If you wish to practice along, you can download our sample workbook here.

## How To Find A Circular Reference

If you are dealing with a small set of data, you can easily find circular references. But what if you have a report to finalize in an hour, and the data is spread over hundreds of rows?

You must be sweating already, but don’t worry. There’s a trick to finding circular references in seconds. 😀

Let’s see it through an example.

In the data set below, we have applied the SUM function.

Now, a circular reference occurs in Excel when we enter the reference of a cell directly into a formula.

This means if we were calculating the sum of cells from **B1:B8 **and we accidentally enter cell **B9 **(one with the SUM function), Excel will show an error message like this:

If you click Ok, the SUM function will go in an endless loop. It will calculate incorrectly and perform hundreds of calculations.

It will then return an approximate result of the calculation; zero – like this:

Since we know where the circular reference exists in this data set, we can easily find it. But if you don’t, you can use Excel’s help.

- Go to the
**Formulas Tab**. - Click
**Error Checking**.

- Hover over Circular References. Excel will show you the cell reference and even select it for you.

You can find the circular reference in the status bar as well.

If you still can’t find circular references in an Excel sheet, you can use trace arrows for help.

You can access them from the **Formula Tab** under the **Formula Auditing group**. These are represented by blue arrows.

There are two types of arrows, namely:

- Trace Dependents
- Trace Precedents

**Pro Tip!**

You can use a keyboard shortcut to bring about the trace relationships too. For Trace Dependents, use **Alt** + **T **> **U **> **D **and for Trace Precedents, use **Alt** + **T **> **U** > **T**. Easy, no? 😀

**Trace Dependents** points toward the cell whose value would be affected in case of change in the data.

As evident from the image above, if we were to change cell B4, the value of B9 would be affected.

Trace Precedents shows the cells that help you form the final result.

In our case, the Sum of cells B1:B8 results in the value in cell B9. So Trace precedents point towards cell B9.

## How To Fix A Circular Reference

So we know how to find a circular reference. But how do you fix one?

Unfortunately, Excel has no defined method for removing circular references. This is why enabling circular references is not advised.

However, if you do want to remove circular references, you need to evaluate all the cells having circular references in Excel.

Then you can choose to alter the formula or add a new one to fix the error.

If your data is small, like the one used above, you can easily spot the cell and change the circular reference. Similar to what we did here:

But if your data is large, you will have to find circular reference using Error checking and then eliminate the reference.

You can also use trace arrows to help find the circular reference.

## Disable Circular References: Iterative Calculation

The circular reference is not enabled in Excel by default, and this is why it shows the error message prompt.

However, you can enable iterative calculations if you want to use circular formulas.

But be warned❌. Doing so is not recommended.

Nevertheless, to enable iterative calculation:

- Go to
**File**>**Options**.

- Click
**Formulas**. - Check mark the Enable Iterative Calculation box.

- If you want to disable it, click the box again.

You have successfully disabled circular references in Excel. 😀

You can also adjust the maximum iterations and maximum change from the Calculation options:

**Maximum Iterations** tells Excel how many times the result is to be recalculated. This causes changes in the period where the result is accessible and easy to understand.

If the maximum iteration number has passed, but the criterion still isn’t met, Excel will stop calculating the result and return the final value as zero.

**Maximum Change**, on the other hand, measures the precision of the result. It allows you to decide how far the value can go before returning the result.

Excel will perform the last successful calculation before the maximum change reaches its peak value. Once there, it will stop recalculating and give the final result.

In Excel-language, 1 means TRUE. 0 means FALSE.

When you enter our two criteria in the next step, the 1 in the MATCH function simply means:

“Look through the rows in the data and return the row number where all of the criteria are TRUE”.

If you wrote a zero, the formula would look for a row where all of our criteria are FALSE – and that wouldn’t really make sense.

## That’s it – Now what?

In this article, we learned how to find and fix circular references in Excel. We also saw how to enable and disable Excel circular references.

It can be slightly challenging to locate the circular reference if you are dealing with extensive data. But with a little help and information, you can fix that problem easily. 😀

If you want to learn more about Microsoft Excel, know that there are no limits. You can start with the basic functions like VLOOKUP, IF and SUMIF, etc.

If you want to learn these handy functions (and more!), then enroll in my 30-minute free email course now.

## Other Resources

Did you enjoy reading this article? If yes, then you’ll love exploring more such topics on Excel functions.

You can check out our other blog posts, like the #VALUE error, the Nested IF function, and others.