Work with cyclic references in Excel

It is believed that cyclic references in Excele are an erroneous expression. Indeed, quite often this is exactly the case, but still not always. Sometimes they apply quite consciously. Let's find out what cyclic links are how to create them how to find already existing in the document how to work with them or how to remove it.

Use of cyclic references

First of all, find out what is a cyclic link. In essence, this expression, which, through the formulas in other cells, refers to itself. Also, it may be a link located in the leaf element to which it itself refers.

It should be noted that by default, the modern versions of Excel automatically block the process of performing a cyclical operation. This is due to the fact that such expressions in the overwhelming majority are erroneous, and the looping produces a constant process of recalculation and calculating, which creates an additional load on the system.

Creating a cyclic link

Now let's see how to create the simplest cyclical expression. This will be a link located in the same cell on which it refers.

1. We highlight the element of the A1 sheet and write the following expression in it:

   = A1.

   Next, click on the ENTER button on the keyboard.



2. After that, a cyclic expression warning dialog box appears. Click in it on the "OK" button.



3. Thus, we received a cyclical operation on a sheet in which the cell refers to itself.

A little complicating task and create a cyclic expression from several cells.

1. In any element of the sheet, write a number. Let it be a cell A1, and the number 5.



2. In another cell (B1) write the expression:

   = C1.



3. In the next element (C1) we will record such a formula:

   = A1.



4. After that, we return to the cell A1, in which the number is set 5. refer to it to the element B1:

   = B1.

   Click on the ENTER button.



5. Thus, the cycle closed, and we received a classic cyclic link. After the warning window is closed, we see that the program marked the cyclic bond with blue arrows on a sheet, which are called trace arrows.

We now turn to creating a cyclic expression on the example of the table. We have a table implementation table. It consists of four columns, which indicate the name of the goods, the number of products sold, the price and amount of revenue from the sale of the entire volume. The table in the last column already has formulas. They calculate revenue by multiplying the amount of the price.

1. To loosen the formula in the first line, we highlight the sheet element with the number of the first product (B2). Instead of static value (6), enter the formula there, which will consider the amount of goods by dividing the total amount (D2) on the price (C2):

   = D2 / C2

   Click on the ENTER button.



2. We have turned out the first cyclic link, the relationship in which is familiar with the trace arrow. But as we see, the result is erroneous and equal to zero, as it has already been said before, Excel blocks the execution of cyclic operations.



3. Copy the expression into all other cells of the column with the amount of products. To do this, set the cursor to the lower right angle of that element that already contains the formula. The cursor is converted to a cross, which is called to call a filling marker. Clear the left mouse button and pull this cross to the end of the table down.



4. As you can see, the expression was copied to all elements of the column. But, only one relationship is marked with the trace arrow. Note it for the future.

Search for cyclic links

As we have already seen higher, not in all cases the program marks the relationship of the cyclic reference with objects, even if it is on the sheet. Given the fact that in the overwhelming majority cyclic operations are harmful, they should be removed. But for this they must first find. How to do this if the expressions are not labeled with the arrow line? Let's deal with this task.

1. So, if you start an Excel file, you have an information window that it contains a cyclic link, it is desirable to find it. To do this, move to the "Formulas" tab. Click on the ribbon on the triangle, which is located to the right of the "Checking for errors" button, located in the "Dependency Dependency" tool block. A menu opens in which the cursor should be hosted to "Cyclic Links". After that, the following menu opens a list of addresses of sheet elements in which the program has discovered cyclic expressions.



2. When clicking on a specific address, the corresponding cell is selected on the sheet.

There is another way to find out where the cyclic link is located. The message about this problem and the address of the element containing a similar expression is located on the left side of the status string, which is at the bottom of the Excel window. True, in contrast to the previous version, the addresses of not all elements containing cyclic references will be displayed on the status bar, if there are many of them, but only one of them, which appeared before others.

In addition, if you are in a book containing a cyclic expression, not on the sheet where it is located, and on the other, then in this case, only a message about the presence of an error will be displayed in the status bar.

Lesson: how to find cyclic links to Excel

Correction of cyclic references

As mentioned above, in the overwhelming majority of cases, cyclic operations are evil, from which it should be easily eliminated. Therefore, it is natural that after the cyclic connection is detected, it is necessary to correct it to bring the formula to normal form.

In order to correct the cyclical dependence, you need to trace the entire interconnection of the cells. Even if the check indicated a specific cell, then the error may not be covered in it itself, but in another element of the chain of dependence.

