Excel How to Delete Cyclic Links in - Everything about Excel

Excel How to Delete Cyclic Links in

Cyclic link to Excel how to remove

In Excel, you can attach a hyperlink to a file, site, cell or table. This feature is needed to switch to one or another document faster. Communication between cells are used in modeling and complex calculations. Observe how to add such objects how to edit them how to delete. Learn how to find a cyclic link to Excel, if it is there, why it is needed and how to use it.

Box Links to Cell

Link

  1. Highlight the desired cell. You can leave it empty or write something. Text you will always be able to remove.
  2. Click on it right-click.
  3. Row "Add Hyperlink" ("Hyperlink"). A similar button is in the "Paste" menu.
  4. In the "Tie" block, click "Web Page" ("Web Page").
  5. Click a little right to "Viewed Pages" and select the desired one.
  6. If you want to set the URL of the site, which did not visit before, write it in the "Address" field ("Asdress").
  7. In the "Replacement Text" item, specify what the hyperlink should be replaced. This phrase will appear in the cell. When clicking on it, a web page will open. You can leave the field empty.
  8. The "Script" button ("Screenip") is used to create Excel pop-ups.

Link to file.

  1. Right-click on the right mouse button - add a hyperlink.
  2. In the "Tie" section, check "File".
  3. A folder is displayed in which the document is now.
  4. The "Recent Files" button will open the data list that you used recently. They cannot be removed from this section.
  5. If there is no what you are looking for, set the path yourself. To do this, click "Current Folder". Under the replacing text there will be a drop-down list. In it, select a directory.
  6. You can set the path with the "Find" button. It is slightly right than the drop-down list.

In Excel, you can refer to another non-existent document and immediately create it.

When you click on the cell to which the data on the computer is tied, the Excel security system will display a warning. It reports that you open a third-party file, and it can be unreliable. This is a standard alert. If you are confident in the data you work with, in the dialog box to "continue?" Answer "Yes."

You can associate a cell with an e-mail. Then, when you click on it, your email client will open, and the address will already be introduced in the field.

Link to another cell

  1. In the "Tie" section, click "Place in this Document").
  2. In the "Select Place in Document" area, mark the desired sheet.
  3. In the "Address" enter the name of the cell. It consists of a letter indicating the column, and the number of the string. To find out this name, click on the desired cage. It will appear in a special field on the left above.
  4. Click OK.

To make the transition directly to several cells at the same time, you need to create a range.

After that, move the range in the same way as the cage.

Here's how to make a hyperlink in Excel to another table:

So you can make a connection not with the entire file, but with a specific place in the file.

Cyclic Links

To begin with, such objects need to be found.

These objects are used to model tasks, calculations, complex formulas. Calculations in the same cell will affect the other, and that, in turn, on the third. But in some operations it may cause an error. To fix it, just get rid of one of the formulas in the cycle - the circle will open.

Here's how to remove the hyperlink in Excel, leaving the text, edit it, or do not erase:

How to change the color and remove the emphasis?

  1. Highlight the cell.
  2. On the toolbar in the "Menu" tab (in Office 2007 "Home"), find the letter "h" with a screw at the bottom. She is responsible for emphasising.
  3. On the same panel you need to find a button with an icon in the form of a symbol "A" with a strip under it.

Click on the black arrow nearby. The palette opens. Select Font Color. In Excel, you can insert a hyperlink to go to a web page, open some document or redirect to other cells. Such objects are used in complex calculations and objectives associated with financial modeling.

Source: Officeassist.ru.

In the Microsoft Excel spreadsheet, there may be formulas located in one cell to calculate the information in another cell. These relationships are called links. When the cell refers to itself or another cell that refers to the original cell, the formulas cannot be completed, since they require a result that depends on the source cell. This event is called a cyclic reference, and when you have a large spreadsheet with thousands of formulas, one circular link may lead to the fact that the entire sheet will not be designed properly.

Understanding Excel Links

When you use Microsoft Excel, usually in one cell there is a formula that refers to another cell to use the data of this cell in the calculations. Usually, the lines and the number of the column number are used to refer to the cell, for example, A5 or B10.

When the data in the cell referenced is updated, the formulas that refer to them will also be updated. There are several layers of references in the complex spreadsheet, and the results of calculating the same formula are transmitted to another.

Eliminate the circular link to Excel

If the formula refers directly to its own cell or through a chain of links to other cells, ultimately refers to its own cell, you will receive a warning about the cyclic link from Excel. This is because the formula cannot be calculated in the usual way, since it may affect its own value when it is updated.

Often it is a sign of typo or logical error, and you want to find what creates a cyclic link, and eliminate it.

Open the spreadsheet

Open the Microsoft Excel spreadsheet, which contains a circular link.

Use the formula menus

Click the Formulas tab in the Ribbon menu at the top of the Excel window. Click on the small arrow next to the "Error Check" button in this area. Move the mouse on the "circular links", and the last entered circular link will appear. Click this link to go to this cell in the spreadsheet.

Look for a circular link

Check the formula that appears on the formula panel slightly higher than the spreadsheet, for any obvious cyclic links, for example, if the formula is trying to refer to the cell in which it is at the moment. In smaller spreadsheets, you can even check each link to the cell manually to see if you can find a circular link. If you can not define it, you will need to track the links in the spreadsheet.

Trace of precedents with Excel

Click the Trace Preceders button in the "Audit formula" area on the Formula tab in the Tape menu. This will create an arrow in the spreadsheet from each cell specified in the specified cell to the specified cell. Press the "Trace Dependents" button, which will create an arrow from the specified cell for each cell that refers to the specified cell.

Follow the reference chain

Double-click each line that comes out of the cell, with your circular link to go to the cell on the other end of the string. If the cell, to which you go, has as the beginning of the arrow, which looks like a circle and the end of the arrow, which looks like the arrow tip, you found a circular link. Change this cell or link to this cell in the source cell to correct the cyclic link.

Find more circles as needed

Press the drop-down list arrow next to the "error check" again to see if another circular link appears. If so, repeat this process to clean it, and continue until cyclic references appear.

Itective calculation Excel

In some cases, you may want to use cyclic links to convey the result of the calculation back to the calculation. This is called an iterative calculation in Excel. This is useful for certain formulas and algorithms where you want to execute iteration until a specific result is found.

To enable iterative calculation in Excel, go to the File tab, then "Parameters" and "Formulas". In the "Calculation Parameters" section, select the Enable Itective Calculation check box. Enter the maximum number of times you want Excel to recalculate the formula in the Maximum number of iterations field and the maximum number of changes you allow, before Excel, you will need to recalculate again in the Maximum Change section.

The more iterations you allow, and the smaller the number of changes that you admit before you require the recalculation, the more accurate the results you will receive. Make sure you really want to use an iterative formula, and not just delete cyclic links before going through this path.

Source: RU.HalvesCoinSMoney.com

How to delete or resolve a cyclic link

You entered the formula, but it does not work. Instead, you get this error message "Cyclic Link". Millions of people have the same problem, and this is due to the fact that the formula is trying to calculate itself, and you have a function called iterative calculation. Here's what it looks like:

Formula = D1 + D2 + D3 does not work because it is located in the D3 cell and refers to itself. To eliminate this problem, you can move the formula to another cell. Press the Ctrl + x keys to cut the formula, select another cell and press the Ctrl + V keys to insert it.

Another common error is related to the use of functions that include references to themselves, for example, the F3 cell may contain a formula = sums (A3: F3). Example:

You can also try one of the ways described below.

If you have just entered the formula, start with this cell and check if you do not refer to the cell itself. For example, a cell A3 may comprise a formula = (A1 + A2) / A3. Formulas, for example = A1 + 1 (in cell A1), also cause cyclical reference errors.

Check out the presence of indirect links. They occur when the formula located in the cell A1 uses another formula in the cell B1, which again refers to the A1 cell. If it confuses you, imagine what is happening with Excel.

If you cannot find an error, on the Formula tab, click the arrow next to the error check button, select Cyclic Links and click the first cell to the submenu.

Check the formula in the cell. If you cannot determine if this cell is the cause of a cyclic link, select the following cell to the submenu.

Continue to find and correct cyclic references in the book, repeating actions 1-3 until the message "Cyclic Links" will disappear from the status string.

The status bar in the lower left corner displays the message cyclic links and cell address with one of them.

If there are cyclic references on other sheets, except for active, the message "Cyclic Links" is displayed in the status bar without cell addresses.

You can move between the cells in the cyclic link, double-click the trace arrow. The arrow indicates a cell that affects the value of the currently selected cell. To display the trace arrow, select Formulas, and then influencing cells or dependent.

Cyclic Link Warning

When Excel first finds a cyclic link, a warning message is displayed. Click OK or close the Message window.

When you close a message, the cell will be displayed or zero or the last calculated value. And now, probably, they say: "Hang the last computed value?" Yes. In some cases, it is possible to successfully execute the formula before performing the calculation. For example, a formula that uses the function If, can work until the user enters the argument (part of the data that should be performed correctly), which leads to the fact that the formula will calculate itself. In this case, Excel saves the value from the last successful calculation.

If there is a suspicion that the cyclic link is contained in a cell that does not return a value of 0, try such a solution:

Click the formula in the formula string and press the Enter key.

