What is a Circular Reference Error?
A circular reference error occurs when a formula in a cell refers to itself or to another cell that depends on the value of the original cell. This creates a loop of calculations that never ends, and Excel cannot resolve the value of the cell. For example, if cell A1 contains the formula =A1+1, this is a circular reference, because the cell is trying to calculate itself.
Circular references can also happen indirectly, when a formula in a cell refers to another cell that contains a formula that refers back to the original cell. For example, if cell A1 contains the formula =B1+1 and cell B1 contains the formula =A1+2, this is also a circular reference, because the cells are trying to calculate each other.
How to Fix a Circular Reference Error?
There are two ways to fix a circular reference error in Excel:
- Remove the circular reference by changing the formula or moving it to another cell that is not involved in the calculation loop.
- Allow the circular reference by enabling iterative calculation, which lets Excel repeat the calculation until a certain condition is met.
Remove the Circular Reference
The simplest way to fix a circular reference error is to remove the circular reference from the formula. This can be done by changing the formula so that it does not refer to itself or to another cell that depends on its value. For example, if cell A1 contains the formula =A1+1, this can be fixed by changing the formula to =A2+1 or =B1+1, or any other cell that is not involved in the circular reference.
Another way to remove the circular reference is to move the formula to another cell that is not involved in the calculation loop. For example, if cell A1 contains the formula =B1+1 and cell B1 contains the formula =A1+2, this can be fixed by cutting the formula from cell A1 and pasting it to cell C1, or any other cell that is not involved in the circular reference.
To find the cell that is causing the circular reference error, you can use the Error Checking feature in Excel. Here are the steps to do that:
- Launch Microsoft Excel and trigger the error message by entering a circular reference formula.
- Go to the Formulas tab and select Error Checking.
- Select the Circular Reference entry from the drop-down menu. This will highlight the cell that is causing the circular reference error.
- Review the formula in the cell and either change it or move it to another cell to remove the circular reference.
- Repeat steps 3 and 4 until the status bar no longer displays “Circular References”.
Allow the Circular Reference
Sometimes, you may want to use a circular reference on purpose, because it allows you to create iterative calculations that repeat until a certain condition is met. For example, you may want to calculate the interest rate of a loan based on the monthly payment, the loan amount, and the number of periods. This requires a circular reference, because the interest rate depends on the monthly payment, and the monthly payment depends on the interest rate.
To allow a circular reference in Excel, you need to enable iterative calculation, which lets Excel repeat the calculation until the value of the cell converges to a stable value or reaches a maximum number of iterations. Here are the steps to do that:
- Launch Microsoft Excel and trigger the error message by entering a circular reference formula.
- Go to the File tab and select Options.
- Select Formulas from the left sidebar and check the box that says “Enable iterative calculation”.
- Optionally, you can adjust the maximum number of iterations and the maximum change values. The maximum number of iterations is the number of times that Excel will repeat the calculation before stopping. The maximum change is the smallest amount of change between two iterations that Excel will accept as a solution. The default values are 100 and 0.001, respectively.
- Click OK to save the changes and close the dialog box.
- Excel will now perform the iterative calculation and display the result in the cell.
Example
To illustrate how to use a workaround formula to prevent circular reference error in Excel, let us consider the following scenario:
You are a sales manager and you want to calculate the commission for your sales team based on their sales performance. The commission rate is 10% of the sales amount, but there is a catch: the sales amount includes the commission itself. This means that the commission depends on the sales amount, and the sales amount depends on the commission. This creates a circular reference, and Excel cannot calculate the commission directly.
To solve this problem, you can use a workaround formula that approximates the commission using an iterative calculation. The formula is:
=Sales Amount/(1+Commission Rate)-Sales Amount
This formula works by dividing the sales amount by 1 plus the commission rate, which gives the sales amount without the commission. Then, it subtracts the sales amount from the result, which gives the commission. This formula repeats the calculation until the commission converges to a stable value.
To use this formula, you need to enable iterative calculation in Excel, as explained in the previous section. Then, you can enter the formula in the cell where you want to calculate the commission. For example, if the sales amount is in cell B2 and the commission rate is in cell C2, you can enter the formula =B2/(1+C2)-B2 in cell D2 to calculate the commission.
Here is an example of an Excel table that shows the sales amount, the commission rate, the commission, and the total sales amount (sales amount plus commission) for four salespeople:
| Salesperson | Sales Amount | Commission Rate | Commission | Total Sales Amount |
|---|---|---|---|---|
| Alice | $10,000 | 10% | $909.09 | $10,909.09 |
| Bob | $15,000 | 10% | $1,363.64 | $16,363.64 |
| Charlie | $20,000 | 10% | $1,818.18 | $21,818.18 |
| David | $25,000 | 10% | $2,272.73 | $27,272.73 |
The formula for the commission in cell D2 is =B2/(1+C2)-B2, and it is copied down to the other cells in column D. The formula for the total sales amount in cell E2 is =B2+D2, and it is copied down to the other cells in column E.
Other Approaches to Prevent Circular Reference Error
Besides using a workaround formula, there are other approaches to prevent circular reference error in Excel. Some of them are:
- Use the Goal Seek feature to find the value of a cell that makes a formula result in a desired value. For example, you can use Goal Seek to find the interest rate that makes the monthly payment of a loan equal to a certain amount.
- Use the Solver add-in to find the optimal value of a cell that satisfies one or more constraints. For example, you can use Solver to find the commission rate that maximizes the profit of a sales team.
- Use a user-defined function (UDF) to create a custom function that performs an iterative calculation. For example, you can use a UDF to create a function that calculates the commission based on the sales amount and the commission rate, using a loop or a recursive algorithm.