1. In our case, despite the fact that the program correctly pointed to one of the cycle cells (D6), the actual error lies in another cell. Select the D6 element to find out which cells it pulls up the value. We look at the expression in the formula string. As we see, the value in this sheet element is formed by multiplying the contents of the B6 and C6 cells.



2. Go to the C6 cell. We highlight it and look at the formula string. As we see, it is the usual static value (1000), which is not a product calculating the formula. Therefore, it is safe to say that the specified element does not contain errors causing cyclic operations.



3. Go to the next cell (B6). After selection in the formula row, we see that it contains a calculated expression (= D6 / C6), which pulls the data from other table elements, in particular, from the D6 cell. Thus, the D6 cell refers to the data of the element B6 and vice versa, which causes loopedness.

   

   Here the relationship we calculated pretty quickly, but in reality there are cases when many cells are involved in the calculation process, and not three elements as we have. Then the search may take quite a long time, because it will have to study every cyclical element.

4. Now we need to understand which cell (B6 or D6) contains an error. Although, formally, it is not even an error, but simply excessive use of references that leads to looping. During the process of solving what cell should be edited, you need to apply logic. There is no clear algorithm for action. In each case, this logic will be its own.

   For example, if our table shared the total amount should be calculated by multiplying the number of actually sold goods at its price, then we can say that the link counting the amount of the total amount of sale is clearly superfluous. Therefore, we remove it and replace it with static importance.



5. Such an operation is carried out over all other cyclic expressions if they are on the sheet. After absolutely all cyclic links have been deleted from the book, the message about the presence of this problem should disappear from the status string.

   In addition, cyclic expressions were completely removed, you can find out using the error checking tool. Go to the "Formulas" tab and click the triangle already familiar to us to the right of the "Checking Errors" button in the instrument group "Depending formulas" . If the "Cyclic Links" item in the running menu is not active, then, it means that we removed all such objects from the document. In the opposite case, you will need to apply the removal procedure to the items that are listed, the same in the same way.

Permission of the execution of cyclic operations

In the previous part of the lesson, we told, mainly how to deal with cyclic references, or how to find them. But, earlier, the conversation was also about that in some cases they, on the contrary, can be useful and consciously used by the user. For example, quite often this method is used for iterative calculations when building economic models. But the trouble is that, regardless of whether you are consciously or unconsciously you use a cyclic expression, Excel will still block the operation on them by default, in order not to lead to excessive overload of the system. In this case, the question of forced disabling such a blocking becomes relevant. Let's see how to do it.

1. First of all, we move to the "File" tab of the Excel application.



2. Next, click on the "Parameters" item located on the left side of the window that opened.



3. The exile parameter window starts running. We need to go into the "Formulas" tab.



4. It is in the window that opens will be allowed to perform the execution of cyclic operations. Go to the right block of this window, where the Excel settings are directly. We will work with the "Computing Parameters" settings block, which is located at the top.

   To allow the use of cyclic expressions, you need to install a tick about the "Enable Iterative Calculations" parameter. In addition, in the same block, you can configure the limit number of iterations and the relative error. By default, their values ​​are 100 and 0.001, respectively. In most cases, these parameters do not need to be changed, although if necessary, it is possible to make changes to the specified fields. But here it is necessary to consider that too much iterations can lead to a serious load on the program and the system as a whole, especially if you work with a file in which many cyclic expressions are placed.

   So, we install a tick about the "Enable Iterative Calculations" parameter, and then that the new settings entered into force, click on the "OK" button, located at the bottom of the Excel parameters window.



5. After that, we automatically go to the sheet of the current book. As we see, in the cells in which cyclic formulas are located, now the values ​​are calculated correctly. The program does not block the calculations in them.

But still it is worth noting that the inclusion of cyclic operations should not be abused. Apply this feature follows only when the user is completely confident in its need. Unreasonable inclusion of cyclic operations can not only lead to excessive load on the system and slow down the calculations when working with a document, but the user may unintentionally make an erroneous cyclical expression, which by default it would be blocked by the program.

As we see, in the overwhelming majority of cases, cyclic references are a phenomenon with which you need to fight. For this, first of all, you should detect the cyclical relationship itself, then calculate the cell where the error is contained, and finally eliminate it by making the appropriate adjustments. But in some cases, cyclic operations can be useful when calculating and used by the user consciously. But even then, it is worth using their use with caution, correctly configuring Excel and knowing the measure in the addition of such references, which, when used in mass quantities, can slow down the operation of the system.