Find and Remove Circular References in Excel 2010

A Circular Reference is created in an excel worksheet when a Formula refers to a cell that contains the formula itself. A circular reference if present, can cause a lot of confusion to the users who use your workbook and it also sometimes create a significant impact on the system’s performance, because it will cause a formula to be recalculated repeatedly.

For example, consider that you have entered a formula =B1+B2 in cell B2. The excel program cannot calculate the result of this formula because, the formula present in cell B2 depends on the cell B2 itself. As the excel application cannot resolve this, it will display a Zero ‘0’ in cell B2. When the excel Application encounters a circular reference in any of the cells, then it will display a Zero inside that cell.

To Find a Circular Reference and remove it, do the following

1) When you just enter a formula in any of the cell in your worksheet, ensure that the formula does not refer to the cell in which it is contained. i.e., ensure that you do not enter a formula such as =SUM(A1+A12) in the cells A12 or A1.

2) Now, on the “Formulas” tab, in the ‘Formula Auditing’ category, click on arrow that is present next to the ‘Error Checking’ icon and then place your mouse on the ‘Circular References’ option in the drop down list and click on the first cell listed in the submenu.

Figure 1

3) The cells listed in the submenu indicate that, these particular cells contain a circular reference.

4) Now, review the formula in the cell that contains circular reference. If you are able to determine that, this cell is not the cause of Cell reference, then click on the next cell present in the Submenu.

NOTE: - the Status bar of the excel Application will display the text ‘Circular References combined with the cell address that contains circular reference. For example, if the application displays Circular References: A12 in the status bar, then it indicates that, the cell A12 contains a circular reference.
If there are Circular references present in any other worksheets other than the active worksheet, the excel application will display only ‘Circular References’ in the Status bar.

Figure 2

5) Continue to review the worksheet by selecting each cell on the submenu and try to remove the circular references present.

So, this is how you can find and remove Circular References in Excel 2010 Application.

Read Other Applications