Circular References: Identify & Fix

Welcome to Excel Jet Consult blog post. In this tutorial, we will learn what is circular reference in Excel, how to trap cells that has Circular References, how to identify cell with Circular References and fix approximately

A Circular References is a formula that refers to its own cell directly or indirectly which result to an infinite loop. A workbook that contains one or more circular references, upon opening, will pop-up a warning sign indicating that there is a circular reference problem in one of the worksheets (if there are more than two worksheets). Executing OK to proceed does not solve the inherent problem which can greatly affect the authenticity and reliability of data for decision making.

Personally, during my Excel training and conference engagements, I’ve seen quite so many users who have problem of circular reference in their Excel data which they don’t know of.

In the caption below, upon opening the workbooks, we can see that Excel gave notification of inherent circular reference(s) and encourage us to either remove it or move the formulas to different cells. However, for this exercise, we are going to click OK

The first thing we want to do is to discover the cell or cells that has circular references and fix appropriately. To discover the cells with the problem:

In the Formulas Auditing group of the Formulas tab, click on the Error Checking drop-down

Select Circular Reference and there we can see that cell B26 has circular reference. See below

When we click on the \$B\$26, it moves us to cell B26. From the caption below, we discovered that cell B26 which is the output cell referenced itself as part of the input values cell reference.

To fix this problem, press F2 or double-click the cell and replace 26 to 25 which is the exact cell reference range

In the caption above, we can see that the problem has been fixed. To verifiy whether there may be more circular references in the data, run through the steps above.

Posted in Blog.