How to find cyclic links to Excel

Cyclic references are a formula in which one cell through a sequence of connections with other cells, ultimately refers to itself. In some cases, users are consciously used such a tool for computing. For example, this approach can help with modeling. But, in most cases, this situation is simply an error in the formula that the user allowed in disabilities or for other reasons. In this regard, to remove the error, you should immediately find the cyclical link itself. Let's see how it is done.

Detection of cyclic connections

If a cyclic link is present in the book, then when you start the file, the program in the dialog box will warn about this fact. So, with the definition of the very presence of such formula, there will be no problems. How to find a problem area on a sheet?

Method 1: Button on the ribbon

1. To find out, in which the range is such a formula, first of all, press the button as a white cross in a red square in the warning dialog box, thereby closing it.



2. Go to the "Formulas" tab. On the tape in the "Dependency Dependency" block there is a button "Checking errors". Click on the icon in the form of an inverted triangle next to this button. In the menu that appears, select "Cyclic Links". After the transition on this inscription in the form of the menu shows all the coordinates of the loop of cyclic nature in this book. When clicking on the coordinates of a particular cell, it becomes active on a sheet.



3. By studying the result, we establish the dependence and eliminate the cause of cyclicity if it is caused by an error.



4. After executing the necessary actions, go back to check the cyclic reference errors button. This time the corresponding menu item must be not active at all.

Method 2: Trace Arrow

There is another way to determine such unwanted dependencies.

1. In the dialog box reporting on the presence of cyclic links, press the "OK" button.



2. A trace arrow appears, which indicates the dependence of the data in one cell from another.

It should be noted that the second way is more visually visually visual, but at the same time does not always give a clear picture of cyclicity, unlike the first option, especially in complex formulas.

As you can see, find the cyclic link to Excel is quite simple, especially if you know the search algorithm. You can use one of two ways to find such dependencies. Somewhat more difficult to determine if this formula is needed in a really or it is just an error, as well as correct the erroneous link.