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.

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

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.

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.

﻿

•

New! Comments

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