Attention! In many cases, when creating additional formulas with cyclic references, the warning message in the Excel application is no longer displayed. Some are listed below, but not all situations in which the warning will appear.

The user creates the first instance of the cyclic link in any open book.

The user removes all cyclic references in all open books, after which it creates a new cyclic reference.

The user closes all the books, creates a new one and introduces a formula with a cyclic reference.

The user opens a book containing a cyclic link.

In the absence of other open books, the user opens a book and creates a cyclic reference in it.

Iterative calculations

Sometimes you may need to use cyclic references, as they lead to iterations of functions - repeated until a certain numerical condition is performed. It can slow down the computer, so iterative calculations are usually disabled in Excel.

If you are not familiar with iterative calculations, you probably do not want to leave active cyclic links. If you need them, it is necessary to decide how many times the calculation of the formula can be repeated. If you enable iterative calculations without changing the limit number of iterations and relative error, the Excel application will stop calculating after 100 iterations or after the change in all values ​​in the cyclic reference with each iteration will be less than 0.001 (depending on which of these conditions will be executed earlier). However, you can specify the limit number of iterations and the relative error.

If you work in Excel 2010 or later, sequentially select File> Parameters> Formulas. If you work in Excel for Mac, open the Excel menu, select Settings and click the Calculation item.

If you use Excel 2007, click the Microsoft Office button. , Click Excely Settings Select the formula category.

In the Computing Parameters section, select the Enable Iterative Calculations check box. On the MAC computer, click Use iterative calculation.

In the Limit number of iterations field, enter the number of iterations for execution when processing formulas. The greater the limit number of iterations, the more time it is necessary to recalculate the sheet.

In the Relative Error field, enter the smallest value before it is to continue iterations. This is the smallest increment in any calculated value. The smaller the number, the more accurate the result and the longer the excel will be required for calculations.

Iterative calculation may have three outcome:

The solution converges, which means obtaining a reliable end result. This is the most desirable outcome.

The solution is divided, i.e., with each subsequent iteration, the difference between the current and previous results increases.

The solution switches between two values. For example, after the first iteration, the result is 1, after the next iteration, the result is 10, after the next iteration, the result is 1, etc., etc.

additional information

You can always ask a question by Excel Tech Community specialist, ask for help in the Answers Community Community, as well as offer a new feature or improvement on the Excel User Voice website.

additional information

Note: This page is translated automatically, so its text may contain inaccuracies and grammatical errors. It is important for us that this article be useful to you. Was the information useful? For convenience, also give a link to the original (in English).

Source: Support.Office.com.

Cyclic Links to Excel

25.10.2013 Gregory Tsapko Useful Tips

Among Excel users is widespread the view that Cyclic Link B. Excel It is a type of error, and it must be easily getting rid of.

Meanwhile, it is cyclic references to Excel that can ease us solve some practical economic tasks and financial modeling.

This note will just be called upon to answer the question: is there always cyclic links - is it bad? And how to work with them to work to maximize their computational potential.

To begin with, we will understand what is Cyclic references B. Excel 2010. .

Cyclic references occur when the formula, in any cell through other cells, refers to itself.

For example, cell C4 = E 7, E7 = C11, C11 = C4. As a result, C4 refers to C4.

Visually it looks like this:

In practice, it is often not such a primitive connection, and more complicated when the results of calculating the same formula, sometimes very intricate, affect the result of calculating another formula, which in turn affects the results of the first. There is a cyclic link.

Cyclic Link Warning

The appearance of cyclic links is very easy to determine. When they occur or presence in the already created book, Excel immediately appears Cyclic Link Warning , by and large and describes the essence of the phenomenon.

When you click on the OK button, the message will be closed, and in the cell containing a cyclic reference in most cases to appear 0.

Warning, as a rule, appears at the initial creation of a cyclic reference, or opening a book containing cyclic references. If a warning is accepted, then with the further occurrence of cyclic references, it may not appear.

How to find a cyclic link

Cyclic references to Excel can be created deliberately, to solve certain financial modeling tasks, and may occur randomly, in the form of technical errors and errors in the logic of the model constructing.

In the first case, we know about their availability, as they themselves have previously created, and we know why we need them.

In the second case, we can not know where they are, for example, when opening a foreign file and the appearance of a report on the presence of cyclic references.

Find a cyclic link You can in several ways. For example, purely visually formulas and cells participating in the formation of cyclic references in Excel are marked with blue arrows, as shown in the first figure.

If a cyclic link is one on the sheet, a message on the presence of cyclic links with the cell address will be displayed in the status bar.

If there are also cyclic references on other sheets except active, the message will be displayed without specifying the cell.

If or on an active sheet more than one, then a message will be displayed with an indication of the cell where the cyclic link appears for the first time, after it is removed, a cell containing the following cyclic reference, etc.

You can also find a cyclic link using the error search tool.

On the Formulas tab in the Formula Decision Group, select Error Search item and in the item drop-down list Cyclic Links .

You will see the address of the cell with the first cyclic reference. After its adjustment or removal - with the second, etc.

Now, after we found out how Find and remove the cyclic link , Consider situations when you do not need to do this. That is, when the cyclic link in Excel brings us a certain benefit.

Use of cyclic references

Consider an example of using cyclic references in financial modeling. It will help us understand the overall mechanism of iterative calculations and give impetus for further creativity.

In financial modeling, it is not often, but still a situation arises when we need to calculate the cost level of the budget depending on the financial result, which this article affects. This may be, for example, the cost of expenses for additional bonuses of sales department, depending on sales profits.

After the introduction of all the formulas, we have a cyclic link:

However, the situation is not hopeless. It is enough for us to change some Excel parameters and the calculation will be carried out correctly.

Another situation when cyclic references can be claimed - this is a method of mutual or inverse distributions of indirect costs between non-production units.

Such distributions are made using a system of linear equations, and can be implemented in Excel using cyclic references.

On the subject of cost distribution methods in the near future, a separate article appears.

So far, we are interested in the possibility of such calculations.

Iterative calculations

In order for the correct calculation to be possible, we must enable iterative calculations in Excel parameters.

Iterative calculations are the calculations repeated many times until the result is achieved corresponding to the specified conditions (the condition of the accuracy or condition of the number of iterations carried out).

You can enable iterative calculations through the File tab → Section parameters → Formula clause. Install the "Enable Iterative Calculations" checkbox.

As a rule, the default limit number of iterations and the relative error is sufficient for our computational purposes.

It should be borne in mind that too much computing can significantly download the system and reduce performance.

Also, speaking of iterative calculations, it should be noted that three options for the development of events are possible.

The solution converges, which means obtaining a reliable end result.

The solution is divided, i.e., with each subsequent iteration, the difference between the current and previous results increases.

The solution fluctuates between two values, for example, after the first iteration, the value is 1, after the second - value 10, after the third - again 1, etc.

Source: Excel-Training.ru.

Work with cyclic references in Excel

Many believe that cyclic references in Excel - errors, but not always so. They can help solve some tasks, so sometimes used intend. Let's analyze in detail what is a cyclic link, how to remove cyclic references to Excel and so on.

What is a cyclic link

The cyclic link is a reference to the referring cell itself. This may not be alone, but a whole group of links that refer to themselves, forming a vicious circle. In the new versions of the application, cyclic links are blocked, and if they are in the document you open, a corresponding message will appear on the screen.

How to find a cyclic link

Most often, the program finds them itself, and since the links of this type are harmful, they must be eliminated. What to do if the program could not find them itself, that is, did not mark the expressions with the arrow lines? In this case:

  • On the top of the program you need to go to the "Formulas" section. Next, open the menu "Depending formulas", and there "Checking errors". A menu will appear where you need to select "Cyclic Links". The following menu will open, where the elements found by the address containing cyclic references will be displayed.

  1. If you click on any of the addresses, an appropriate cell on the sheet will be separated.

Eliminate cyclic links

After the reference is detected, the question arises how to remove the cyclic link to Microsoft Excel. To correctness, it is necessary to view the dependence between the cells. Suppose you have been introduced any formula, but in the end it did not want to work, and the program issues an error pointing to the presence of a cyclic reference.

For example, this formula is not workable because it is in the D3 cell and refers to itself. It must be cut from this cell, selecting the formula in the formula row and pressing Ctrl + x, after which insert in another cell, highlighting it and pressing Ctrl + V.

This is only a simple example, for more complex calculations, the check may be delayed for a long time.

Interative calculations

Many, probably heard such a thing as iteration and want to know, iterative calculations in Excel, which is. These are calculations that continue to be performed until the result is obtained corresponding to the specified conditions.

To enable the function, open the program parameters and in the Formulas section to activate "Enable Iterative Calculations".

Conclusion

Now you know what to do if the calculated expression has looked around. Sometimes you need to apply logic and though to think to eliminate cyclic links.

Source: Tehno-bum.ru.

Excel How to Delete Cyclic Links in

When I open the file with the data writes "Warning of the cyclic link". how to remove it ?? Tired of each time it comes out!

And when scattering the document writes to save the file? I did not do anything just opened the file when screaming, it sees to save the file.

Where I can not find the answer thanks_)

When I open the file with the data writes "Warning of the cyclic link". how to remove it ?? Tired of each time it comes out!

And when scattering the document writes to save the file? I did not do anything just opened the file when screaming, it sees to save the file.

Where I can not find the answer. Thank you.) Art22

