How to Find Circular References in Excel

If you’re getting an error message in your Excel spreadsheet that there’s a circular reference, that means that at least one of your calculations is referencing back to itself in one way or another. It doesn’t always have to be that two cells are referencing one another. Even indirectly referencing back to a cell can cause a circular reference error.

First thing’s first though, you have to find out where your circular references are before you can fix them, which isn’t always easy. Below, I’ll show you how to find circular references in Excel using multiple approaches.

Finding circular references

Let’s look at a very simple example where cells A1 and B1 are just referencing one another. This is what the error would look like on the two cells:

The blue arrows pointing at one another indicate that the cells are referencing each other. If the circular reference involves another worksheet, you’ll see something like this:

If you double-click on that line it’ll bring up the Go To dialog box which will show you which cell it’s referencing on another sheet:

From here you can click on the item in the Go To box and click OK and it’ll take you to that cell.

If you’re able to see blue arrows or the link to another spreadsheet, then it’s easy to trace where the circular references are . But if you’ve got a large spreadsheet, it may not be so easy spotting where the circular references are, which is why you may need to look at other methods.

One way is by looking at the bottom left corner of Excel, where you’ll see it mention circular references if there is an error:

And if you’re on the same sheet that the circular reference is on, you’ll also see which cell is causing it:

But it won’t be able to show you all of the circular references if there are more than one here as there’s obviously limited space to do that.

Another way that you can look for circular references is by going to the Formulas tab and then clicking on the down arrow next to the Error Checking button:

Then, click on the Circular References button and you’ll see a list of all the circular references in the workbook:

You’ll notice this way it will also show you which sheets have circular references.

Fixing circular references

Once you’ve identified which cells need to be fixed, then what’s left to do is adjust the cells. If you’ve just made an error then all this may involve is just deleting the contents of the cell entirely or just removing the reference to the cell in order to remove the circular reference.

But in many cases, it’ll involve a formula. And this is where it can get a little tricky as you may need to restructure your formula to ensure it’s not circling back in some way to the original cell. One possible solution can be to add another field as an intermediary to ensure you aren’t trying to do too much in just one formula.

If you liked this post on how to find circular references in Excel, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.