How to Use the SCAN Function in Microsoft Excel for Running Totals and Accumulations

Calculating running totals, cumulative balances, or rolling string concatenations in Microsoft Excel has traditionally required dragging relative formulas (like =SUM($B$2:B2)) down hundreds of worksheet rows. While effective, manual formula copying breaks easily when rows are added, deleted, or sorted. In modern Microsoft 365, Excel includes the SCAN function—a versatile LAMBDA helper that iterates across an array, accumulates intermediate values step-by-step, and spills the entire sequence of running totals into a dynamic array.

Understanding the Syntax of the SCAN Function

The SCAN function takes an initial starting value, traverses an array, and passes each step to a custom LAMBDA formula:

=SCAN([initial_value], array, lambda(accumulator, value, calculation))

• initial_value (optional): The starting seed value for the calculation (typically 0 for running sums or "" for text concatenation). If omitted, Excel defaults to zero.
• array (required): The sequence or range of numbers/text values to be scanned.
• lambda (required): A custom calculation that takes two parameters:
  • accumulator: The running balance accumulated up to the previous step.
  • value: The current item being processed in the array.

Unlike REDUCE—which collapses an array into a single grand total—SCAN outputs every intermediate calculation, creating a complete rolling array.

Practical Example: Generating a Dynamic Running Total

Suppose you have monthly revenue numbers in cells B2:B13. To calculate a cumulative year-to-date total for all months using a single formula in cell C2:

=SCAN(0, B2:B13, LAMBDA(a, v, a + v))

How it evaluates:

1. Month 1 (B2 = 1,000): Accumulator starts at 0. 0 + 1,000 = 1,000.
2. Month 2 (B3 = 1,500): Accumulator is 1,000. 1,000 + 1,500 = 2,500.
3. Month 3 (B4 = 2,000): Accumulator is 2,500. 2,500 + 2,000 = 4,500.

The result spills automatically down column C to cell C13. If you insert new rows or update figures in column B, the running totals recalculate instantaneously without dragging formulas.

Advanced Use Cases: Rolling Text and Conditional Balances

Because SCAN supports any calculation inside its LAMBDA argument, it enables complex cumulative workflows:

1. Cumulative Text Concatenation (Breadcrumbs)

To accumulate an ongoing breadcrumb trail from a list of category steps in A2:A6:

=SCAN("", A2:A6, LAMBDA(a, v, IF(a="", v, a & " > " & v)))

This outputs an expanding hierarchy: Electronics, Electronics > Audio, Electronics > Audio > Headphones.

2. Running Bank Balance with Debits and Credits

If column B lists deposits (positive) and withdrawals (negative) starting with an initial opening balance of £500:

=SCAN(500, B2:B20, LAMBDA(balance, transaction, balance + transaction))

The formula generates an automated ledger ledger balance across all transactions seamlessly.

Comparing SCAN with REDUCE and Traditional SUM

Understanding when to use SCAN versus other Excel functions streamlines financial modelling:

• SCAN vs REDUCE: Use SCAN when you need to see every intermediate step (e.g. running inventory, bank balance ledgers, progress charts). Use REDUCE when you only care about the single final accumulated outcome.
• Performance Advantage: Because SCAN lives in a single cell, it eliminates thousands of redundant range evaluations that occur when using legacy SUM($B$2:B1000) constructs on large enterprise worksheets.