How To Fix Excel Circular Reference Error There Are One Or More Circular References Where A Formula Refers To Its Own Cell Either Directly Or Indirectly

When you input a formula in an excel cell, sometimes you may encounter the excel circular reference error. The error message is there are one or more circular references where a formula refers to its own cell either directly or indirectly. This article will tell you how to fix this error on Excel for Windows & macOS.

1. How To Reproduce The Excel Circular Reference Error.

1. For example, when you input the formula =A1+A2 in cell A2 and press the enter key, it will pop up a dialog with this error message.
2. The error message is There are one or more circular references where a formula refers to its own cell either directly or indirectly.

2. How To Fix The Excel Circular Reference Error.

2.1 Why The Excel Circular Reference Error Happens.

1. Because you input the formula =A1+A2 in cell A2, cell A2‘s value is A1+A2.
2. Then the formula will be translated to =A1 + (A1 + (A1 + (A1 + …….))).
3. So the formula contains an endless loop in it, then the circular reference error occurs.

2.2 How To Fix The Error On Excel For Windows OS.

1. If you use excel on Windows, click the File —> Options item.
2. Then it will pop up the Excel Options dialog window.
3. Click the Formulas item in the Excel Options dialog left side.
4. Check the checkbox Enable iterative calculation in the Calculation options area.
5. You can also change the Maximum Iterations and Maximum Change values below the checkbox in the related input text box.

2.3 How To Fix The Error On Excel For Mac OS.

1. If you use excel on macOS, click the Excel —> Preferences… menu item on excel top menu bar.
2. Then it will open the Excel Preferences dialog window.
3. In the Formulas and Lists section, click the Calculation icon to open the Calculation dialog.
4. Check the checkbox before the Use iterative calculation in the Calculation Options area.
   
5. You can change the Maximum iterations and Maximum change value in the text box below the checkbox.

3. What Does The Maximum Iterations & Maximum Change Mean?

3.1 Maximum iterations Options.

1. After you enable the Use iterative calculation checkbox in section 2, you can find the error dialog will disappear.
2. And when you input number 1 in cell A1, you will find cell A2‘s value will be 100. And cell A2 contains the formula =A1+A2.
3. This is because the default value of the Maximum iterations option is 100, which means the iteration will repeat 100 times only.

3.2 Maximum change Options.

1. Now we change the value of the Maximum change option to 2.
2. And when you input the number 1 in cell A1, cell A2‘s value will be number 1 also.
3. This is because when the minus value of 2 adjacent iteration result values in the loop is smaller than the Maximum change option value, then the iteration will stop.
4. Because in the first loop, the formula A1+A2‘s value is 1, and cell A2‘s initial value is 0.
5. So the minus value between the first 2 iterations is 1, which is smaller than the Maximum change option value ( 2 ), so the loop stop.
6. If you input number 2 in cell A1, then you will find the number 200 in cell A2, this is because the loop exit only after repeating 100 times which is decided by the Maximum iterations option.
7. Because in this case, the minus value between any 2 adjacent iteration results in the loop is number 2 which is not smaller than the Maximum change option value ( 2 ), so the loop will stop only when the Maximum iterations value ( 100) is reached.