The Excel SCAN function walks through an array carrying an accumulator, applies a LAMBDA at each step, and — here’s its signature move — returns the array of every intermediate result. One formula produces a whole running-total column that resizes itself with the data. Its sibling REDUCE does the identical walk but keeps only the final value; SCAN shows the journey, REDUCE shows the destination.
Syntax
| Argument | Description | |
|---|---|---|
initial_value | Optional | The accumulator’s starting value — 0 for sums, 1 for products, "" for text building. Omitted, it defaults from the first element, which can surprise you; stating it is good practice. |
array | Required | The range or array to walk through. SCAN’s output has exactly the same shape. |
lambda | Required | A LAMBDA taking two parameters: the accumulator (the running result so far) and the current value. Whatever it returns becomes both this position’s output and the next step’s accumulator. |
Available in: Excel for Microsoft 365 (and Excel for the web) only — it is not in Excel 2021. Older versions show #NAME?. The result is a dynamic array that spills to match the input’s shape.
Running totals: the classic SCAN
Monthly deposits sit in B2:B6. One formula in D2 produces the entire balance column:
| A | B | D | ||
|---|---|---|---|---|
| 1 | Month | Deposit | Balance | |
| 2 | Jan | 500 | 500 | |
| 3 | Feb | 300 | 800 | |
| 4 | Mar | 450 | 1,250 | |
| 5 | Apr | 200 | 1,450 | |
| 6 | May | 350 | 1,800 |
Each step the LAMBDA receives the balance so far (acc) and the new deposit (v), returns their sum, and that sum is recorded and passed forward. Add a June row to the source and the spill grows by itself — no formula to drag down.
The old-school equivalent =SUM($B$2:B2) dragged down a column still works, but it is one formula per row and breaks when rows are inserted above the anchor.
Try it: interactive SCAN demo
Edit the comma-separated deposits and watch SCAN rebuild the running total — and note what REDUCE would keep from the same walk.
Beyond sums — and SCAN vs REDUCE
Change the LAMBDA and SCAN computes any cumulative sequence:
SCAN vs REDUCE is the question everyone asks. Both walk the array with an accumulator and the same LAMBDA shape; the difference is purely what they return:
Want the whole trail — balances, cumulative counts, running records? SCAN. Want one answer at the end of the walk? REDUCE. For element-wise transforms with no memory between steps, that’s MAP. Wrap any of them in LET when the pieces get long.
Errors & common pitfalls
#NAME? — not an Excel 365 workbook. SCAN exists only in Excel for Microsoft 365 and Excel for the web — not Excel 2021, not 2019. There is no non-365 equivalent short of helper columns.
#VALUE! — the LAMBDA has the wrong shape. SCAN’s LAMBDA must take exactly two parameters — accumulator first, then value. One parameter or three, and the call fails.
#SPILL! — no room for the output. SCAN returns an array the same size as its input; anything sitting in the landing zone blocks it. Clear the cells below/beside the formula.
Pitfall: skipping initial_value. It is technically optional, but relying on the default makes the first step behave differently from the rest. State it: 0 for sums, 1 for products, "" for text.
Pitfall: 2-D ranges walk row by row. On a multi-column range, SCAN runs left-to-right then top-to-bottom, carrying the accumulator across row breaks, and returns the same 2-D shape. For per-row results, combine BYROW with a per-row SCAN or restructure with TOCOL first.
Practice workbook
Frequently asked questions
What's the difference between SCAN and REDUCE?
How do I make a running total with SCAN?
=SCAN(0, B2:B100, LAMBDA(acc, v, acc + v)) — start at 0, add each value, and the formula spills the balance after every row. It grows automatically when the source range grows.Which Excel versions support SCAN?
What does the LAMBDA inside SCAN need to look like?
LAMBDA(acc, v, calculation). The calculation's result is written to the output and becomes the next step's acc. A different parameter count fails the call.Can SCAN do a running product or running maximum?
=SCAN(1, rng, LAMBDA(a,v, a*v)) compounds a product; =SCAN(0, rng, LAMBDA(a,v, MAX(a,v))) tracks the record high so far.How does SCAN handle a multi-column range?
Master functions like this in one day
This page covers one function. Our Excel Formulas and Functions class covers the 30 that matter most — live, hands-on, taught by professionals in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class