How to find circular reference in excel

How to find circular reference in excel

You’ve tried to enter some formula in your Excel sheet, but for some reason it’s not working. Instead, it tells you something about a circular reference. Is this how you ended up on this page? How to find circular reference in Excel quickly?

What is a circular reference in Excel?

Here is a very straight and concise definition of a circular reference provided by Microsoft:

When an Excel formula refers back to its own cell, either directly or indirectly, it creates a circular reference.

When you apply a formula in a cell, says Cell C1, and the formula refers back to its own cell directly or indirectly, says =Sum (A1:C1), circular reference happens.

When you reopen this workbook with circular reference again, it pops up a circular reference error message, which warns formulas contains a circular reference and may not calculate correctly. See the following screenshot:

how to find circular reference in excel
circular reference error

It tells what is the problem, however, it does not say where the error stays in. If you click the OK button, the warning error message will be closed, but it will pop up next time when you reopen the workbook; if you click the Help button, it will bring you to the Help document.

Why does Microsoft Excel give you a heads-up? Because Excel circular references can iterate indefinitely creating an endless loop, thus significantly slowing down the workbook calculations.

If you can’t find the error, click the Formulas tab, click the arrow next to Error Checking, point to Circular References, and then click the first cell listed in the sub-menu.

how to find circular reference in excel
Error Checking

Review the formula in the cell. If you can’t determine whether the cell is the cause of the circular reference, click the next cell in the Circular References sub-menu.

For further information please visit:

Maybe you are also looking for: