Our Recommendation for You Search your Query, You can find easily. for example search by book name or course name or any other which is related to your education


Circular References in Excel

How to solve the problem of circular references in Excel Workbook

What is a Circular Reference in Excel? I would layperson said it was an inadvertent error user when creating the formula, in which he entered the cell address where you write the formula. 
What does this mean? This means that we are in the formula used by the cell address or range of cells that contains the address of the cell in which the formula is entered.

What are the symptoms this error. When you start Excel files ie. The workbook that in the opening warning message appears on the error.

Warning that the formulas in the workbook contains circular references in Excel

If you like this warning appears when you open an Excel Workbook then click the OK button. If your computer is connected to an Internet network, you will open a window as shown below. Click on the link at the bottom and follow the instructions.

Instructions on how to solve the problem of circular references in an Excel Workbook

In this window, as you above is not satisfactory, follow the instructions in this tutorial below and solve the problem of circular references in Excel.

How to quickly find, locate, display and remove a Circular References in Excel

Circular reference can be in one or more cells on a worksheet or workbook. First you need to locate a cell that contains a circular reference (or multiple cells if there is more circular references).

In the picture below note in cell C3 the formula =A3+B3+C3
This formula contains a circular reference because it is in cell C3 and the same address is included in the formula.
When you type a formula that contains a circular reference and save the workbook then the next re-open Workbook will show us a warning message about circular references.

It's not a problem to find and display the cell that contains a circular reference to a worksheet that has entered some ten formulas. But how to find and display cells that contain circular references when on a Worksheet we have hundreds formulas.

How would you quickly find all cells that contain circular references follow the following instructions.
  • The first way is to look in the 'Status Bar' and immediately after the word 'Ready' you notice if there is shown a circular reference and address of the cell.
  • Another way to click on the Formulas tab => Formula Auditing group, click Error Checking => Circular References and you notice next to the address of the cell that contains a circular reference.

Once you locate the address of the cell that contains a circular reference formula and correct them, also  Save the Workbook.

How to quickly locate and display Excel cell that contains a circular reference

If you are still in the 'Status Bar' see the address of another cell means that it has a circular reference. Click on the respective cell and correct formula. So for the next cell until the 'Status Bar' is no longer visible Circular References: cell address. After correcting all the formulas, save the workbook and close it and reopen it. If you still see the warning about circular references then check all of the worksheets in the workbook.

How to locate all the cells in an Excel Workbook containing a circular reference

How to use a VBA macro to create a list of all circular references in Excel Workbook

This is a VBA macro that finds all circular references in the worksheet and displays them on a new worksheet. So the new worksheet sets a list of all circular references, ie. cell addresses and formulas in the respective cells.
Sub FindCircularReferences()
' Get source information.
sourcesheet = ActiveSheet.Name
' Get destination information.
destsheet = ActiveSheet.Name
destrange = ActiveCell.Address
' Return to source.
RowCount = 0
' Trap for error in "result", indicating no circular reference.
On Error GoTo notcircular

' Loop through every used cell in source.
For Each Item In ActiveSheet.UsedRange
' Check to see if cell contains a formula.
If Left(Item.Formula, 1) = "=" Then
' If cell intersects with precedents, cell has circular
' reference.

result = Intersect(ActiveSheet.Range(Item.Address), _

Worksheets(destsheet).Range(destrange).Offset(RowCount, _
0).Value = Item.Address(False, False)

Worksheets(destsheet).Range(destrange).Offset(RowCount, _
1).Value = "'" & Item.Formula

RowCount = RowCount + 1
' Skip to here if not circular.
skip item:
End If
Exit Sub

' If error in "result", go here.

' Skip cells that do not contain circular references.
Resume skipitem
End Sub

This VBA macro above, copy in the Module Workbooks
(Copy VBA from Web => Open Excel => Alt+F11 => Insert => New Module => Paste VBA).
Click on a sheet on which a circular reference check to be active => Alt+F8 => Select 'Sub FindCircularReferences' => Run. Notice that appeared new worksheet is positioned to the left of the worksheet being tested. On it is a list or a list of all cells with formulas that contain circular references. (VBA source; ozgrid.com)