Circular Reference Warning in Microsoft Excel

If you are creating a formula which refers to the same cell that you are currently working on (or refers to it indirectly via another cell), you will get a circular reference warning.

Watch our video to find out What is a Circular Reference Warning in Microsoft Excel?

What Is a Circular Reference Warning and Why Do We Get It?

A circular reference is an iteration that in theory, could go on forever. If you are trying to create a formula by using the value in its own cell to calculate the answer, you will get this error. You can compare it to a dog chasing its own tail!

Take it in a more Excel related example. Click into cell A1 and type in the formula:
=A1+10

Since you are already in A1, it will keep trying to add 10 to the current value and go on adding 10 indefinitely. This is when the circular reference warning appears.

If you refer to the cell you are using in a formula directly or indirectly, it will continually try to calculate with each new value you get, so generally, this is not a good idea.

If you are editing a formula, you might inadvertently create a circular reference. Excel doesn't immediately tell you where this is located so even though the formula you are working on looks legitimate, it may indirectly refer back to itself if you were able to trace through the steps of the formula. If you do get this error and you don't know where it came from, you can locate the cell of the circular references within a sheet, by using the Formulas tab and the Error checking menu.



If you are editing a formula, you might inadvertently create a circular reference. Excel doesn't immediately tell you where this is located. You can locate circular references within a sheet, by using the Formulas tab.

You may also see the formula auditing arrows appear on the screen. These can be turned off by clicking the Remove Arrows button on the Formulas tab.

Creating a Circular Reference

There are occasions where you may want to create a circular reference. One scenario could be to create an automatic date/time stamp.

If you want to turn circular references on, open the Excel Options and enable iterative calculation from the Formulas category of tools.