When most spreadsheet users hear the term circular reference, they immediately think of an error condition. In the vast majority of situations, a circular reference represents an accident-something that you need to correct. Sometimes, however, a circular reference can be a good thing. This chapter presents some examples that demonstrate intentional circular references.
When entering formulas in a worksheet, you occasionally may see a message from Excel, such as the one. This message is Excel's way of telling you that the formula you just entered will result in a circular reference. A circular reference occurs when a formula refers to its own cell, either directly or indirectly. For example, you create a circular reference if you enter the following formula into cell A10 because the formula refers to the cell that contains the formula:
=SUM(A1:A10)
Every time the formula in A10 is calculated, it must be recalculated because A10 has changed. In theory, the calculation could continue forever while the value in cell A10 tried to reach infinity.
When you see the circular reference message after entering a formula, Excel gives you two options:
- Click OK to attempt to locate the circular reference. This also has the annoying side effect of displaying a help screen whether you need it or not.
- Click Cancel to enter the formula as is.
Most circular reference errors are caused by simple typographical errors or incorrect range specifications. For example, when creating a SUM formula in cell B10, you might accidentally specify an argument of B1:B10 instead of B1:B9.
If you know the source of the problem, click Cancel. Excel displays a message in the status bar to remind you that a circular reference exists. In this case, the message reads Circular References: B10. If you activate a different workbook or worksheet, the message simply displays Circular References (without the cell reference). At this point, you can then edit the formula and fix the problem.
If you get the circular message error but you don't know what formula caused the problem, you can click OK in response to the dialog box alert. When you do so, Excel shows the Help topic on circular references and also draws errors on the worksheet, which may help you identify the problem. For more help, choose Formulas
Note: | The Circular References command on the Ribbon will not be available if you have the Enable Iterative Calculation setting turned on. You can check this setting in the Excel Options dialog box (in the Formulas section). I discuss more about this setting later in this chapter. |
Often, finding the source of a circular reference is easy to identify and correct. Sometimes, however, circular references are indirect. In other words, one formula may refer to another formula that refers to a formula that refers back to the original formula. In some cases, you need to conduct a bit of detective work to figure out the problem.