A circular reference is a formula that either directly or indirectly depends on itself. With a circular reference formula, you can't calculate the formula because to calculate the formula you first need to calculate the formula. Although that description sounds like gobbledy-gook, it's more common in real life than you might think. (Pension fund calculations, for example, are often circular.) Say you were building a budgeting worksheet and that one of your formulas calculated an employee bonus that equaled, say, 10 percent of the profits after deducting the bonus. To calculate the bonus, you need to know the profits. But to calculate the profits, you need to know the bonus. You see the circular nature of this.

If you build a worksheet that makes a circular reference, Excel initially assumes you've made an error and displays an error message. After you click the OK button in the alert box, Excel uses the status bar to identify the cell with the formula creating the circular reference. (The message says "Circular:" followed by the cell address with the circular reference formula.)

If you know you've made an error, click OK; Excel displays the Circular Reference toolbar. You can use its drop-down list box to display a list of the cell references that make up the circular reference and to move to any of these cell references. (The idea here, perhaps obviously, is that you move through each of the cells that make up the circular reference, looking for the formula that erroneously creates the circularity.)

If you build a worksheet that intentionally uses a circular reference, Excel may be able to iteratively solve the circular reference. It turns out that some circular references—like the one about an employee bonus equaling ten percent of the profits after the bonus—converge to a single solution. All Excel has to do is repeatedly recalculate the worksheet. To tell Excel that you want it to repeatedly calculate a worksheet in an attempt to resolve a circular reference, choose Tools | Options, select the Calculation tab, and then mark the Iteration check box.

Note, however, that some circular references can't be solved. In this case, the formula that creates the circular reference doesn't converge to a single, correct solution. Sometimes, this lack of convergence causes formula results to jump around wildly. Other times, the lack of convergence causes the formula results either to continue to grow or decline—and usually at an accelerating rate. In practice, circular references that don't converge to a single solution are erroneous formulas. You've either made an error entering the formula—perhaps you referenced an incorrect cell or the cell itself with the formula. Or you've constructed a large, complex workbook—and your modeling logic has broken down someplace.

NOTE: Excel doesn't alert you to circular references if you've already told it you want it to make iterative, or repeated, workbook recalculations. Excel figures that if you've told it to do this, you know you've got circular references that you're trying to solve through convergence.