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.
Circular Reference Warning
What are the symptoms this error. When you start Excel files ie. Workbook that in the opening warning message appears on the error.
If you like this Circular Reference 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.
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 Reference 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.
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 Reference then check all of the worksheets in the workbook.
How to Find all Circular References by VBA Macro
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.
' 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
result = Intersect(ActiveSheet.Range(Item.Address), _
0).Value = Item.Address(False, False)
1).Value = "'" & Item.Formula
RowCount = RowCount + 1
' Skip to here if not circular.
' If error in "result", go here.
' Skip cells that do not contain circular references.
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 of all cells with formulas that contain circular references. (VBA source; ozgrid.com).