Excel’s SCAN Function: The Hidden Gem That Simplifies Running Totals and More

Meet Excel’s SCAN function — one of the most powerful and underrated Dynamic Array functions introduced in Microsoft 365. Once you understand what it does, you’ll never go back to the old methods again.

What Is the SCAN Function?

The SCAN function iterates (or “scans”) through a range of data and returns all intermediate results — not just the final total.

Think of it as Excel’s version of a “running memory.” It keeps track of the cumulative results as it moves through your data.

Syntax:

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

• initial_value → The starting point (e.g., 0 for running totals or 1 for running products).

• array → The range you want to scan.

• lambda → The custom logic that tells Excel what to do with each item (add, multiply, concatenate, etc.).

Why Use SCAN Instead of Other Methods?

Before SCAN, creating cumulative calculations meant using:

• SUM($A$2:A2) or SUMIFS() formulas

• Helper columns or manual fills

• Complex VBA or iterative logic

With SCAN, you can perform the entire process in a single dynamic formula — clean, efficient, and incredibly flexible.

Example 1: Combine Text Dynamically (Simple Example)

Step	Current	New Value
1	Excel
2	is
3	super
4	duper
5	awesome

Formula:

=SCAN("", B2:B6, LAMBDA(a,b, a & " " & b))

Result:

Excel Excel is Excel is super Excel is super duper Excel is super duper awesome

✅ Why it’s cool: You can see the text “build” with each step — no helper column or TEXTJOIN gymnastics required.

Example 2: Running Total by Product (Intermediate Example)

Date	Product	Sales Rep	Sales Amount	Running Total
1/5/2025	Docking Station	Emily Davis	$220.00
1/10/2025	Docking Station	Jade Jarvis	$210.00
1/15/2025	Docking Station	Omar Reed	$250.00
…	…	…	…	…

Formula:

=SCAN(0, E2:E31, LAMBDA(a,b, a+b))

Result:

A full list of running totals that automatically spills down — no dragging, no $A$2:A2 range needed.

💡 Tip: You can nest this inside a FILTER or SORT function to create product-specific cumulative totals on the fly.

Example 3: Running Ledger Balance (Advanced Example)

Date	Description	Debit	Credit	Balance
1/2/2025	Opening week fuel	$45.00		955
1/3/2025	Client payment		$350.00	1305
1/4/2025	Office supplies	$62.00		1243
…	…	…	…	…

Formula:

=SCAN(1000, D5:D21 - C5:C21, LAMBDA(a,b, a+b))

Starts with a $1,000 balance, subtracts debits, adds credits, and outputs the running balance for each transaction — clean, fast, and dynamic.

Why You’ll Love Using SCAN

• No more helper columns — everything in one neat formula

• Dynamic results — automatically expand with new data

• Works with text, numbers, or logic

• Pairs perfectly with LAMBDA — for reusable logic across sheets

Once you start using SCAN, you’ll wonder how you ever worked without it.

DOWNLOAD EXERCISE FILE:

ScanFunctionExamples-01.xlsx

Download XLSX • 13KB