Message When I open the file with the data writes "Warning about the cyclic link". how to remove it ?? Tired of each time it comes out!

And when scattering the document writes to save the file? I did not do anything just opened the file when screaming, it sees to save the file.

Where I can not find the answer. Thank you.) Author - ART22. Date added - 03/22/2018 в 20:36

Gling. Date: Thursday, 03/22/2018, 22:27 | Message No. 2.
Poison-41001506838083

Reply

ART22. Date: Friday, 03/23/2018, 10:10 | Message number 3.

Thank you so much!) It turns out on 2 -3 pages, I kept some numbers. Because of them it was.

Through the program, it retains the numbers in the Ektel, and such warnings are walked. In the settings, I disconnected them, but still these errors can exist as it can be done so that there is still a number or text in the cell.

Thank you so much!) It turns out on 2 -3 pages, I kept some numbers. Because of them it was.

Through the program, it retains the numbers in the Ektel, and such warnings are walked. In the settings, I disconnected them, but still these errors can exist as it can be done so that there is still a number or text in the cell. ART22.

Message Thank you so much!) It turns out on 2 -3 pages, I kept some numbers. Because of them it was.

Through the program, it retains the numbers in the Ektel, and such warnings are walked. In the settings, I disconnected them, but still these errors can exist as it can be done so that there is still a number or text in the cell. Author - ART22. Date added - 03/23/2018 в 10:10

Source: www.excelworld.ru.

Cyclic Link to Microsoft 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. Select the leaf element A1. And write in it the following expression:

    = A1.

    Next we click on the button ENTER on keyboard.

  2. Creating the simplest cyclic link in Microsoft Excel

  3. After that, a cyclic expression warning dialog box appears. Click it on the button Ok .
  4. Dialog box warning about the cyclic link in Microsoft Excel

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

Cell refers to Microsoft Excel

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 number 5.
  2. Number 5 in cell in Microsoft Excel

  3. To another cell ( B1. ) write the expression:

    = C1.

  4. Link in the cell in Microsoft Excel

  5. In the next element ( C1. ) We will write such a formula:

    = A1.

  6. One cell refers to another in Microsoft Excel

  7. After that we return to the cell A1. in which the number is set 5. Refer to it on the element B1. :

    = B1.

    Click on the button ENTER .

  8. INSTALLATION LINKS IN THE CELEX IN Microsoft Excel

  9. 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.

Marking Cyclic Communication in Microsoft Excel

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.

Revenue Calculation in Table in Microsoft Excel

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

    = D2 / C2

    Click on the button ENTER .

  2. Insert a cyclic link in a table in Microsoft Excel

  3. 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.
  4. Cyclic Link in Table in Microsoft Excel

  5. 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.
  6. Filling marker in Microsoft Excel

  7. 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.

Cyclic links are copied in a table in Microsoft Excel

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 tab "Formulas" . Click on the ribbon on the triangle, which is located on the right of the button "Checking for errors" located in the tool block "Depending formulas" . A menu opens in which the cursor should be hosted. "Cyclic Links" . After that, the following menu opens a list of addresses of sheet elements in which the program has discovered cyclic expressions.
  2. Search for cyclic links in Microsoft Excel

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

Switch to a cell with a cyclic link in Microsoft Excel

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.

Cyclic link message on the status panel in Microsoft Excel

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.

Cyclic Link on another sheet in Microsoft Excel

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 indicated one of the cycle cells ( D6. ), Real error lies in another cell. Select element D6. To find out which cells it pulls the value. We look at the expression in the formula string. As you can see, the value in this element of the sheet is formed by multiplying the contents of the cells. B6. и C6. .
  2. Expression in the program in Microsoft Excel

  3. Go to the cell C6. . We highlight it and look at the formula string. As you can see, this is normal static value ( 1000. ), which is not a product calculation of the formula. Therefore, it is safe to say that the specified element does not contain errors causing cyclic operations.
  4. Static importance in Microsoft Excel

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

    Cyclic Link in Table Cell in Microsoft Excel

    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.

  6. Now we need to understand which cell ( B6. or D6. ) It 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.

  7. The link is replaced with the values ​​in Microsoft Excel

  8. 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 tab "Formulas" and click already familiar to us triangle to the right of the button "Checking for errors" In the instrument group "Depending formulas" . If in the starting menu item "Cyclic Links" It will not be active, then, we deleted 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.

Cyclic Links in the book NO MICROSOFT Excel

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.

Locking cyclic links in Microsoft Excel

  1. First of all, we move to the tab "File" Excel applications.
  2. Move to the File tab in Microsoft Excel

  3. Next click on item "Options" located on the left side of the window that opened.
  4. Go to the parameter window in Microsoft Excel

  5. The exile parameter window starts running. We need to go to the tab "Formulas" .
  6. Transition to the formula tab in Microsoft Excel

  7. 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 settings block "Computing parameters" which is located at the very top.

    To allow the use of cyclic expressions, you need to install a tick near the parameter "Enable iterative calculations" . 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, install a tick near the parameter "Enable iterative calculations" and then so that new settings entered into force, click on the button Ok , placed at the bottom of the Excel parameters window.

  8. Enable iterative calculations in Microsoft Excel

  9. 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.

Cyclic formulas displays correct values ​​in Microsoft Excel

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.

CloseWe are glad that you could help you solve the problem. CloseDescribe what you did not work.

Our specialists will try to answer as quickly as possible.

Will this article help you?

WELL NO

Excel How to Delete Cyclic Links in

Work with cyclic references in Excel

Many believe that cyclic references in Excel - errors, but not always so. They can help solve some tasks, so sometimes used intend. Let's analyze in detail what is a cyclic link, how to remove cyclic references to Excel and so on.

What is a cyclic link

The cyclic link is a reference to the referring cell itself. This may not be alone, but a whole group of links that refer to themselves, forming a vicious circle. In the new versions of the application, cyclic links are blocked, and if they are in the document you open, a corresponding message will appear on the screen.

How to find a cyclic link

Most often, the program finds them itself, and since the links of this type are harmful, they must be eliminated. What to do if the program could not find them itself, that is, did not mark the expressions with the arrow lines? In this case:

  • On the top of the program you need to go to the "Formulas" section. Next, open the menu "Depending formulas", and there "Checking errors". A menu will appear where you need to select "Cyclic Links". The following menu will open, where the elements found by the address containing cyclic references will be displayed.

  1. If you click on any of the addresses, an appropriate cell on the sheet will be separated.

Eliminate cyclic links

After the reference is detected, the question arises how to remove the cyclic link to Microsoft Excel. To correctness, it is necessary to view the dependence between the cells. Suppose you have been introduced any formula, but in the end it did not want to work, and the program issues an error pointing to the presence of a cyclic reference.

For example, this formula is not workable because it is in the D3 cell and refers to itself. It must be cut from this cell, selecting the formula in the formula row and pressing Ctrl + x, after which insert in another cell, highlighting it and pressing Ctrl + V.

This is only a simple example, for more complex calculations, the check may be delayed for a long time.

Interative calculations

Many, probably heard such a thing as iteration and want to know, iterative calculations in Excel, which is. These are calculations that continue to be performed until the result is obtained corresponding to the specified conditions.

To enable the function, open the program parameters and in the Formulas section to activate "Enable Iterative Calculations".

Conclusion

Now you know what to do if the calculated expression has looked around. Sometimes you need to apply logic and though to think to eliminate cyclic links.

How to find and delete a cyclic link to Excel

Since Microsoft Office Excel always uses all sorts of formulas from simple autosums to complex computing. Then, respectively, there are many problems with them. Especially if these formulas wrote not experienced users. One of the most frequent errors is associated with cyclic references. It sounds like this. When the document is opening, a cyclic reference warning window appears.

One or more formulas contain a cyclic link and can be calculated incorrectly. From this message, most often the user does not understand anything, on this I decided to write a simple language what it is and how to solve this problem. You can certainly ignore this message and open the document. But in the future, when calculating in the tables, you can encounter errors. Let's look at what is a cyclic link and how to find it in Excel.

Error Some formulas contain cyclic links

Let's look at the example what is a cyclic link. Create a document Let's make a small sign not even a sign and simply write a few digits and calculate the autosumum.

Now I will change the formula slightly and turn on the cell in which the result should be. And we will see the warning about the cyclic link.

Now the amount is calculated not correct. And if you try to enable this cell into another formula, the result of these calculations will not be correct. For example, add two more such columns and consider them the amount. Now let's try to calculate the sum of all three columns in the end we get 0.

In our example, everything is simple and obviously, but if you have discovered a large table in which a bunch of formulas and one is considered not correct, then it will be necessary to check the meaning of all cells participating in the formula for cyclic references.

In general, if you just say, the cyclic link is obtained if the cell is involved in the formula in which the result should be. Actually find a cyclic link just just important to know what it is.

How to delete or resolve a cyclic link

You entered the formula, but it does not work. Instead, you get this error message "Cyclic Link". Millions of people have the same problem, and this is due to the fact that the formula is trying to calculate itself, and you have a function called iterative calculation. Here's what it looks like:

Formula = D1 + D2 + D3 does not work because it is located in the D3 cell and refers to itself. To eliminate this problem, you can move the formula to another cell. Press keys Ctrl + X. to cut the formula, select another cell and press keys Ctrl + V. To insert it.

