What is SCAN in Google Sheets?
SCAN is like REDUCE but returns every intermediate accumulator in a spilled column. It powers running totals, cumulative text builds, and step-by-step counters without dragging cumulative formulas.
When to use it
Use SCAN for running balance columns, cumulative revenue, or showing how a fold evolves across a list.
When to skip it
Skip SCAN when you only need the final aggregate. REDUCE or SUM is simpler.
How it works
-
1
Provide starting accumulator value matching the result type you need.
-
2
Pass the array to walk through in order.
-
3
Define LAMBDA(acc, value, next_acc) for each step.
-
4
SCAN spills one row per array element with the accumulator after each step.
-
5
Align output beside source data for chart-friendly running series.
-
6
Guard blanks in LAMBDA so running math does not break mid-column.
Examples in Google Sheets
Running revenue
=SCAN(0, C2:C, LAMBDA(acc, v, acc+v)) cumulative sums sales down the column.
Running max
=SCAN(0, B2:B, LAMBDA(acc, v, MAX(acc, v))) tracks peak value seen so far.
Growing text log
=SCAN("", A2:A, LAMBDA(acc, v, acc & v & "; ")) builds a semicolon-separated trail.
Better Sheets resources
Common mistakes
-
Expecting SCAN to return one cell like REDUCE.
-
Starting accumulator incompatible with later math, such as text plus number.
-
Including header cell in the scanned range.
-
Using SCAN for simple running sum when a basic cumulative reference would confuse fewer teammates.
-
Not leaving spill room beside long columns.
Frequently asked questions
- SCAN vs running SUM?
- SCAN with LAMBDA allows custom rules each step, not only addition.
- SCAN output size?
- Same length as the input array, one accumulator per element.
- SCAN and charts?
- Pair running SCAN output with dates for line charts of cumulative metrics.
- SCAN vs REDUCE?
- SCAN shows all steps. REDUCE shows only the end.
- Blank cells in SCAN?
- Treat explicitly in LAMBDA or FILTER blanks first.
- Can SCAN go horizontal?
- Input orientation follows your array; output spills down by default.
- SCAN performance?
- Long arrays with heavy LAMBDA slow recalc like other array helpers.
Related Tutorials
Watch how SCAN works
Scrape Google Maps
Automatic Screenshots in Apps Script
Search Every Function in Google Sheets
Tips to Navigating Thousands of Lines of Code In Apps Script
How do I reference a different spreadsheet in Apps Script?
Related blog posts
Guides that explain SCAN in more depth.
What's Better Than Google Sheets?
Nothing is better than Google Sheets. You just need to learn more about the unlimited power of Google Sheets. Functions, Formulas, Apps Script and more.
Read post →Explore Coding in Google Sheets
Did you know you can actually code in Google Sheets?
Read post →Getting Started Coding in Apps Script
What you need to know to access Apps Script inside Google Sheets. Code in Google Scripts. Automate business processes and more.
Read post →Related terms
REDUCE
REDUCE walks an array left to right, carrying an accumulator value updated by a LAMBDA on each step. It can implement custom aggregates, text joins with rules, or conditional running logic that SUM alone cannot express.
Read guide →MAP
MAP walks aligned arrays element by element and applies a LAMBDA to each position. Pass one array for unary transforms or several arrays for element-wise combinations. It is the modern replacement for many ARRAYFORMULA column operations.
Read guide →LAMBDA
LAMBDA defines an anonymous function inside a formula. You name parameters, write the calculation, then pass arguments when you call it. Combined with MAP, BYROW, or a named LAMBDA in the Name manager, it replaces fragile copy-paste logic across big tables.
Read guide →MAKEARRAY
MAKEARRAY creates a grid of specified height and width, computing each cell with a LAMBDA that receives row and column indexes. It builds lookup tables, sensitivity matrices, and coordinate-driven templates without manual fill.
Read guide →Done reading about SCAN?
Membership unlocks 600+ 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.