Ravindra BagaleCourses & study guides Track your progress

Excel SCAN Function

By · Last updated:

Chala mitrano — today we learn SCAN. Read Why → What → How, then try the practice list.

Version note. Microsoft 365 with LAMBDA.
Fictional sample data for learning. Fictional daily sales and cafe covers.

Purpose

Like REDUCE, but spills every intermediate accumulator value (running total style).

Return value

An array of running results, one per input item.

Syntax

SCAN([initial_value], array, lambda)

Arguments

ArgumentRequired?Description
initial_valueOptionalStarting accumulator.
arrayRequiredValues to scan.
lambdaRequiredLAMBDA(acc, value) → new acc (returned at each step).

Version

Microsoft 365 with LAMBDA.

How to use — Why → What → How

Why: Managers love running totals month by month — SCAN spills them in one formula.

What: Each step stores the accumulator so far into the spill grid.

How — numbered steps:

  1. Try =SCAN(0, B2:B7, LAMBDA(a,v, a+v)).
  2. Compare to a helper column with expanding SUM.
  3. Use charts on the SCAN output for a trend of the running total.
  4. Combine with ROUND for currency.
  5. Clear spill blockers below.

Ravindra Bagale's Tip

SCAN = “show every step”; REDUCE = “show only the end”.

Ravindra Bagale's Tip

Put SCAN in its own column so #SPILL! is easy to see.

Worked examples

1) Running sales total (₹)

Daily sales.

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

Result: Running totals

What it means: Each row is sum so far.

2) Running count of orders

Ones and zeros.

Formula: =SCAN(0, C2:C12, LAMBDA(a,v, a+v))

Result: Cumulative count

What it means: Same pattern.

3) Running balance after expenses

Start 5000.

Formula: =SCAN(5000, D2:D6, LAMBDA(a,v, a-v))

Result: Balance after each spend

What it means: Initial cash then subtract.

4) UK cafe running covers (£)

Covers per hour.

Formula: =SCAN(0, E2:E5, LAMBDA(a,v, a+v))

Result: Cumulative covers

What it means: Works with any numeric list.

5) Running max

Track peak.

Formula: =SCAN(0, F2:F10, LAMBDA(a,v, MAX(a,v)))

Result: Peak so far

What it means: Not only sums — any fold.

Common errors

#SPILL!: Output blocked.
#CALC!: LAMBDA parameters wrong.

Practice tasks (10+)

  1. Task 1. Build a running total for 12 months.
    Hint: try on a tiny range first
  2. Task 2. Running balance with opening cash.
    Hint: try on a tiny range first
  3. Task 3. Running max and running min side by side.
    Hint: try on a tiny range first
  4. Task 4. Chart the SCAN output.
    Hint: try on a tiny range first
  5. Task 5. Compare SCAN vs partial SUM.$A$2:A2 style.
    Hint: try on a tiny range first
  6. Task 6. SCAN percentages of a fixed total using LET.
    Hint: try on a tiny range first
  7. Task 7. Explain SCAN vs REDUCE in one sentence.
    Hint: try on a tiny range first
  8. Task 8. Fix a #SPILL! under SCAN.
    Hint: try on a tiny range first
  9. Task 9. SCAN with ROUND(...,2) for ₹.
    Hint: try on a tiny range first
  10. Task 10. Bonus: SCAN of MAP output.
    Hint: try on a tiny range first

Quick quiz

Q1. SCAN spills intermediate totals.
Q2. REDUCE and SCAN use a similar LAMBDA shape.
Q3. SCAN needs Excel 365.
Q4. #SPILL! means printers failed.

Answers: Q1=True, Q2=True, Q3=True, Q4=False

Related: LAMBDA · LET · GROUPBY · Function hub · Learning path