Another common error is related to the use of functions that include references to themselves, for example, the F3 cell may contain a formula = sums (A3: F3). Example:

You can also try one of the ways described below.

If you have just entered the formula, start with this cell and check if you do not refer to the cell itself. For example, a cell A3 may comprise a formula = (A1 + A2) / A3. Formulas, for example = A1 + 1 (in cell A1), also cause cyclical reference errors.

Check out the presence of indirect links. They occur when the formula located in the cell A1 uses another formula in the cell B1, which again refers to the A1 cell. If it confuses you, imagine what is happening with Excel.

If you find an error fails, on the tab Formulas Click the arrow next to the button Check error , Select Cyclic Links And click the first cell to the submenu.

Check the formula in the cell. If you fail to determine if this cell cause a cyclic link, select to the submenu Cyclic Links Next cell.

Continue to find and correct cyclic references in the book, repeating actions 1-3 until the message "Cyclic links" will disappear from the status bar.

In the status bar in the lower left corner the message is displayed. Cyclic Links and the cell address with one of them.

If there are cyclic references on other sheets, except for active, the message "Cyclic links" is displayed in the status bar without addresses of cells.

You can move between the cells in the cyclic link, double-click the trace arrow. The arrow indicates a cell that affects the value of the currently selected cell. To display the trace arrow, select Formulas , and then - Influencing cells or dependent Cells.

Cyclic Link Warning

When Excel first finds a cyclic link, a warning message is displayed. Press the button OK Or close the message window.

When you close a message, the cell will be displayed or zero or the last calculated value. And now, probably, they say: "Hang the last computed value?" Yes. In some cases, it is possible to successfully execute the formula before performing the calculation. For example, a formula that uses the function If, can work until the user enters the argument (part of the data that should be performed correctly), which leads to the fact that the formula will calculate itself. In this case, Excel saves the value from the last successful calculation.

If there is a suspicion that the cyclic link is contained in a cell that does not return a value of 0, try such a solution:

Click the formula in the formula string and press the Enter key.

Attention! In many cases, when creating additional formulas with cyclic references, the warning message in the Excel application is no longer displayed. Some are listed below, but not all situations in which the warning will appear.

The user creates the first instance of the cyclic link in any open book.

The user removes all cyclic references in all open books, after which it creates a new cyclic reference.

The user closes all the books, creates a new one and introduces a formula with a cyclic reference.

The user opens a book containing a cyclic link.

In the absence of other open books, the user opens a book and creates a cyclic reference in it.

Iterative calculations

Sometimes you may need to use cyclic references, as they lead to iterations of functions - repeated until a certain numerical condition is performed. It can slow down the computer, so iterative calculations are usually disabled in Excel.

If you are not familiar with iterative calculations, you probably do not want to leave active cyclic links. If you need them, it is necessary to decide how many times the calculation of the formula can be repeated. If you enable iterative calculations without changing the limit number of iterations and relative error, the Excel application will stop calculating after 100 iterations or after the change in all values ​​in the cyclic reference with each iteration will be less than 0.001 (depending on which of these conditions will be executed earlier). However, you can specify the limit number of iterations and the relative error.

If you work in Excel 2010 or later, select items. File > Options > Formulas . If you work in Excel for Mac, open the menu Excel , Select Settings and click Element Calculation .

If you use Excel 2007, click Microsoft Office. Click Excel Settings And select a category Formulas .

In section Parameters of calculations Check the box Enable iterative calculations . On Mac Computer Click Use iterative calculation .

In field Maximum number of iterations Enter the number of iterations for execution when processing formulas. The greater the limit number of iterations, the more time it is necessary to recalculate the sheet.

In field Relative error Enter the smallest value to which iterations should be reached. This is the smallest increment in any calculated value. The smaller the number, the more accurate the result and the longer the excel will be required for calculations.

Iterative calculation may have three outcome:

The solution converges, which means obtaining a reliable end result. This is the most desirable outcome.

The solution is divided, i.e., with each subsequent iteration, the difference between the current and previous results increases.

The solution switches between two values. For example, after the first iteration, the result is 1, after the next iteration, the result is 10, after the next iteration, the result is 1, etc., etc.

additional information

You can always ask a question by Excel Tech Community specialist, ask for help in the Answers Community Community, as well as offer a new feature or improvement on the Excel User Voice website.

additional information

Note: This page is automatically translated, so its text may contain inaccuracies and grammatical errors. It is important for us that this article be useful to you. Was the information useful? For convenience, also give a link to the original (in English).

How to find and remove cyclic links to Excel?

Cyclic references to Excel - formula errors. This means that the calculation looked at some cell, which refers to itself. The calculation cycle is repeated infinitely and cannot give the correct result. Let's see how the editor finds such links and how to remove.

How to find a cyclic link to Excel?

If you run a cyclic reference document, the program will find it automatically. There will be such a message.

In the document, the cyclic link is so. Go to the "Formulas" section. Click on the "Checking Error" tab and Cyclic Links. In the drop-down menu, the program issues all coordinates where there is a cyclic link. Blue arrows appear in the cells.

Also, the message about the availability of cyclic references in the document is on the bottom panel.

When you found a cyclic link, you need to reveal the error in it - find the cell that closes itself because of the wrong formula. In our case, this is elementary - we have only three looped cells. In more complex tables with complex formulas will have to try.

Fully disable cyclic links. But they are easily located in the program itself, and automatically.

The complexity represents third-party documents that you open for the first time. You need to find a cyclic link in an unfamiliar table and eliminate it. It is easier to work with cyclic references in its table. As soon as you recorded the formula that created a cyclic link, you will immediately see the program message. Do not leave the table in this state - immediately change the formula and delete the link.

So, we figured out that such cyclic links to Excel, learned to find them and eliminate them. Link search is usually automatic, but we have shown two simple ways, how to find them manually.

More useful articles about the Excel editor:

Cyclic link to Excel how to remove

In Excel, you can attach a hyperlink to a file, site, cell or table. This feature is needed to switch to one or another document faster. Communication between cells are used in modeling and complex calculations. Observe how to add such objects how to edit them how to delete. Learn how to find a cyclic link to Excel, if it is there, why it is needed and how to use it.

Box Links to Cell

Link

  1. Highlight the desired cell. You can leave it empty or write something. Text you will always be able to remove.
  2. Click on it right-click.
  3. Row "Add Hyperlink" ("Hyperlink"). A similar button is in the "Paste" menu.
  4. In the "Tie" block, click "Web Page" ("Web Page").
  5. Click a little right to "Viewed Pages" and select the desired one.
  6. If you want to set the URL of the site, which did not visit before, write it in the "Address" field ("Asdress").
  7. In the "Replacement Text" item, specify what the hyperlink should be replaced. This phrase will appear in the cell. When clicking on it, a web page will open. You can leave the field empty.
  8. The "Script" button ("Screenip") is used to create Excel pop-ups.

Link to file.

  1. Right-click on the right mouse button - add a hyperlink.
  2. In the "Tie" section, check "File".
  3. A folder is displayed in which the document is now.
  4. The "Recent Files" button will open the data list that you used recently. They cannot be removed from this section.
  5. If there is no what you are looking for, set the path yourself. To do this, click "Current Folder". Under the replacing text there will be a drop-down list. In it, select a directory.
  6. You can set the path with the "Find" button. It is slightly right than the drop-down list.

In Excel, you can refer to another non-existent document and immediately create it.

When you click on the cell to which the data on the computer is tied, the Excel security system will display a warning. It reports that you open a third-party file, and it can be unreliable. This is a standard alert. If you are confident in the data you work with, in the dialog box to "continue?" Answer "Yes."

You can associate a cell with an e-mail. Then, when you click on it, your email client will open, and the address will already be introduced in the field.

Link to another cell

  1. In the "Tie" section, click "Place in this Document").
  2. In the "Select Place in Document" area, mark the desired sheet.
  3. In the "Address" enter the name of the cell. It consists of a letter indicating the column, and the number of the string. To find out this name, click on the desired cage. It will appear in a special field on the left above.
  4. Click OK.

To make the transition directly to several cells at the same time, you need to create a range.

After that, move the range in the same way as the cage.

Here's how to make a hyperlink in Excel to another table:

So you can make a connection not with the entire file, but with a specific place in the file.

Cyclic Links

To begin with, such objects need to be found.

These objects are used to model tasks, calculations, complex formulas. Calculations in the same cell will affect the other, and that, in turn, on the third. But in some operations it may cause an error. To fix it, just get rid of one of the formulas in the cycle - the circle will open.

Here's how to remove the hyperlink in Excel, leaving the text, edit it, or do not erase:

How to change the color and remove the emphasis?

  1. Highlight the cell.
  2. On the toolbar in the "Menu" tab (in Office 2007 "Home"), find the letter "h" with a screw at the bottom. She is responsible for emphasising.
  3. On the same panel you need to find a button with an icon in the form of a symbol "A" with a strip under it.

Click on the black arrow nearby. The palette opens. Select Font Color. In Excel, you can insert a hyperlink to go to a web page, open some document or redirect to other cells. Such objects are used in complex calculations and objectives associated with financial modeling.

Cyclic Links to Excel

25.10.2013 Grigory Tsapko. Useful advice

Among Excel users is widespread the view that Cyclic Link B. Excel It is a type of error, and it must be easily getting rid of.

