A to Z of Excel Functions: The SCAN Function
17 March 2025
Welcome back to our regular A to Z of Excel Functions blog. Today we look at the SCAN function.
The SCAN function
This function “scans” an array by applying a LAMBDA to each value and returns an array that has each intermediate value. The syntax is as follows:
SCAN([initial_value], array, lambda)
where:
- initial_value: this is an optional argument and represents the starting value for the accumulator, i.e. the “running total” prompted by the lambda expression
- array: this is a required value and represents the array to be scanned
- lambda: this is also a required value and represents a LAMBDA function called to scan the array, that consists of two parameters:
- accumulator: the returned (aggregated) value from LAMBDA
- value: a value from array.
As a simple example, let’s consider a common problem when working with structured references, i.e. Excel Tables. Imagine I have the following sales for the first six months of the year:
I might wish to create a running total of these sales. One way I have seen people do this is as follows:
This is a horrible “hotch potch” of a formula:
=N(C1) + [@Sales]
It mixes Excel cell referencing (cell C1, because you cannot refer to a value for a different record simply in an Excel Table), structured referencing ([@Sales]) and the N function, in order to treat the numerical value of text as zero [0] and therefore avoid #VALUE! errors when adding amounts together.
It seems to work if values are added:
However, it all goes pear-shaped when values are inserted:
This is where SCAN comes to the rescue. Assuming the Table is also called Sales (not just the field in column B), we can create the formula
=SCAN(0, Sales[Sales], LAMBDA(accumulator, value, accumulator + value))
SCAN “scans” the array (i.e. the Excel Table Sales) by applying a LAMBDA to each value. It then returns an array of results corresponding to the accumulator value returned by the LAMBDA. As stated above, SCAN takes two parameters:
- accumulator: the initial value returned by SCAN and each LAMBDA call
- value: a value from the supplied array.
As above, the initial_value is zero [0] so that the running total calculates correctly.
We’ll continue our A to Z of Excel Functions soon. Keep checking back – there’s a new blog post every other business day.
