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.

Formula refers to 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:

Warning error in formula's own cell

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:

Indirect circular reference immediately apparent

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.

  1. Go to the Formulas Tab.
  2. Click Error Checking.
Error checking button
  1. Hover over Circular References. Excel will show you the cell reference and even select it for you.
Circular reference in Excel

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

Status bar displays circular reference

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.

Adding the blue arrow

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 + and for Trace Precedents, use Alt + U > T. Easy, no? 😀

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

Trace dependents point

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.

Trace precedents to provide data

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:

Selecting cells in sequence

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:

  1. Go to File > Options.
Selecting options
  1. Click Formulas.
  2. Check mark the Enable Iterative Calculation box.
Calculation options
  1. 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 box and maximum change box

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.

Kasper Langmann, Microsoft Office Specialist

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 errorthe Nested IF function, and others.