How to Find Circular Reference Cell in Excel

Circular reference cells are a common Excel problem, encountered by countless users on a daily basis. They occur when a formula in a cell refers back to the contents of that same cell in some way, creating a problematic infinite loop. Usually, this is unintentional, and needs to be fixed. But first, you have to track down the offending cell in order to be able to make the changes.

This guide will show you how to find circular reference cells and how to fix them.

How to Find Circular Reference Cells

In a small spreadsheet with relatively straightforward information organization, finding circular reference cells usually isn’t too tricky. You could simply click on the various cells and inspect the formulas to see where and how the circular reference problem occurred.

However, in big spreadsheets, filled with lots of numbers and formulas, finding circular references seems much harder. Fortunately, Microsoft Excel includes a handy feature that lets you track down circular reference errors with just a few quick clicks:

1. Open the Excel workbook for which you want to find circular references.
   
2. Click on the “Formulas” tab at the top of the screen.
   
3. Find the button marked “Error Checking” and click on the little dropdown arrow beside it.
   
4. This will reveal a new menu, where you should see a button called “Circular References.”
   
5. Hover the mouse over “Circular References” to see the exact cell where a circular reference has been detected. Click on the cell number to be redirected to it.
   

This is the fastest and most reliable method to get straight to the source of a circular reference error message. Then, once you’ve arrived at the offending cell, you can take the necessary steps to adjust its formula and remove the circular reference.

What Are Circular Reference Cells and Why Are They a Problem?

In simple terms, a circular reference cell is one which contains a formula that either directly or indirectly refers back to the same cell. This usually creates impossible calculation loops that Excel can’t resolve, so it provides an error message.

The message lets the user know that circular references have been detected which may cause the formula to calculate incorrectly. And this is the main problem with circular references; they usually cause formulas to fail and leave users confused about why their spreadsheet isn’t working as intended.

An Example of a Circular Reference Cell

There are lots of different ways in which circular reference cells can appear. Here’s an elementary example to illustrate how and why this problem happens:

1. Imagine you wanted to use Excel to list a series of numbers and add them up. To begin, you could write out the list of numbers in cells A1 through A10.
   
2. Then, in cell A11, you might like to display the total. To do this, you would usually use the formula “=SUM(A1:A10)” to add everything in the line from cell A1 to cell A10.
   
3. However, you accidentally make a typo and enter a formula like “=SUM(A1:A11)”.
   

The circular error happens because cell A11 has been given a formula which involves itself. Effectively, it’s being asked to add a number from within its own cell, which doesn’t actually exist yet. This breaks the “SUM” calculation and will result in an error message.

How to Fix Circular Reference Cells

There are some nuanced cases in which circular reference cells may be desired. They can be used for certain advanced formulas. However, in the majority of cases, they occur because of user error and need to be fixed.

Unfortunately, Excel doesn’t simply give you a one-button fix for circular references. Instead, you’ll have to manually look through the cells and formulas to figure out what went wrong and make the necessary changes. There are two main methods you can use.

Removing and Rewriting the Formula

The most common way to fix circular reference cells is to use the steps above to find them. From there, you can take a look at the formula in the cell to try to find out what is wrong with it. In some cases, this will be easy. In others, with more complicated formulas and lots of information, it can be tricky.

Many users decide to just delete the formula and rewrite it. By deleting the formula entirely, you should get rid of the circular reference affecting that cell. Then, as you re-enter the formula, pay close attention to the cells it references and ensure that it doesn’t double back on itself anywhere.

Using Excel’s Trace Features

In those situations where a complicated formula is present and the circular reference is hard to spot, you can activate Excel’s “Trace Precedents” and “Trace Dependents” features. This will essentially show you the other cells affected by a cell’s formula, and it should make circular references easier to find and fix.

1. Click on the cell you want to trace the formula for.
   
2. Click on the Formulas tab at the top of the screen.
   
3. Find the Formula Auditing section of the menu and then, click to activate either Trace Precedents or Trace Dependents.
   

As the names imply, “Trace Precedents” will show you all the cells that essentially provide data for the formula in your chosen cell. Meanwhile, “Trace Dependents” will show cells later on in the calculation train that refer back to your chosen cell.

Arrows will appear from or to the cell, showing those that are affected by it or linked to it in some way. You can then make your way along the arrows, inspecting the relevant cells to find out what went wrong to make the circular reference.

Excel only lets you trace precedents and dependents for a single cell at a time. So, if you’ve got multiple circular reference errors, you’ll have to address them individually.

FAQs

Is there any use for circular reference cells?

There are some cases where advanced and experienced users can intentionally make and use them. They’re mainly used for iterative calculations. However, in order to use these calculations, you need to activate them in the Excel settings. Most users won’t need to use them and should simply resolve circular references when they appear.

What are the different types of circular references?

There are two main types of circular reference cells: direct and indirect. A direct circular reference is one where the formula in a cell directly refers back to the same cell. For example, if you put a formula in cell A10 which refers to cell A10 in some way, it’ll produce an error. Indirect references happen when a chain of formulas across different cells refers back to itself at some stage.

Solve Circular Reference Issues in Excel

It’s frustrating to see the “circular reference” error message appear when trying to use Excel. But, with the Error Checking tool, it’s easy to track those problems down and get them fixed. To stop them from appearing in the first place, double-check all formulas when typing them in.

Have you had issues with circular reference cells? Do you know any other handy tips for fixing these references when they appear? Let everyone know in the comments section.