The SCAN function applies a custom calculation to each element in a given array or range and returns an array that contains the intermediate values created during the scan. SCAN can be used to generate running totals, running counts, and other calculations that show intermediate results. 


Scan array and return intermediate results

Return value 

An array of results


  • initial_value - [optional] The initial value of the accumulator.
  • array - The array to be scanned.
  • lambda - The custom LAMBDA function to apply.

How to use 

The SCAN function applies a custom calculation to each element in a given array and returns an array that contains each intermediate value created during the scan. SCAN can generate running totals, running counts, and other calculations that create intermediate or incremental results. The results returned by SCAN are the value of an "accumulator" at each step in the process. 

Like the REDUCE function, SCAN iterates over all elements in an array and performs a calculation on each element while updating the value of an accumulator. However, while REDUCE returns a single value, SCAN returns an array of values. 

The SCAN function takes three arguments: initial_value, array, and lambda. Initial_value is the initial seed value to use for the first result. Initial_value is optional. Array is the array to scan and lambda is a custom LAMBDA function to perform on each element of array.

LAMBDA structure

SCAN uses the LAMBDA function to apply the required calculation. The LAMBDA is applied to each value, and the result from SCAN is an array of results with the same dimensions as the original array. The structure of the LAMBDA used inside of SCAN looks like this:


The first argument, a, is the accumulator used to create intermediate values. The accumulator begins as the initial_value provided to SCAN and changes as the SCAN function loops over the elements in the array and applies a calculation. The v argument represents the value of each element in the array. The calculation is a formula that refers to both the accumulator (a) and value (v). The result of the calculation determines the value of the accumulator at that point in the scan, and the final result from SCAN is an array that contains all accumulator values created during the scan.

Note: SCAN returns the value of the accumulator when each element in the array is processed. The result is an array of "intermediate" values. To process each element in an array individually and return an array of non-intermediate results, see the MAP function


In the formula below, SCAN is used to create a running total of an array with three values:

=SCAN(0,{1,2,3},LAMBDA(a,v,a+v)) // returns {1,3,6}

In the LAMBDA function, a is the initial_value given to SCAN (zero), and v represents the individual elements in array. The LAMBDA runs one time for each element in array, and at each iteration, the value of v is added to the accumulator. SCAN returns the array {1,3,6} as a final result. 

In the worksheet shown above, the formula in D5 is:


The result is a running sum of values in the range B5:B16.

Text values

To work with text values, set the initial_value to an empty string (""). The formula below creates a running concatenation of an array:

=SCAN("",{"a","b","c"},LAMBDA(a,v,a&v)) // returns {"a","ab","abc"}

Notice that because the incoming array has three values, the resulting array also has three values.

Dave Bruns Profile Picture

AuthorMicrosoft Most Valuable Professional Award

Dave Bruns

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.