Total row includes itself
=SUM(B2:B100) in B100 includes B100. Use =SUM(B2:B99) or put the total in B101.
What is a circular reference in Google Sheets?
A circular reference happens when a formula chain loops back to the cell you are editing, directly or through other cells. Google Sheets normally blocks this with a warning and may show zero or an error until you break the loop or enable iterative calculation. Most circles in real files are mistakes, not intentional models.
When to use it
You rarely want a circular reference on purpose. Learn the concept so you can fix accidental loops in totals rows, running balances, and summary cells that include themselves.
When to skip it
Do not enable iterative calculation to silence warnings until you verify the loop is intentional and bounded with iteration limits.
How it works
-
1
Sheets highlights the cell and shows a circular dependency warning in the status area.
-
2
Trace precedents and dependents from the formula menu to see which arrows form the loop.
-
3
Break the loop by moving the total to a different cell, using SUM on rows above only, or a helper column.
-
4
If the model truly needs a loop, enable iterative calculation with documented iteration caps.
-
5
Check merged cells and full-column references that accidentally pull the summary into its own SUM.
-
6
After fixing, reload the tab and confirm the warning disappeared for all editors.
Examples in Google Sheets
Running balance slip
Balance in D5 references D5 plus transaction. Move prior balance to D4 only and reference D4 explicitly.
Named range too wide
Named range Revenue includes the cell that computes total revenue from that same name.
Next step for Circular reference
Jump to a related Better Sheets resource, or unlock the full tutorial library.
Better Sheets resources
Common mistakes
- Ignoring the warning because the number looks close enough while the model is unstable.
- Using iterative calc on shared templates without a README note for finance reviewers.
- Hiding the circular cell on a white-on-white font color instead of fixing logic.
- Copying Excel circular models without re-checking row layout in Sheets.
- Assuming IMPORTRANGE caused the circle when the loop is entirely local.
Frequently asked questions
- How do I find a circular reference?
- Click the warning, use trace precedents, and walk the arrows until you see the loop back to the start cell.
- Circular reference but no error?
- Iterative calculation may be on, or the cell shows 0 until the engine resolves or fails the loop.
- Circular reference across sheets?
- Yes. Sheet1!A1 can depend on Sheet2!B1 which depends on Sheet1!A1. Trace across tabs carefully.
- ARRAYFORMULA circular?
- A spilled array must not depend on cells inside its own spill block. Restructure with a helper column.
- Fix without iterative calc?
- Usually yes. Separate input, calculation, and output cells so no formula reads its own result cell.
- Circular reference warning for viewers?
- Editors see warnings. Fix before publishing templates clients clone.
- Does Google Sheets allow intentional circles?
- Only with iterative calculation enabled and tuned. Excel users may expect different defaults.
- Scripts cause circular refs?
- Scripts write values that formulas read, but the warning is about formula-to-formula loops in the grid.
Related Tutorials
Watch how Circular reference works
How do I reference a different spreadsheet in Apps Script?
Created a GPT function in Sheets
Related blog posts
Guides that explain Circular reference in more depth.
How to Gain Confidence in Google Sheets
Building confidence in Google Sheets, and yourself is essential. It's benefits include: streamlining your workflow, ensuring data accuracy, and maximizing your productivity. If you’ve ever felt overwhelmed or unsure about using Google Sheets, this guide is here to help. Let’s explore some practical tips and strategies to boost your confidence and become a Google Sheets pro. I'll show you where you can find help inside and outside of Google Sheets. 👍Better Sheets Members can email me anytim...
Read post →How to reference a different Google Spreadsheet in Apps Script?
Transferring Data Between Cells in Different Spreadsheets
Read post →Beginners Make This Spreadsheet Mistake All The Time!
Today I'm going to explain the most fundamental mistake beginners make when working in spreadsheets. No matter what you use – Google Sheets, Microsoft Excel, Apple Numbers, or Libra Office – you might be making this mistake.
Read post →Related glossary terms
Iterative calculation
Iterative calculation lets formulas that reference their own cells converge through repeated passes instead of stopping at an error. You set a maximum number of iterations and a convergence threshold in File > Settings > Calculation. It is an advanced escape hatch for intentional feedback loops, not a fix for accidental typos.
Read guide →Recalculation
Recalculation is when Google Sheets recomputes formula results after something changes. Most functions update when their input cells change. Volatile functions like NOW, RAND, and OFFSET recalculate more often. Large workbooks may feel slow when thousands of formulas refresh at once.
Read guide →Google Sheets formula syntax
Every formula starts with = followed by a function name and arguments in parentheses. Ranges use A1 notation, text sits in quotes, and operators like + and & combine values. Sheets recalculates when inputs change, so syntax errors show as #NAME? or #ERROR! in the cell.
Read guide →#NAME? error
#NAME? means Google Sheets does not recognize a name in your formula. That can be a misspelled function, a named range that does not exist, text used without quotes, or a function name from the wrong language locale.
Read guide →Done reading about Circular reference?
Membership unlocks 636+ tutorials, unlimited generators, and every template. Practical lessons. Zero fluff.
Need this once
Jump to a free tool or a single tutorial for this topic.
Learning Sheets for real
Unlock the full library, generators, and templates with membership.