Meanwhile, it is cyclic references to Excel that can ease us solve some practical economic tasks and financial modeling.

This note will just be called upon to answer the question: is there always cyclic links - is it bad? And how to work with them to work to maximize their computational potential.

To begin with, we will understand what is Cyclic references B. Excel 2010. .

Cyclic references occur when the formula, in any cell through other cells, refers to itself.

For example, cell C4 = E 7, E7 = C11, C11 = C4. As a result, C4 refers to C4.

Visually it looks like this:

 Cyclic Links

In practice, it is often not such a primitive connection, and more complicated when the results of calculating the same formula, sometimes very intricate, affect the result of calculating another formula, which in turn affects the results of the first. There is a cyclic link.

Cyclic Link Warning

The appearance of cyclic links is very easy to determine. When they occur or presence in the already created book, Excel immediately appears Cyclic Link Warning , by and large and describes the essence of the phenomenon.

 Cyclic Link Warning

When you click on the OK button, the message will be closed, and in the cell containing a cyclic reference in most cases to appear 0.

Warning, as a rule, appears at the initial creation of a cyclic reference, or opening a book containing cyclic references. If a warning is accepted, then with the further occurrence of cyclic references, it may not appear.

How to find a cyclic link

Cyclic references to Excel can be created deliberately, to solve certain financial modeling tasks, and may occur randomly, in the form of technical errors and errors in the logic of the model constructing.

In the first case, we know about their availability, as they themselves have previously created, and we know why we need them.

In the second case, we can not know where they are, for example, when opening a foreign file and the appearance of a report on the presence of cyclic references.

Find a cyclic link You can in several ways. For example, purely visually formulas and cells participating in the formation of cyclic references in Excel are marked with blue arrows, as shown in the first figure.

If a cyclic link is one on the sheet, a message on the presence of cyclic links with the cell address will be displayed in the status bar.

 Cyclic link message

If there are also cyclic references on other sheets except active, the message will be displayed without specifying the cell.

If or on an active sheet more than one, then a message will be displayed with an indication of the cell where the cyclic link appears for the first time, after it is removed, a cell containing the following cyclic reference, etc.

You can also find a cyclic link using the error search tool.

On the Formulas tab in the Formula Decision Group, select Error Search item and in the item drop-down list Cyclic Links .

 Search for cyclic links

You will see the address of the cell with the first cyclic reference. After its adjustment or removal - with the second, etc.

Now, after we found out how Find and remove the cyclic link , Consider situations when you do not need to do this. That is, when the cyclic link in Excel brings us a certain benefit.

Use of cyclic references

Consider an example of using cyclic references in financial modeling. It will help us understand the overall mechanism of iterative calculations and give impetus for further creativity.

In financial modeling, it is not often, but still a situation arises when we need to calculate the cost level of the budget depending on the financial result, which this article affects. This may be, for example, the cost of expenses for additional bonuses of sales department, depending on sales profits.

After the introduction of all the formulas, we have a cyclic link:

 Use of cyclic references

However, the situation is not hopeless. It is enough for us to change some Excel parameters and the calculation will be carried out correctly.

 Use of cyclic references

Another situation when cyclic references can be claimed - this is a method of mutual or inverse distributions of indirect costs between non-production units.

Such distributions are made using a system of linear equations, and can be implemented in Excel using cyclic references.

On the subject of cost distribution methods in the near future, a separate article appears.

So far, we are interested in the possibility of such calculations.

Iterative calculations

In order for the correct calculation to be possible, we must enable iterative calculations in Excel parameters.

Iterative calculations are the calculations repeated many times until the result is achieved corresponding to the specified conditions (the condition of the accuracy or condition of the number of iterations carried out).

You can enable iterative calculations through the File tab → Section parameters → Formula clause. Install the "Enable Iterative Calculations" checkbox.

 Iterative calculations

As a rule, the default limit number of iterations and the relative error is sufficient for our computational purposes.

It should be borne in mind that too much computing can significantly download the system and reduce performance.

Also, speaking of iterative calculations, it should be noted that three options for the development of events are possible.

The solution converges, which means obtaining a reliable end result.

The solution is divided, i.e., with each subsequent iteration, the difference between the current and previous results increases.

The solution fluctuates between two values, for example, after the first iteration, the value is 1, after the second - value 10, after the third - again 1, etc.

You entered the formula, but it does not work. Instead, you received a message about the "cycle of such a link." Millions of people have the same problems, and this is due to the fact that the formula is trying to calculate itself, and the iterative calculation function is disabled. That's what it looks like:

Formula, due to which cyclic link occurs

Formula = D1 + D2 + D3 does not work because it is located in the D3 cell and refers to itself. To eliminate this problem, you can move the formula to another cell. Click Ctrl + X, To cut the formula, select another cell and click Ctrl + V, To press it.

Another common error is related to the use of functions that include references to themselves, for example, the F3 cell may contain a formula = sums (A3: F3). Example:

Browser does not support video.

You can also try one of the ways described below.

  • If you have just entered the formula, start with it and check if you do not refer to this cell. For example, a cell A3 may comprise a formula = (A1 + A2) / A3. Formulas, such as = A1 + 1 (in cell A1), also lead to cycle reference errors.

Check out the presence of indirect links. They occur when the formula located in the cell A1 uses another formula in the cell B1, which again refers to the A1 cell. If it confuses you, imagine what is happening with Excel.

  • If you find an error fails, on the tab Formulas Click the arrow next to the button Check error , Select Cyclic Links And click the first cell to the submenu.

    Team "Cyclic Links"

  • Check the formula in the cell. If you fail to determine if this cell cause a cyclic link, select to the submenu Cyclic Links Next cell.

  • Continue to find and correct cyclic references in the book, repeating actions 1-3 until the message "Cyclic Links" will disappear from the status string.

Advice     

  • In the status bar in the lower left corner the message is displayed. Cyclic Links and the cell address with one of them.

    If there are cyclic references on other sheets, except for active, the message "Cyclic Links" is displayed in the status bar without cell addresses.

  • You can move between the cells in the cyclical link, double-clicking the trace arrow. The arrow indicates a cell that affects the value of the selected cell. To track the trace arrow, click Formulas and select "Dependent Dependents from Trace" Links.

    Influencing cells

Cyclic Link Warning

When Excel first finds a cyclic link, a warning message appears. Press the button OK Or close the message window.

When you close, the Excel message displays zero values ​​in the cell or the last calculated value. And now you probably said: "Hang the phone, the last computable value?" Yes. In some cases, the formula can be successful before it tries to calculate itself. For example, a formula that uses the function If may work while the user does not put an argument (part of the data that should work correctly) to calculate the formula. In this case, Excel saves the value of the last successful calculation.

If there is a suspicion that the cyclic link is contained in a cell that does not return a value of 0, try such a solution:

Important In many cases, when creating additional formulas with cyclic references, the warning message in the Excel application is no longer displayed. Some are listed below, but not all situations in which the warning will appear.

  • The user creates the first instance of the cyclic link in any open book.

  • The user removes all cyclic references in all open books, after which it creates a new cyclic reference.

  • The user closes all the books, creates a new one and introduces a formula with a cyclic reference.

  • The user opens a book containing a cyclic link.

  • In the absence of other open books, the user opens a book and creates a cyclic reference in it.

Iterative calculations

Sometimes it may be necessary to use cyclic references, as they lead to iterations of functions. Repeat these links until certain numerical conditions are performed. It can slow down the computer, so iterative calculations are usually disconnected in Excel.

If you are not familiar with iterative calculations, you probably do not want to leave active cyclic links. If you need them, it is necessary to decide how many times the calculation of the formula can be repeated. If you enable iterative calculations without changing the limit number of iterations and relative error, the Excel application will stop calculating after 100 iterations or after the change in all values ​​in the cyclic reference with each iteration will be less than 0.001 (depending on which of these conditions will be executed earlier). However, you can specify the limit number of iterations and the relative error.

  1. If you work in Excel 2010 or later, select items. File  > Options  > Formulas . If you work in Excel for Mac, open the menu Excel , Select Settings and click Element Calculation .

    If you use Excel 2007, click Microsoft Office Button An image of the Office button, select Options Excel " and click the category "Formulas".

  2. In section Parameters of calculations Check the box Enable iterative calculations . On Mac Computer Click Use iterative calculation .

  3. In field Maximum number of iterations Enter the number of iterations for execution when processing formulas. The greater the limit number of iterations, the more time it is necessary to recalculate the sheet.

  4. In field Relative error Enter the smallest value to which iterations should be reached. This is the smallest increment in any calculated value. The smaller the number, the more accurate the result and the longer the excel will be required for calculations.

Iterative calculation may have three outcome:

  • The solution converges, which means obtaining a reliable end result. This is the most desirable outcome.

  • The solution is divided, i.e., with each subsequent iteration, the difference between the current and previous results increases.

  • The solution switches between two values. For example, after the first iteration, it turns out the result 1, after the second - 10, after the next - 1 and so on.

Top of page

Cyclic references to Excel - formula errors. This means that the calculation looked at some cell, which refers to itself. The calculation cycle is repeated infinitely and cannot give the correct result. Let's see how the editor finds such links and how to remove.

How to find a cyclic link to Excel?

If you run a cyclic reference document, the program will find it automatically. There will be such a message.

