# SCAN function in Excel

> SCAN works like REDUCE, passing a running result and each value to a LAMBDA, but it returns the running result at every step instead of just the last one.

Source: https://spreadsheetpractice.com/functions/scan · Updated October 28, 2026 · Formulas and results on this page were run by the Spreadsheet Practice formula engine on October 30, 2026

## Quick answer

- **What it does:** SCAN works like REDUCE, passing a running result and each value to a LAMBDA, but it returns the running result at every step instead of just the last one.
- **Syntax:** `=SCAN([initial_value], array, lambda)`
- **Most common use:** `=SCAN(0,B2:B5,LAMBDA(total,v,total+v))` returns $1,200 (A running total, spilled down)
- **Available in:** Excel 2024 and Microsoft 365
- **Category:** [Dynamic array functions](https://spreadsheetpractice.com/functions/category/dynamic-arrays)
- **Returns:** An array the same size as the input, with the running result at each value.
- **Google Sheets:** Available in Google Sheets.

## What it does

SCAN works like REDUCE, passing a running result and each value to a LAMBDA, but it returns the running result at every step instead of just the last one. That makes it a one-formula running total, running maximum or running balance that spills next to your data.

## Syntax

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

| Argument | Required? | What it is |
|---|---|---|
| `initial_value` | Optional | The starting value of the running result; 0 if left empty. |
| `array` | Required | The values to walk through, row by row. |
| `lambda` | Required | A LAMBDA with two inputs: the running result so far, and the next value. |

## Examples

Each result was calculated by running the formula on the sample sheet.

| Cell | Formula | Result | What it shows |
|---|---|---|---|
| C2 | `=SCAN(0,B2:B5,LAMBDA(total,v,total+v))` | $1,200 (spills 4 × 1) | A running total, spilled down |
| D2 | `=SCAN(0,B2:B5,LAMBDA(m,v,MAX(m,v)))` | $1,200 (spills 4 × 1) | The best month so far |
| F2 | `=INDEX(SCAN(0,B2:B5,LAMBDA(total,v,total+v)),3)` | $3,540 | The running total after March |

### The sample sheet

Every formula above runs on this data:

|  | A | B | C | D | E | F |
|---|---|---|---|---|---|---|
| 1 | Month | Sales | Running total | Best so far |  |  |
| 2 | Jan | $1,200 | $1,200 | $1,200 |  | $3,540 |
| 3 | Feb | $1,350 | $2,550 | $1,350 |  |  |
| 4 | Mar | $990 | $3,540 | $1,350 |  |  |
| 5 | Apr | $1,500 | $5,040 | $1,500 |  |  |

## Common mistakes

| What you see | Why, and how to fix it |
|---|---|
| #CALC! | The LAMBDA returns an array instead of one value. |
| The first running total is off | Start initial_value at 0 for running totals. |

## Tips

- A running balance: =SCAN(opening,B2:B20,LAMBDA(bal,v,bal+v)) with deposits positive and payments negative.

## Related functions

- [REDUCE](https://spreadsheetpractice.com/functions/reduce): REDUCE walks through every value in an array, passing a running result and the next value to a LAMBDA, and returns the final running result.
- [SUM](https://spreadsheetpractice.com/functions/sum): SUM adds numbers.
- [LAMBDA](https://spreadsheetpractice.com/functions/lambda): LAMBDA creates your own function from a formula: name the inputs, then write the calculation, as in LAMBDA(price, rate, price*(1+rate)).

## Questions

### How do I make a running total with one formula?

=SCAN(0,B2:B100,LAMBDA(total,v,total+v)) spills a cumulative sum down the column.

### Does SCAN work with text?

Yes, the LAMBDA can return text, such as joining values as it goes.

## Practice

A graded SCAN exercise ("Running total in one formula") is on [the SCAN page](https://spreadsheetpractice.com/functions/scan). It is checked instantly, then re-checked on changed data.

---

Spreadsheet Practice (https://spreadsheetpractice.com) teaches spreadsheet formulas by practice: every exercise is graded in a live grid and re-checked on changed data.