Circular reference in Excel

Allow or correct a circular reference A formula that refers to its own cell directly or indirectly is called a circular reference. Microsoft Excel cannot automatically calculate all open workbooks when one of them contains a circular reference. You can remove a circular reference, or Excel can calculate each cell involved in the circular reference once using the results from the previous iteration. Unless you have changed the default iteration settings, Excel stops calculating after 100 iterations or when the change between all the referenced values does not exceed 0.001 between iterations. To find and remove a circular reference If the Circular Reference toolbar is not displayed, click Customize in the Tools menu, click the Toolbars tab, and then check the Circular Reference checkbox. In the Circular Reference toolbar, click on the first cell in the Navigate through Circular References box. Examine the formula in the cell. If you cannot determine if the cell is the cause of the circular reference, click on the next cell in the Navigate through Circular References box. Note The status bar displays the word "Circular" followed by a reference to one of the cells contained in the circular reference. If the word "Circular" appears without a reference to a cell, the active worksheet does not contain a circular reference. Continue to examine and correct the circular reference until the status bar no longer displays the word "Circular". Tips When the Circular Reference toolbar is displayed, audit arrows appear to designate the cells dependent on the formula. You can move between the different cells of a circular reference by double-clicking on the audit arrows. To operate a circular reference by changing the number of iterations of formulas in Microsoft Excel In the Tools menu, click Options, then click the Calculation tab. Check the Iteration checkbox. To set the maximum number of times Microsoft Excel recalculates, type the number of iterations in the Max Iterations box. The higher the number of iterations, the longer it takes to calculate a worksheet. To set the maximum change between calculation results, type the number in the Maximum Change box. The smaller the value, the more precise the result and the longer it takes to calculate a worksheet.