How to find circular references in Excel?

What are circular references in Excel?

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

For example, a formula can directly reference the cell in which the formula is located:

The formula can also refer to itself indirectly by referencing other cells. Again referencing the cell where the formula is located:

In most cases, circular references are unnecessary and are created in error; a formula that references itself usually does not provide any Useful features. There are some situations where you might want to use a circular reference, but in general if you create a circular reference it's probably a bug.

How to Find Circular References in Excel

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

However, once you ignore this warning, it is not immediately obvious whether you have a circular reference in your spreadsheet. However, Excel makes it easy to track them.

To find circular references in Excel:

1. Select the Formulas tab.
   
2. Click the Down Arrow next to Error Checking.
   
3. Hover your mouse over Circular References and you will see the cell reference of the most recently entered circular reference.
   
4. Click the cell reference to go to the cell where the circular reference occurs.
5. Fix circular references as needed (follow the steps below for more help).
6. Repeat steps 1-5 until the cells no longer appear when hovering over Circular References.

How to Track Circular References in Excel

Using the above method, it is easy to navigate to the cells containing circular references. Once you get there, it's not always obvious what causes a circular reference. Excel has tools that can help you track down the cause of your problems.

To track circular references in Excel:

1. Follow the steps in the section above to navigate to the cell that contains the circular reference.
2. Click the "Formula" tab.
   
3. Click to display the formula . This makes your formula visible in the cell, not the result.
   
4. ClickTrack Precedent.
   
5. The arrow may or may not appear. If so, repeat step 4 until the arrow no longer appears.
   
6. Select Track Dependencies.
   
7. The arrow may or may not appear. If so, repeat step 6 until the arrow no longer appears.
8. You can now see all cells that reference or are referenced by the cell that contains the circular reference.

How to fix circular reference in Excel

Now that you have found the circular reference and its precedents and dependencies, you should be able to solve it question. Let's look at some examples.

In this example, we can see that the SUM formula in the last cell not only references the cell above it, but also itself.

This is a common problem when selecting cells for formulas. The circular reference can be fixed by changing the range to remove the cell where the formula is located.

Here's another example - we can see that although no cell directly references itself, each cell is its own precedent.

In other words, cell 1 gets its value from cell 2, which gets its value from cell 3, which gets its value from cell 1. Cell Cell 1 indirectly references itself .

An easy way to fix this problem is to replace one of the cells with a value instead of a formula. The circular reference is now fixed. To avoid this problem, it is often useful in Excel to replace the formula with the result.

Harness the Power of Excel Formulas

Knowing how to find circular references in Excel can allow you to solve the problem and get your formulas back on track.

Excel formulas allow you to do almost anything you want to do with your data. You can use them to truncate text in Excel, calculate unique values, or even extract data from another worksheet in Excel.

The above is the detailed content of How to find circular references in Excel?. For more information, please follow other related articles on the PHP Chinese website!