How to Use SCAN Function in Excel + Tips

Hello everyone! Today i will show you how to use SCAN function in Microsoft Excel step by step. Very power formula in Excel 365 and Excel 2024 for make running total (cumulative sum) instantly!

1. What is SCAN Function in Excel?

The SCAN function scans an array or range cell-by-cell and returns an array of intermediate accumulated values at each step.

Think of SCAN function like a running calculator step:

• Row 1: 10 -> Total: 10
• Row 2: 20 -> Total: 30
• Row 3: 15 -> Total: 45

Unlike REDUCE function (which returns only 1 single final number), SCAN returns the full list of running totals for every row!

2. 🛠️ Formula Syntax & Parameters

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

• [initial_value]: Optional. Starting value for accumulated total (usually 0 for numbers or "" for text).
• array: Required. The range of cells you want to process (e.g., A2:A6).
• LAMBDA(accumulator, value, calculation): Required. A custom calculation:
  • accumulator (e.g., a): Stores the running accumulated result so far.
  • value (e.g., v): The current item value in the array.
  • calculation: The operation like a + v or a & " " & v.

3. 📋 Practical Examples

Example 1: Calculate Running Total (Cumulative Sum)

Suppose range B2:B6 has daily sales amounts:

To calculate cumulative running total:

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

👉 Result: Spills all running totals down column C automatically with only 1 formula!

Example 2: Fill Down Blank Cells with Above Value

Have you ever had report with empty merged cells? You can fill down missing values easily!

Suppose A2:A6 has blank spaces:

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

👉 Result: Returns Apple, Apple, Apple, Banana, Banana! Automatically repeats previous value whenever it sees blank cell!

Example 3: Accumulate Text String Step-by-Step

Combine words row by row:

=SCAN("", A2:A4, LAMBDA(a, v, TEXTJOIN(" ", TRUE, a, v)))

If A2:A4 contains Excel, Is, Awesome:

• Row 1: "Excel"
• Row 2: "Excel Is"
• Row 3: "Excel Is Awesome"

💡 Quick Tips for Using SCAN Function

1. Difference between SCAN and REDUCE:
   • SCAN: Returns an array of every step (e.g. [10, 30, 45]). Perfect for running total!
   • REDUCE: Returns 1 final total value (e.g. 45).
2. Initial Value 0 vs "": Always use 0 as initial value when working with numbers/sum. Use "" when working with text/string join.
3. No need helper column: Before SCAN, running total needed formula =SUM($B$2:B2) dragged down. With SCAN(0, B2:B10, LAMBDA(a,v, a+v)), 1 formula handles entire column!
4. Excel Version Requirement: Needs Microsoft 365, Excel 2024, or Excel for Web with LAMBDA support.

📚 Related Resources & Tutorials

• How to Use TAKE Function in Excel
• How to Use CHOOSE Function in Excel
• Top 15 Basic Excel Formulas for Beginners ↗