Inner circle reference Excel occurs when a formula directly or indirectly references its own cell.
| Join the channel Telegram of the AnonyViet 👉 Link 👈 |
Circular References in Excel
Direct circular reference
Direct circular referencing in Excel occurs when a formula directly references its own cell.
1. For example, the formula in cell A3 below refers directly to its own cell (A3). This is impossible.
2. Press Enter. The circular reference error message will appear.
3. Click OK. Excel returns 0.
Indirect circular reference
Indirect circular references in Excel occur when a formula indirectly references its own cell. See a simple example below.
1. For example, cell D2 below contains the value 25.
2. Cell F4 refers to cell D2.
3. Cell C5 refers to cell F4.
4. Everything is fine. Now replace the value 25 in cell D2 with the formula shown below.
5. Press Enter. The circular reference error message appears.
6. Click OK. Excel returns 0.
7. On the Formulas tab, in the Formula Auditing group, click Show Formulas.
8. Cell D2 refers to cell C5. Cell C5 refers to cell F4. Cell F4 references back to cell D2.
Conclusion: the formula in cell D2 indirectly refers to its own cell (D2). This is impossible. The formulas in cell C5 and cell F4 also refer back to their own cells.
Find circular references
1. Our sheet contains 2 circular references. 1 direct ring reference (A3) and 1 indirect ring reference (D2, C5 and F4).
2. On the Formulas tab, in the Formula Auditing group, click the down arrow next to Error Checking.
3. Click Circular References. Excel displays the cell address of a circular reference.
4. You can also look at the status bar
5. Fix this circular reference.
To find more circular references, repeat these steps.
6. On the Formulas tab, in the Formula Auditing group, click the down arrow next to Error Checking.
3. Click Circular References.
8. The status bar shows only a single cell address.
