Quick Navigation

SCAN Function

Excel 365, Excel 2021

Summary

The SCAN function applies a LAMBDA function cumulatively to each element of an array, returning an array containing all intermediate results. Unlike REDUCE which returns only the final result, SCAN provides the complete sequence of accumulated values, making it perfect for creating running totals, cumulative products, and progressive calculations.

Syntax

=SCAN([initial_value], array, LAMBDA(accumulator, value, body))

Parameters

Parameter	Type	Required	Description
initial_value	`Any`	No	Sets the starting accumulator value. Omit for first array element.
array	`Array`	Yes	The array to process cumulatively
lambda	`LAMBDA`	Yes	LAMBDA(accumulator, value, body) defining accumulation logic

Using the SCAN Function

SCAN transforms arrays by applying cumulative operations, building each result based on all previous values. Ideal for running totals, factorials, cumulative sums, or concatenating strings progressively.

Common SCAN Examples

Running Total Calculation

=SCAN(0, B2:B10, LAMBDA(acc, val, acc+val))

Creates cumulative sum starting from 0 across sales data in B2:B10.

Factorial Sequence

=SCAN(1, A1:A5, LAMBDA(acc, val, acc*val))

Generates factorial sequence: 1, 1!, 2!, 3!, 4!, 5! from numbers 1-5.

Cumulative Text Concatenation

=SCAN("", A1:A5, LAMBDA(acc, val, acc&" "&val))

Builds progressive string by concatenating array elements with spaces.

Frequently Asked Questions

SCAN returns all intermediate results as an array; REDUCE returns only the final accumulated value.

No, omit it to use the first array element as the starting accumulator.

Yes, set initial_value to "" for empty string and use & operator in LAMBDA.

Common Errors and Solutions

#VALUE! 'Incorrect Parameters'

Cause: Invalid LAMBDA syntax or wrong parameter count

Solution: Ensure LAMBDA has exactly 3 parameters: accumulator, value, body

#CALC!

Cause: Empty array with no initial_value

Solution: Provide initial_value or ensure array has data

Spill error

Cause: Output area blocked

Solution: Clear space for dynamic array results

Notes

SCAN spills results matching input array size
Use empty string "" for text accumulation
Perfect companion to MAP for element-wise operations
Processes arrays left-to-right by default

Compatibility

Available in: Excel 365, Excel 2021

Not available in: Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel 2007

Content last reviewed: December 9, 2025
Update frequency: As needed
Excel versions tested: Excel 365, Excel 2021