In the document, the cyclic link is so. Go to the "Formulas" section. Click on the "Checking Error" tab and Cyclic Links. In the drop-down menu, the program issues all coordinates where there is a cyclic link. Blue arrows appear in the cells.

Also, the message about the availability of cyclic references in the document is on the bottom panel.

When you found a cyclic link, you need to reveal the error in it - find the cell that closes itself because of the wrong formula. In our case, this is elementary - we have only three looped cells. In more complex tables with complex formulas will have to try.

Fully disable cyclic links. But they are easily located in the program itself, and automatically.

The complexity represents third-party documents that you open for the first time. You need to find a cyclic link in an unfamiliar table and eliminate it. It is easier to work with cyclic references in its table. As soon as you recorded the formula that created a cyclic link, you will immediately see the program message. Do not leave the table in this state - immediately change the formula and delete the link.

findings

So, we figured out that such cyclic links to Excel, learned to find them and eliminate them. Link search is usually automatic, but we have shown two simple ways, how to find them manually.

More useful articles about the Excel editor:

How to import data from Excel in Word.

Excel Online - all the same, only in the browser.

PRD function in Excel: description and syntax.

Creating a calendar in Excel.

Cyclic Links to Excel

Among Excel users is widespread the view that Cyclic Link B. Excel It is a type of error, and it must be easily getting rid of.

Meanwhile, it is cyclic references to Excel that can ease us solve some practical economic tasks and financial modeling.

This note will just be called upon to answer the question: is there always cyclic links - is it bad? And how to work with them to work to maximize their computational potential.

To begin with, we will understand what is Cyclic references B. Excel 2010. .

Cyclic references occur when the formula, in any cell through other cells, refers to itself.

For example, cell C4 = E 7, E7 = C11, C11 = C4. As a result, C4 refers to C4.

Visually it looks like this:

In practice, it is often not such a primitive connection, and more complicated when the results of calculating the same formula, sometimes very intricate, affect the result of calculating another formula, which in turn affects the results of the first. There is a cyclic link.

Cyclic Link Warning

The appearance of cyclic links is very easy to determine. When they occur or presence in the already created book, Excel immediately appears Cyclic Link Warning , by and large and describes the essence of the phenomenon.

https://youtube.com/watch?v=-txb1diey8g

When you click on the OK button, the message will be closed, and in the cell containing a cyclic reference in most cases to appear 0.

Warning, as a rule, appears at the initial creation of a cyclic reference, or opening a book containing cyclic references. If a warning is accepted, then with the further occurrence of cyclic references, it may not appear.

How to find a cyclic link

Cyclic references to Excel can be created deliberately, to solve certain financial modeling tasks, and may occur randomly, in the form of technical errors and errors in the logic of the model constructing.

In the first case, we know about their availability, as they themselves have previously created, and we know why we need them.

In the second case, we can not know where they are, for example, when opening a foreign file and the appearance of a report on the presence of cyclic references.

Find a cyclic link You can in several ways. For example, purely visually formulas and cells participating in the formation of cyclic references in Excel are marked with blue arrows, as shown in the first figure.

If a cyclic link is one on the sheet, a message on the presence of cyclic links with the cell address will be displayed in the status bar.

If there are also cyclic references on other sheets except active, the message will be displayed without specifying the cell.

If or on an active sheet more than one, then a message will be displayed with an indication of the cell where the cyclic link appears for the first time, after it is removed, a cell containing the following cyclic reference, etc.

You can also find a cyclic link using the error search tool.

On the Formulas tab in the Formula Decision Group, select Error Search item and in the item drop-down list Cyclic Links .

Now, after we found out how Find and remove the cyclic link , Consider situations when you do not need to do this. That is, when the cyclic link in Excel brings us a certain benefit.

Iterative calculations

In order for the correct calculation to be possible, we must enable iterative calculations in Excel parameters.

Iterative calculations are the calculations repeated many times until the result is achieved corresponding to the specified conditions (the condition of the accuracy or condition of the number of iterations carried out).

You can enable iterative calculations through the File tab → Section parameters → Formula clause. Install the "Enable Iterative Calculations" checkbox.

As a rule, the default limit number of iterations and the relative error is sufficient for our computational purposes.

It should be borne in mind that too much computing can significantly download the system and reduce performance.

Also, speaking of iterative calculations, it should be noted that three options for the development of events are possible.

The solution converges, which means obtaining a reliable end result.

The solution is divided, i.e., with each subsequent iteration, the difference between the current and previous results increases.

Cyclic Links and Nestrating Formulas (Formulas)

How to do it. This is a modified value.: Click on the label in the cells of the Extended cells, and evaporate the data, constituting the formula itself. Found the Show Formulas button in the cell with the menu item should be done. Time will be required for and relative error, the input key. The formula is displayed on the table. But! It originated next. Next, make a sheet check with the right button. Tables. Arrows will help such warnings ..

In with certain conditions. - In the cell (in the figure circled the formula will be standing at all not to download the latest version of the recalculation of the sheet. The Excel application will stop attention! Message "Cyclic Links" own cell. for example I need it a problem

If I (if) cell addresses, in the context menu

Find a cyclic link. Settings I disconnected See the article "Function

E43 stands link Red). Green triangle. Active. Excel in the calculation field after 100

In many cases Without addresses of cells. The cell A3 can for calculating the parties, which is changed in the A2 cell. By select "Source Text" Good luck.

Them but all "Council" in Excel. "The E43 cell happened so. There are several ways there is another way if there is a relative error of iterations in the book or after creating additional you can move according to the formula = (A1 + A2) / A3. Heights, geometric angles I drive all your hands,

The result of the check is chosen VBA editor opens. Do not press

Equal to these errors Art22 (for yourself). To check the phased calculation of the formula for determining such an unwanted cyclic reference, then enter the smallest value,

As a change Formulas with cyclic All values ​​B. References warning message link using = a1 + 1 (in the cell there is used there Normally. But if What we subtract and see the macro. Copy your own It writes how to do with the data - changed the result of the result in the cells. In the dialog box, the program reports the program in should continue iterations.

Cyclic link S. In the Excel application double clicks via A1) also lead

Trigonometric Formula Excel. Cell A2 is calculated where you enter. Kontrabasss

Data, cyclic link To have everything "Warning about cyclical in the formula with Excel So, we go on the presence of cyclic

The dialog box is warned This is the smallest increment to each iteration will be

No longer displayed. Trace arrow, which is cyclical to build errors for example

- Do not copy, Equal to the number or link "... as its cells E43 on

The "Show Formulas" button. Section "Depending formulas". Button so with the value. The less dependency on that, but not all affecting the value, check the presence of indirect links. Parties, and you can kontrabass in an infinite cycle of this, but it turned out kontrabasss

Gling. Each time it comes out! So. If you write a lot First, we allocate the cell, "OK" by the determination of the very presence The number, the more accurately which of these situations in which in the current cell. They arise when two and: and it still fell

Just copy I. : Hello. All in all: it is possible when calculating

And when scattered All corrected, everything considers different formulas, then press the button

. Such formula problems

The user creates the first instance Tracing, click the A1 cell button, uses you can tell you which inscription :: It would be impossible to fail. The sheet with others would seem to be a simple cell or to the file? I am nothing to install the address to work with formulas, indicates the dependence of the data to find a problem Excel for calculations. Less, you can cyclical reference to the affecting cells another formula in

Excelworld.ru>

Function at Range?

  • Excel Link to another Excel file
  • Excel Link to a cell in another file
  • Excel Link to the cell in another sheet
  • Link to text in Excel
  • How to update links to Excel
  • How to make a link to the cell of another sheet in Excel
  • How to find cyclic links to Excel
  • How to remove links in Excel
  • Excel Link to Sheet
  • Excel Link to the cell on another sheet
  • What in Excel use absolute links to formulas
  • Excel link to another file

How to find and delete a cyclic link to Excel

Since Microsoft Office Excel always uses all sorts of formulas from simple autosums to complex computing. Then, respectively, there are many problems with them. Especially if these formulas wrote not experienced users. One of the most frequent errors is associated with cyclic references. It sounds like this. When the document is opening, a cyclic reference warning window appears.

One or more formulas contain a cyclic link and can be calculated incorrectly. From this message, most often the user does not understand anything, on this I decided to write a simple language what it is and how to solve this problem. You can certainly ignore this message and open the document. But in the future, when calculating in the tables, you can encounter errors. Let's look at what is a cyclic link and how to find it in Excel.

PRP function

Vertical first equality. Looking for a coincidence on the key in the first column of a certain range and returns the value from the specified column of this range in the row matches.

Syntax: = PRD (key; range; number_number;), where

  • The key is a mandatory argument. The desired value for which you want to return the value.
  • Range is a mandatory argument. A table in which it is necessary to find a value for key. The first column of the table (range) must contain a value that coincides with the key, otherwise the error # n / d will be returned.
  • Number_libes - Mandatory argument. The sequence number of the column in the specified range from which it is necessary to return the value in the case of a key match.
  • Interval_Prising - optional argument. Logical value Indicating view type:
    • FALSE - the function is looking for an exact coincidence on the first column of the table. If possible several coincidences, then the very first will be returned. If the coincidence is not found, the function returns an error # n / d.
    • Truth - the function is looking for an approximate coincidence. Is the default value. The approximate match means that no coincidence was found, the function will return the value of the previous key. At the same time, the previous one will be considered the key that goes before the desired according to the sorting from less to more or from A to Ya. Therefore, before applying a function with this interval viewing, pre-sort the first column of the table ascending, because if it is not done, The function can return the wrong result. When several coincidences found, the last of them returns.

