How-To

How to Find a Circular Reference in Excel

By Ben Stockton

Published June 9, 2022

Having trouble with circular references in Excel? You can usually fix the problem by changing your formula. Here’s how.

Excel functions allow you to create powerful formulas to manipulate your data.

What happens when a formula stops working or throws up an error? One of the most common is a circular reference error in Excel. This stops your formula from showing your data.

If you’re having trouble, here’s how to find a circular reference in Excel (and fix the problem).

What Is a Circular Reference in Excel?

As the name suggests, a circular reference in Excel is a formula that refers back to the cell that the formula is in.

For example, a formula could directly reference the cell that the formula is in:

The formula could also refer indirectly to itself, by referencing other cells which in turn reference the cell the formula is in:

In most cases, circular references are unwanted and created in error; a formula that refers to itself does not usually provide any useful function. There are a few cases where you may want to use a circular reference but on the whole, if you’ve created one, it’s probably a mistake.

How to Find a Circular Reference in Excel

Excel can help you to avoid circular references by providing you with a warning the first time you try to create one.

However, once you dismiss this warning, it’s not immediately obvious if you have a circular reference in your spreadsheet. Excel makes it easy to track them down, however.

To find a circular reference in Excel:

1. Select the Formulas tab.
   
2. Click the downwards arrow next to Error Checking.
   
3. Hover over Circular References and you will see the cell reference of the most recently entered circular reference.
   
4. Click on that cell reference to be taken to the cell where the circular reference appears.
5. Fix the circular reference, as required (follow the steps below for additional help).
6. Repeat steps 1-5 until no more cells appear when you hover over Circular References.

How to Trace a Circular Reference in Excel

Using the method above, it’s simple to navigate to the cell that contains the circular reference. Once you’re there, it’s not always immediately obvious what’s causing the circular reference. Excel has some tools that can help you trace the cause of the problem.

To trace a circular reference in Excel:

1. Follow the steps in the section above to navigate to the cell containing the circular reference.
2. Click on the Formulas tab.
   
3. Click on Show Formulas. This makes your formula visible in the cell, rather than the result.
   
4. Click on Trace Precedents.
   
5. An arrow may or may not appear. If one does, repeat step 4 until no more arrows appear.
   
6. Select Trace Dependents.
   
7. An arrow may or may not appear. If one does, repeat step 6 until no more arrows appear.
8. You can now see all the cells that reference, or are referenced by, the cell containing the circular reference.

How to Fix a Circular Reference in Excel

Now that you have found the circular reference, along with its precedents and dependents, you should be able to fix the problem. Let’s take a look at some examples.

In this example, we can see that the SUM formula in the final cell is referencing not only the cells above but also itself.

This is a common issue when selecting cells for a formula. The circular reference can be fixed by changing the range to remove the cell that the formula is in.

Here’s another example—we can see that, although no cell refers directly to itself, each cell turns out to be its own precedent.

In other words, cell 1 takes its value from cell 2, which takes its value from cell 3, which takes its value from cell 1. Cell 1 is indirectly referencing itself.

The simple way to fix this is to replace one of the cells with a value rather than a formula. The circular reference is now fixed. To avoid this issue, it’s often useful to replace a formula with a result in Excel.

Harness The Power of Excel Formulas

Knowing how to find a circular reference in Excel allows you to fix the problem and get your formula back on track.

Excel formulas allow you to do almost anything you could wish with your data. You can use them to truncate text in Excel, count unique values, and even pull data from another sheet in Excel.