How to Find Circular References in Excel
※ Download: Find circular references excel
You can always ask an expert in the , get support in the , or suggest a new feature or improvement on. So far, everything's OK. Working out such a formula would cause an endless loop, so instead of performing the calculation, Excel displays a warning.
However, here and in the status bar , only the circular reference entered last is displayed. Let me take you through one example. Why does Microsoft Excel give you a heads-up?
Remove or allow a circular reference - Why the date is changing on spreadsheet, when the cursor is left on the initial field after exiting. Does anyone have any suggestions on how to locate this circular reference error on Excel 2003?
To fix the problem, you can move the formula to another cell in the formula bar, press Ctrl+X to cut the formula, select another cell, and press Ctrl+V. While you're looking, check for indirect references. They happen when you put a formula in cell A1, and it uses another formula in B1 that in turn refers back to cell A1. If this confuses you, imagine what it does to Excel. If you can't determine whether the cell is the cause of the circular reference, click the next cell in the Circular References submenu. The arrow indicates the cell that affects the value of the currently selected cell. You show the tracer arrow by clicking Formulas, and then click either Trace Precedents or Trace Dependents. Learn about the circular reference warning message The first time Excel finds a circular reference, it displays a warning message. Click OK or close the message window. When you close the message, Excel displays either a zero or the last calculated value in the cell. In some cases, a formula can run successfully before it tries to calculate itself. For example, a formula that uses the may work until a user enters an argument a piece of data the formula needs to run properly that causes the formula to calculate itself. When that happens, Excel retains the value from the last successful calculation. Important In many cases, if you create additional formulas that contain circular references, Excel won't display the warning message again. This can slow your computer down, so iterative calculations are usually turned off in Excel. Unless you're familiar with iterative calculations, you probably won't want to keep any circular references intact. If you do, you can enable iterative calculations, but you need to determine how many times the formula should recalculate. When you turn on iterative calculations without changing the values for maximum iterations or maximum change, Excel stops calculating after 100 iterations, or after all values in the circular reference change by less than 0. However, you can control the maximum number of iterations and the amount of acceptable change. If you're using Excel 2007, click the Microsoft Office Button , click Excel Options, and then click the Formulas category. On the Mac, click Use iterative calculation. The higher the number of iterations, the more time that Excel needs to calculate a worksheet. This is the smallest change in any calculated value. The smaller the number, the more precise the result and the more time that Excel needs to calculate a worksheet. This is the desirable condition. 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, and so on. You can always ask an expert in the , get support in the , or suggest a new feature or improvement on.
About the Author Shawn McClain has spent over 15 years as a journalist covering technology, business, culture and the arts. Supposing you want to add up values in column A with a usual SUM formula, and when doing this you inadvertently include the total cell itself B6 in this example. For circular formulas to work, you must enable iterative calculations in your Excel workbook. Note that the Enable Iterative Calculation check box is here as well. The basic idea of this method is to avoid circular references in general. In the Formula Auditing group, click the Error Checking and then the Circular References button.