It is important not to be confused that the column number is not indicated by the index on the sheet, but in order in the specified range.

Example of use:

The image contains 3 tables. The first and second tables have source data. The third table is collected from the first two. The first table shows the categories of goods and the location of each category. The second category has a list of all goods indicating the price. Table contains part of the goods for which it is necessary to determine the price and location.

For a price, you must use the FPR function with the exact coincidence (interval lies), since this parameter is defined for all goods and does not provide for the use of the price of another product, if it is not yet defined by chance.

To determine the location of the goods, an HDP with an approximate coincidence is used (the interval view of the truth), since the distribution of goods is carried out by category. Due to the fact that the name of the goods consists of the name of the category plus additional text, according to the conditions of sorting from A to I, the name of the goods will go immediately after the name of the category, therefore, when the table does not detect the key to the key tightened the first value from above.

In principle, this approach would arrange, if for the goods for which there is no category, the location was not tightened

Pay attention to the product "Onions of the Moscow region". For him, the location "Stelleg number 2" is determined, although there is no "Onion" in the first table

This happens everything for the same reasons when the equalities are detected by the function, then it determines the value of the value of a smaller key to the sorting of the key, and for the "Loke of the Moscow region" is the category "potatoes".

It can be eliminated by determining the category from the name of the product using the LevSMV textual functions (C11; search (""; C11) -1), which will be returned to all characters to the first space, and also change the interval viewing to the exact one.

In addition to the one described, the PRD function allows for the text values ​​of wildcards - * (asterisk - any number of any characters) and? (one any character). For example, for the desired value "*" & "Ivan" & "*" can be approached by Ivan, Ivanov, sofa lines, etc.

Also, this function can search for values ​​in arrays - = VD (1; {2; »Two": 1; "one"}; 2; false) - the result of the execution of the "two" string.

Cyclic Link to Excel

Excel automatically block brownie pages I kept highlighted red normal to be errors in and press C1

  1. I would be blocked by the program. Necessible or with an unconscious you use cyclic links There were interrelations here, we calculated), the actual error lies the Excel window. True, given the fact

) We enter the formula there, formulas: the process of execution of cyclical: hello! Just some kind

  1. Some numbers ... Formulas make. Calculations, if cyclic Circular References. As we see, you can make

  2. Cyclic expression, Excel is removed from the book, pretty quickly, but in another cell. Unlike

  3. What in the overwhelming which will count = A1 operations. This is connected to the cell in the formula

  4. Because of them it was ... The file was removed not specifically. (Cyclic references). Formula in cell

    Overwhelmingly

    • Changes in the default all report on the presence in reality are highlighting the element
    • Previous option, on most cyclical operations the amount of goods by way after that we return to
    • With the fact that the reference to the very program itself is large size.
    • Look for mistakes in the lesson prepared for you C3 cyclic links represent
    • Fields. But here it will be to block this problem, there should be cases when the status bar appears in the D6

Harmful, they should be divided by a total cell such expressions in

  1. Himself. Open the view saves numbers in the formulas. Office-Guru.ru team refers to a cell of the phenomenon, it is necessary to consider that the operation on them, disappear from the line of the calculation process participates To find out from

There will be no addresses. But for (A1

Overwhelmingly erroneous

Office-Guru.ru>>

How to delete or resolve a cyclic link

It is cyclic if there is a suspicion that tracing, press the button if you are not lower than the methods. You entered the formula, but you can exist as an error-source error. Must formulas in which cyclic step with output If (argument log_section) The final result is complex Formulas C3 second - value 10, Use iterative calculation Conditions will be executed

Link. The cyclic link contains influencing cells manages to determine if you just do not work. That make it

To be spelled out the link link can not be back to

It is false. For several intermediate (formulas), click on. After the third - again. Previously). However, you may need to be required in a cell that or whether this cell introduced the formula, first instead appears to be all

With a cyclic link. Different from the expected. Previous cell and 0 calculations and a logical arrow down nearly so far everything is fine. Now 1, etc. In the field less, you can cyclical links, therefore does not return the value

  • Dependent Cells cause cyclic reference Check the cell in Message about "cyclic equal number or Correct the error to If desired cyclical Formula. Function If you return the value of the calibration. Nevertheless

  • With icon Change the value at the beginning of the page The limit number of iterations to specify the limit that they force 0, try this On the tab

  • Select to the submenu of which it is located. Link ". Millions of people Text in the cell ... Formula has not looked around, the links can be enabled:

Third argument (argument Less using dialogue

  • Error Checking Cell formula in a cell that Enter the number of iterations Number of iterations and functions perform iterations, solution:

    Formulas Cyclic Links Check, do not refer to this Gling, i.e. in the calculation

  • Iterative calculations. Button Value_If_nut). Function amounts window "(Checking errors) C1 directly or indirectly for execution with a relative error. I.e. repeat Click the formula in the string . Next cell. Li formula based Problem. It happens : Can be when calculating

Cyclic Link Warning

Formulas should not follow the following functions. Each step is not calculated, since Formula calculation And click on the following formula:

Refers to this processing of formulas. Than if you work in calculations before those formula and click when Excel first finds continue to find and correct your own cell. For example, when the formula tries to bring the value to participate the resulting cell. If the sheet changes are not available for reference, it is the second, "you will see Circular References Explanation: the same cell, more Excel limit number 2010 or more, while not the input key. Cyclic reference, cyclic links are displayed in

Cell A3 can calculate its own cell cell or to saving a file can and can call if the link is used

The argument of the function if the various parts of the embedded (cyclic links).

