What you will build
Find and break a circular reference without destroying the data needed by the calculation. This lesson uses fictional sample data so you can reproduce each action.
Learning objective
Complete the steps using the dataset below, explain why the feature or formula is used, and recognize issues when inputs change.
Prepare the sample data
Paste this example as tab-separated values beginning at A1 on a new worksheet:
Practice in your own copy
Make your own Google Sheets copy with the same data, formulas, formatting, filter and frozen header. The original remains unchanged.
Sign in to Google. If the original is not publicly shared, download the full workbook and import it into Google Sheets, or paste the example data into your own sheet.
Open the sample workbook in Google Drive (access required) ↗
Work through the example
Step 1: Reproduce the cycle with an incorrect example: enter =D2+B2 in D2 and press Enter. D2 refers to itself, so Google Sheets returns a #REF! error caused by the circular dependency.
Step 2: Clear the broken formula from D2. Keep Price in B2 and Qty in C2 as independent source inputs; do not make either source cell depend on the result cell.
Step 3: Use H2 as a separate result cell, enter =B2*C2, and press Enter. With B2 = 12 and C2 = 2, H2 displays 24. The formula reads only source cells, so it does not create a cycle.
Formula to try
Use H2 as a separate result cell and enter the formula below. With B2 = 12 and C2 = 2, H2 must return 24.
=B2*C2Checkpoint
| Check | Cell / formula | Expected result |
|---|---|---|
| Source inputs | B2 = 12; C2 = 2 | Keep unchanged |
| Corrected formula | H2 contains =B2*C2 | 24 |
| Circular example | D2 contains =D2+B2 | Do not use; D2 refers to itself |
The checkpoint passes when D2 is cleared, H2 displays 24, and the formula bar shows =B2*C2; B2 and C2 remain independent source inputs.
Common mistake and correction
If H2 is not 24, verify B2 = 12, C2 = 2, and H2 contains =B2*C2. If Google Sheets still reports a circular dependency, check whether B2 or C2 contains a formula that refers back to H2. Iterative calculation is appropriate only for models intentionally designed to iterate.
Independent practice
After following the example, complete this variation without editing the source data: Explain why typing =D2+B2 into D2 creates a cycle and replace it with a source-only expression.
Key takeaways
- ✓A circular dependency occurs when a formula depends directly or indirectly on its own result cell.
- ✓Keep source cells independent from the result cell to break the dependency cycle.
- ✓With B2 = 12 and C2 = 2, =B2*C2 in H2 must return 24.
- ✓Use iterative calculation only for models intentionally designed to iterate; do not use it to mask formula mistakes.
Check your understanding
Answer four questions about the practice sheet.
Continue learning
Previous lesson: Fix formula parse errors; Next lesson: Fix IMPORTRANGE permission errors.
Comments
No comments yet. Be the first to join the discussion.