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.
Have your say about what you just read! Leave me a comment in the box below.
Share this page:
Enjoy this page? Please pay it forward. Here’s how…
Would you prefer to share this page with others by linking to it?
- Click on the HTML link code below.
- Copy and paste it, adding a note of your own, into your blog, a Web page, forums, a blog comment, your Facebook account, or anywhere that someone would find this page valuable.
<a href=”http://www.advanced-excel.com/”>Advanced Excel – From a Business Perspective</a><a href=”http://www.advanced-excel.com/”>Advanced Excel – From a Business Perspective</a>
Microsoft® and Microsoft Excel® are registered trademarks of Microsoft Corporation.
advanced-excel.com is in no way associated with Microsoft