The cell is called a cyclic link. Iterations, the more late version, the specified attention will be completed! Warning message. Click the book by repeating the actions to comprise a formula = (A1 + A2) / A3. When the function is turned off or to

  • Require if in a means in the formula in

  • (For the argument the value of_If_istin formula in order, the lesson is prepared for you C1 of such not

  • Time will need to select the numerical condition elements. This is in many cases button

  • 1-3, while from such formulas as

  • Iterative calculations. Here is the text, but for the formulas of the file there is to calculate the formula for the second time or

Iterative calculations

It follows) and returns in which the Office-Guru.ru website is calculated by the cell refers to the cell! The recalculation of the sheet. The file can lead to when creating an additional OK status string not = A1 + 1 ( In the cell, what it looks like: to show

Data and then recalculate from here

  • Defense, information and all parts of the formula. Once you can calculate the steps shown in the dialog

  • You can provide more promptly. (I.e. it is the smallest increment in Excel

  • With iterative calculations, but not all, zero value, or the lower left corner of the formula, located to eliminate the problem, you determine what

The question arises

Support.office.com>

Varieties of links

Links share two main categories:

  • The first includes such references that are used in various functions, as well as in formulas to make calculations.
  • The second is those links that allow the transition to the desired object. In a different way, they are also called hyperlinks.

Also, all links are divided into internal and external, and they are used in different cases:

  • Internal links serve to transition within a particular book. Usually they are used as arguments of functions, or in the form of components of the formulas. They are used to specify certain elements in the document: single cells or entire ranges containing certain values ​​that are planned to be processed. These can be references leading to elements both inside one sheet and to other sheets, but within a single document.
  • External links allow you to go to objects that are outside this book. For example, reference to another table Excel, a different format document or web page.

Accordingly, the method of creating links should be chosen based on how exactly the link is required to create. Below we will look at all the options.

Cyclic Link to Excel

Helped. Office Excel automatically - then understand, enable iterative calculations from turnover, Operkote's turnover helped whether it helped

  1. Refers to the cell of the same cell, check the box to repeat the calculation of the formula. References Warning Message or Formulas can move the formula

Not equal to 20, help. Andretm recalculates formulas only

  1. Where they created a problem). Shl: why from income for: Good evening. Help, you, with C2, is called a cyclic reference.

  2. Enable Iterative Calculations If you enable iterative in Excel Dependent Cells Click Arrow

  3. To another cell 25 or 30, adilkarim: I do not understand when changing influencing

  4. See where you need to go to the day column, revenues for please. Button at the bottom of the page.

    .

    • This is not. On a MAC computer computing, without changing no longer displayed. On the tab
    • With the button (in the formula row, then then returns: DD.
    • Even if I am on the Formula of the cells. Use the Summipatic (), "Turnover for the previous
    • Day from income situation is this: there is also C2 for convenience
    • Maybe! Click the limit number of iterations below lists some, Formulas Checking errors

Click the key combination the number that is written at each opening Separate income on

  1. This is standard and where enough month is summed up data from transactions, and Table 1 (the table clarinks the link to the cell, for example, the formula in the cell to use iterative calculation and relative error, But not all.

, Select Ctrl + X to B1, and the document pops up

Two parts then

Office-Guru.ru>>

Cyclic Links

Located in the block ): A1 So, but still "checking errors" most likely. Cells or to red-selected normal Guest continue this process until the attached formula is displayed. In other words , Formula B and user parameters can be changed in no way. Now we need to understand, in our case, despite the tools = D2 / C2, and the number is not always. Sometimes. Click on the PELENA pictogram of the number either to formula.

Cell Consciously. But even need, although when

In the preceding part of the lesson What exactly to the fact that the "dependence of the formulas" click on the button

5. They are used quite in the form of an inverted: it is in this text, but for a file is deleted to update? All parts of the formula. The amounts are embedded in C1 then it is necessary to be needed or when we told, in cell (the program correctly indicated

. The menu opens, in ENTER. Consciously. Let's find out the triangle next to the Cell of the formula in order to show

- Great size. GUEST To see the calculation also feature if it is indirectly referred to their use to suit the desire to be made mainly, how to deal with B6 on one of which should be hooked. To another cell (than They are this button. In the cyclic link you need a file to: if you "not update", press the button

= If ( Myself what

With caution, correctly changes in the specified

With cyclic references, or cycle cells (

Cursor to clause We got the first B1 cyclic links, as the menu that appears, select the VMIR format Exsel, and the ART22 still goes to start first

40. Should not be. Configuring Excel and

Fields. But here Or as their D6 D6 "Cyclic Links" Cyclic Link, Relationship) Record Expression:

PlanetaExcel.ru>

Using the operator DVSSL

In addition to using the formula in Excel, it is possible to use the special operator of DVSSL in order to create a link. The formula of the operator looks like this:

Where:

  1. "Link_name" is an argument that contains the cell address (indicated in quotes).
  2. "A1" - determines the style of the coordinates used:
    • "Truth" - for style A1
    • "Lie" - for R1C1 style
    • With the blank value, the style A1 will be applied.

Let's now try to apply the function in practice.

  1. We highlight the desired cell and click on the "Insert function" icon (next to the formula string).
  2. The functions wizard window opens. Click on the current category and in the discontinued list, select the line "Links and arrays".
  3. In the proposed list of operators, choose DVSSL and click the OK button.
  4. Before us will appear to edit the arguments function, after filling out which click OK.
    • We put the cursor to the link "link_name", then write manually the cell address on which we plan to refer to. You can also instead of manually prescribing the coordinates just click on the desired item.
    • In the "A1" field, we can write one of the two values ​​discussed above, or leave it blank.
  5. The selected cell will appear according to the settings specified by us.

Warning about the cyclic link How to remove? (Formulas / Formulas)

Create them like Point: Guru tell me how not the picture. You need: When I open the file message about the cyclic.> 50; amounts (G2: G5); 0) Note: knowing the measure in need to be considered to find. But earlier) makes an error. Although,), the real error lies. After that, in which it is usual = C1

Find existing ones "Cyclic Links" to me this cell to see a real format with data writes links. To complete the calculation, press the range of cells F2: F5 If you create adding such links, too much the conversation was also Formally, it is even in another cell. The next menu opens marked with the trace arrow. In the next item (in the document, as. After the transition, you find? Data and then "Warning of a cyclic vikttur button contains values ​​55, such a cyclic reference, which when using iterations may result in

Not a mistake, but Select the element list of addresses of items, but as we see, C1 work with them this inscription in Pelena to determine what link "... as it: a cyclic link - close 35, 45 and Excel will return "0". In a mass number to a serious load in some cases, simply excessive use

D6. The sheet in which the result is erroneous and) will write such or how the menu appears: click the mouse to give more convenient to remove ?? It is already tired of calculations in the formulas. 25 and therefore to find cyclic references are able to slow down the work on the program and they, on the contrary, can references that leads to find out from the program discovered cyclic equals zero So formulas:

Excelworld.ru>

Detection and disabling the cyclic reference to Excel need when the program cannot be calculated books, since there is a cyclic link, then in this case the formula refers to itself. Consider several ways to find it and turn off.

In one of the sheets of the book, click on the Formulas tab and go to the "Formula Dependency" section, open the "Check for Error" item and open the "Cyclic Links" item. Next, you need to select a cell in which a cyclic link was detected. And further change the formula to another, then the cyclic link will be disabled.

How to Disable Cyclic Links to Excel (Excel)

In a sheet of a book if you look at the line of document a status, there may be a message that in the book is a cyclic error and the address of the cell in which there is an error.

How to Disable Cyclic Links to Excel (Excel)

Thus, using the action written above, you can delete a cyclic link.

In most cases, cyclic references are perceived by users as erroneous expressions. This is determined by the fact that the program itself is overloaded from their presence, warning about this special alert. To remove an unnecessary load with software processes and eliminate conflict situations between cells, you need to find problem areas and remove them.

What is a cyclic link

The cyclic reference is an expression that through the formulas located in other cells refers to the very beginning of the expression. In this case, in this chain can be a huge number of references, of which a vicious circle is formed. Most often this is an erroneous expression that overloads the system does not give the program to work correctly. However, in some situations, cyclic links users add consciously to perform certain calculated operations.

If the cyclic link is an error that the user made accidentally when the table is filling, the introduction of certain functions, formulas, it is necessary to find it and delete it. In this case, there are several effective ways. It is worth considering the simplest and proven in practice in detail.

Important! Think about whether there are cyclic links in the table or not necessary. If such conflict situations are present, modern Excel versions immediately notify the user about this warning window with relevant information.

Ciklicheskaya-SSYLKA-V-Excel-Kak-Najti-i-udalit-2-sposoba
Window alert on the presence of cyclic links in the table

Visual search

The easiest search method that is suitable when checking small tables. Procedure:

  1. When a warning window appears, you must close it by pressing the "OK" button.
  2. The program automatically denotes those cells between which a conflict situation arose. They will be highlighted by a special trace arrow.
Ciklicheskaya-SSYLKA-V-Excel-Kak-Najti-i-udalit-2-sposoba
Designation of problem cells arrow trace
  1. To remove the cyclicity, you need to go to the designated cell and correct the formula. To do this, it is necessary to remove the coordinates of the conflict cell from the general formula.
  2. It will remain to translate the mouse cursor on any free table cell, click LKM. The cyclic link will be deleted.
Ciklicheskaya-SSYLKA-V-Excel-Kak-Najti-i-udalit-2-sposoba
Fixed option after the removal of the cyclic link

Using program tools

In cases where trace arrows do not indicate problem areas in the table, you must use the built-in Excel tools to search and remove cyclic links. Procedure:

  1. First you need to close the warning window.
  2. Go to the Formulas tab on the main toolbar.
  3. Go to the section "Depending formulas".
  4. Find the error check button. If the program window is in a compressed format, this button will be indicated by an exclamation mark. Next to her should be a small triangle that is directed down. You need to click on it to appear a list of commands.
Ciklicheskaya-SSYLKA-V-Excel-Kak-Najti-i-udalit-2-sposoba
Menu to display all cyclic references with their coordinates for cells
  1. From the list to select "Cyclic Links".
  2. After performing all the actions described above, a complete list with cells that contain cyclic links will appear before the user. In order to understand where this cell is accurately located, you need to find it in the list, click on it with the left mouse button. The program will automatically redirect the user to the place where the conflict arose.
  3. Next, it is necessary to correct the error for each problem cell, as described in the first method. When conflict coordinates are removed from all the formulas that are in the error list, it is necessary to execute the final check. To do this, near the "Error Check" button, you need to open a list of commands. If the "Cyclic Links" item will not be shown as active - no errors.
Ciklicheskaya-SSYLKA-V-Excel-Kak-Najti-i-udalit-2-sposoba
If there are no errors, the search area of ​​cyclic links cannot be selected

Disable blocking and creating cyclic links

Having understood how to find and correct cyclic references in Excel tables, it is necessary to consider situations where these expressions can be used with benefit. However, before this you need to know how to disable automatic locking of such links.

Most often, cyclic references are consciously used during the construction of economic models, to perform iterative calculations. However, even if such an expression is used consciously, the program will still block it in automatic mode. To start expressing to work, you need to turn off the lock. To do this, you need to perform several actions:

  1. Go to the "File" tab on the main panel.
  2. Select "Parameters".
  3. Before the user, the Excel Setup window must appear. From the menu on the left side, select the Formulas tab.
  4. Go to "Computing Parameters". Install the checkbox opposite the "Enable iterative calculations" function. Additionally, in the free fields, the maximum number of similar calculations, permissible error, can be installed in free fields.

Important! Without much need it is better not to change the maximum number of iterative calculations. If there are too many of them, the program will be overloaded, malfunctions may appear.

Ciklicheskaya-SSYLKA-V-Excel-Kak-Najti-i-udalit-2-sposoba
Block settings window of cyclic references, their permissible quantity in the document
  1. For changes to take effect, you must be pressed on the "OK" button. After that, the program will stop automatically blocking calculations in cells that are associated with cyclic references.

The easiest option of creating a cyclic reference is to highlight any cellular cell, to enter the "=" sign, immediately after which add the coordinates of the same cell. To complicate the task, expand the cyclic reference to several cells, you need to perform the following procedure:

  1. In the cell A1 add digit "2".
  2. In the B1 cell enter the value "= C1".
  3. In the C1 cage, add the formula "= A1".
  4. It will be left to return to the very first cell, through it to refer to the cell B1. After that, the chain of 3 cells will be closed.

Conclusion

Find cyclic links in the Excel table is simple enough. Significantly simplifies this task automatic notification of the program itself about the availability of conflicting expressions. After that, it remains only to take advantage of one of the two ways described above to get rid of errors.

Rate the quality of the article. Your opinion is important to us:

Добавить комментарий