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:
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:
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. Comment Name * Email *
Δ Save my name and email and send me emails as new comments are made to this post.
