Circular Reference

Circular reference occurs in Excel when you set up a formula that includes itself in the formula. A simple case of circular reference occurs when you create a SUM formula in D3 trying to add up D1 to D3. In this case, D3 is trying to add to itself.

Circular Reference Sample

It is easy to detect circular reference on your own worksheet because Excel will prompt you immediate the moment a circular reference is created.

Circular Reference Message

You can check on the last formula and remove the error instantly. The problem with most users is not reading the error message when it pops up and continues to work with a worksheet containing circular reference and causes some of the valid calculations to fail. Refer to the sample above and notice that the total is showing zero instead of 15.

What if you receive a workbook from somebody and in it, you are encountering the same type of reference error? How do you find out where it is?

In the status bar, Excel will display a notification to inform you that the workbook contains referencing error.

Circular Reference Notifcation

If you see the message showing "Circular Reference" with a cell reference, this means that the workbook contains circular reference AND it is not located in the worksheet you are currently seeing.

If the circular reference is in the worksheet you are currently viewing, you will see the message comes with the message "Circular reference" plus the cell address. Please see example below.

Circular Reference Guide


New! Comments

Have your say about what you just read! Leave me a comment in the box below.