Logical

SCAN Function in Excel

Scans an array by applying a LAMBDA to each value and returns an array of intermediate values.

Syntax

  • =SCAN([initial_value], array, lambda)

Arguments

  • initial_value (optional): Starting value
  • array (required): Array to scan
  • lambda (required): LAMBDA(accumulator, value) to apply

Examples

  • =SCAN(0, A1:A5, LAMBDA(a,b, a+b)) - Running total - Result: Cumulative sums

Excel SCAN function documentation

  • SCAN returns an array of intermediate values from a REDUCE-style fold — running totals etc.
  • Syntax: =SCAN(initial_value, array, LAMBDA(accumulator, value, calculation)). Microsoft 365.
  • Shows each step of accumulation — running sum, running max, compound product trace.
  • Related: [REDUCE](/functions/reduce/), [SEQUENCE](/functions/sequence/), [SUM](/functions/sum/).

SCAN syntax and spill

  • Same LAMBDA signature as REDUCE but outputs array matching scan length.
  • initial_value seeds first accumulator state.
  • Spills down — clear blocked cells.
  • Pair with charts for cumulative line visuals.

Step-by-step: running balance column

  • Step 1 — Transactions in B2:B100.
  • Step 2 — =SCAN(0, B2:B100, LAMBDA(a,v, a+v)) running sum spill.
  • Step 3 — Place formula adjacent to table in empty column.
  • Step 4 — Compare cumulative sum helper copied down.
  • Step 5 — Bank rec: start with opening balance as initial_value not 0.

SCAN vs REDUCE vs manual running total

  • SCAN — full trajectory array.
  • REDUCE — final value only.
  • Manual — fragile when rows inserted.
  • 365 dynamic array best practice for live ledgers.

Worked examples to copy

  • Running sum: =SCAN(0, amounts, LAMBDA(a,v, a+v)).
  • Running max: =SCAN(-1E+99, vals, LAMBDA(a,v, MAX(a,v))).
  • Compound: =SCAN(1, factors, LAMBDA(a,v, a*v)).
  • With opening: =SCAN(opening, flows, LAMBDA(a,v, a+v)).

People also ask

  • SCAN Excel 2019? — No; 365.
  • SCAN vs SUM cumulative chart? — SCAN feeds data; chart visualizes.
  • SCAN length? — Same as array argument length (plus spill rules).
  • SCAN errors in lambda? — Propagate — test on small array first.

Common errors

  • Requires Excel 365
  • LAMBDA needs 2 parameters

Use cases

  • Running totals
  • Cumulative calculations
  • Progressive results

Frequently asked questions

  • What is SCAN and how is it different from REDUCE? Both process arrays with an accumulator, but SCAN returns ALL intermediate values while REDUCE returns only the final result. =SCAN(0, {1,2,3}, LAMBDA(a,b,a+b)) returns {1,3,6} (running totals). REDUCE would return just 6.
  • How do I create a running total with SCAN? Use =SCAN(0, A1:A10, LAMBDA(acc, val, acc+val)). The accumulator starts at 0, each step adds the current value. Result is cumulative sum at each position. Great for running balances, cumulative sales, etc.
  • Can SCAN do more than running totals? Yes! Running max: LAMBDA(a,b, MAX(a,b)). Running product: LAMBDA(a,b, a*b). Running count of positives: LAMBDA(a,b, a+IF(b>0,1,0)). Any calculation that builds on previous results works with SCAN.

Editorial review

  • Reviewed by Excel.Directory Editorial Team. Updated May 2026.

When to use SCAN

  • Running totals — common Logical scenario for SCAN.
  • Cumulative calculations — common Logical scenario for SCAN.
  • Progressive results — common Logical scenario for SCAN.

SCAN in the Logical category

  • Browse all Logical functions at /categories/logical/ for related formulas.
  • SCAN syntax: =SCAN([initial_value], array, lambda)
  • initial_value (optional): Starting value
  • array (required): Array to scan
  • lambda (required): LAMBDA(accumulator, value) to apply
  • Confirm SCAN arguments match the syntax shown above before filling down.
  • Lock table and range references with $ when copying formulas across rows or sheets.

Formula checklist before you copy down

  • Confirm SCAN arguments match the syntax shown above before filling down.
  • Lock table and range references with $ when copying formulas across rows or sheets.
  • If results look wrong, check for text stored as numbers and invisible spaces with TRIM.
  • Spot-check three known input rows manually against expected output.

Related Excel functions

  • LAMBDA (/functions/lambda/): Creates custom, reusable functions that can be called by a friendly name.
  • MAP (/functions/map/): Returns an array formed by mapping each value in the array(s) to a new value using a LAMBDA.
  • REDUCE (/functions/reduce/): Reduces an array to an accumulated value by applying a LAMBDA to each value.

Errors to watch for

  • Requires Excel 365 — review causes on linked error pages in the directory.
  • LAMBDA needs 2 parameters — review causes on linked error pages in the directory.

Copy-paste audit workflow

  • Enter SCAN on three test rows with known expected output documented on a QA tab.
  • Fill down only after absolute references are locked on lookup tables and rate tables.
  • Compare against manual calculation or a calculator for financial and statistical functions.
  • Search this directory for comparison guides when choosing between similar functions in the same category.

SCAN worked examples to copy

  • =SCAN(0, A1:A5, LAMBDA(a,b, a+b)) — Running total. Expected result: Cumulative sums.

SCAN reference summary for crawlers and offline review

  • SCAN belongs to the Logical category in Excel. Scans an array by applying a LAMBDA to each value and returns an array of intermediate values.
  • Full syntax: =SCAN([initial_value], array, lambda). Open /functions/scan/ for parameters, FAQs, and related pages.
  • Common mistakes: Requires Excel 365; LAMBDA needs 2 parameters
  • Pair this function with comparison guides when another Excel formula might fit the same task better.
  • Review fix-excel-formula-errors when unexpected errors appear after upgrading Excel or sharing across locales.