Check the box “Enable iterative calculation.” You can further set the maximum number of iterations as well as the maximum change at which you want Excel to stop iterating. Click on “File” and then “Options.” Navigate to “Formulas,” as shown in figure 19. Excel 2016 allows up to 32,767 iterations. In some cases, you will want to keep the circular references and allow Excel to iterate in order to calculate the result. Select “Values” and press Enter.Įnable iterative calculations for solving circular references Copy and paste one of the cells by using the “Paste Special” dialogue (press Ctrl + Alt + V on the keyboard). If cell A refers to cell B, and cell B refers to cell A, then you must break the circle, for example by making cell B refer to cell C instead of cell A.Īlternatively, you can replace one formula cell of the circle by using hard values. To remove unintentionally created circular references, you’ll need to correct the corresponding formulas. People often don’t intentionally create circular references instead, they usually result from formula errors. 
It also shows you the circular reference, with blue arrows pointing out the calculation chain for the circular reference. When you enter a formula that turns out to be part of a circular reference, Excel warns you with an error message. This list could potentially contain hundreds of cells.
After clicking on the “Circular References” button, you’ll see a list of cells that are involved in the circular reference.
If it is not grayed out, then your file has circular references. If the “Circular References” button is grayed out such that you cannot click on it, then your workbook has no circular references.If the “Formula Auditing” group is collapsed, as shown in figure 18, then click on it.To find circular references, follow these steps to identify and remove them.
0 kommentar(er)
