Once you understand SCAN in Excel, you’ll never build formulas the same way

If you’ve ever built a running total in Excel, you’ve probably written something like {=SUM(A$1:A2)} and dragged it down the column again and again. It’s a simple enough approach—until your dataset grows or starts changing frequently. At that point, keeping everything accurate can turn into a hassle. Creating a table in Excel or using dynamic ranges can make things a bit easier, but they don’t completely solve the problem. You still end up copying formulas, expanding ranges, and hoping nothing breaks.

That’s where SCAN comes in. Think about the last time you built a running total. Now imagine doing the same thing with a single formula that automatically expands and updates as your data changes, and it never needs to be copied down the column. That’s the power of SCAN, and it’s only the beginning of what this function can do.

What exactly does SCAN do

Meet the function that thinks like you do

The simplest way to think about SCAN is that it applies a custom calculation to each element in a range/array and returns every intermediate result it produces along the way. If that sounds abstract, don’t worry. It’ll make perfect sense once you see it in action.

Imagine you have monthly sales figures in cells A2:A8, and you want a running total. The traditional approach would be to enter the following formula in B2, then copy it down to B8:

=SUM(A$2:A2) 

That’s six cells and six formulas. If someone later inserts a row or your data updates, you’re back to adjusting ranges or rechecking your work.

With SCAN, you can replace all that with a single formula in B2:

=SCAN(0, A2:A8, LAMBDA(a, v, a + v))

When you press Enter, Excel automatically spills the full running total down the column. It’s one formula but six results.

Here’s the basic syntax for SCAN:

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

In this formula, Initial_value is your starting point. For a running total, that’s typically 0, but it can be any number, or even text.

If your initial value is text, wrap it in double quotes (“”).

Array is the range of data you want to process. In this example, that’s A2:A7, your monthly sales. However, instead of using cell references, you can reference a table, like Table1.

Finally, LAMBDA(accumulator, value, and body) defines the logic of your calculation. In plain English, SCAN walks through your range one item at a time, applies your calculation, remembers the result, and uses it in the next step. The accumulator (a) stores the running result, the value (v) is the current item, and the body (combination of a & v) defines what happens at each step.

In the example above, the body is a + v, which creates a cumulative sum. But you can use any operation you like:

• Multiplication: a * v
• Subtraction: a - v
• Text concatenation: a & ", " & v
• Conditional logic: IF(v > 100, a + 1, a)

While SCAN doesn’t replace functions like SUM or SUMIFS in every scenario, it’s a powerful tool when your logic needs to build progressively over a range.

SCAN is available in Excel for Microsoft 365. If you’re using a version earlier than Excel 2024, this function won’t be available.

How SCAN changes the way you build formulas

From repetition to progression

As you’ve already seen, the running total example only hints at what SCAN can do. In practice, SCAN shifts your approach to building formulas in three major ways:

First, it moves you from copying formulas down a column to writing a single, dynamic one. Because SCAN returns a dynamic array, your results automatically expand to match your data, eliminating the need to drag formulas or adjust ranges when your dataset grows. Second, it transforms your basic calculations into multistep logic. SCAN can handle progressive calculations where each result depends on the previous one, allowing you to track every intermediate value as your data evolves. Finally, SCAN works with the entire array from the start, meaning that when you add or remove data, your formula immediately adjusts to reflect those changes.

You’ll notice these differences as soon as you start using SCAN in real datasets. For instance, imagine a table that tracks the daily score and status of a project. Here are three practical ways to use SCAN in that scenario:

Running maximum

This is one of the simplest ways to track a progressive state. The goal is to show the highest score achieved up to each date.

=SCAN(0, B2:B9, LAMBDA(a, v, MAX(a, v)))

The logic is straightforward: at each step, SCAN compares the current score (v) with the running maximum (a). If the new score is higher, it replaces the previous maximum. The accumulator (a) always holds the largest score encountered so far.

Running count of active days

Instead of summing scores, you can count how many times a specific condition has been met—in this case, how often the project status is set to Active.

=SCAN(0, C2:C9, LAMBDA(a, v, IF(v="Active", a + 1, a)))

Here, SCAN checks the current status (v). If it equals “Active,” it adds 1 to the accumulator (a); otherwise, it keeps the count unchanged. The result is a running tally of active days.

Conditional running product

This example combines multiplication and conditional logic. Suppose you want to calculate a running product of daily scores, but only on days when the status is Active. When the status switches to Inactive, the product should reset to its default value of 1.

Since multiplication requires an initial value of 1, the formula starts there:

=SCAN(1, C2:C9, LAMBDA(a, v, IF(v="Active", a * INDEX(B:B, ROW(v)), 1)))

First, SCAN checks the current status (v) from column C. If the status is Active, it multiplies the previous product (a) by the corresponding score from column B, which is pulled in using the INDEX function. However, if the status is Inactive, it resets the product to 1, pausing accumulation until the next Active day.

This kind of formula can be very useful in business scenarios. For instance, imagine a sales team earning a commission multiplier that increases daily as long as the account remains compliant. If an account passes its daily review (Active), its sales amount contributes to the ongoing product. If it fails (Inactive), the multiplier resets to 1. That way, your account officers will be encouraged to ensure compliance so they don’t reset their bonus streak.

Let SCAN do the work for you

By now, you’ve seen how powerful SCAN can be and how it transforms traditional formulas into scalable, spillable logic that grows with your data. Use it whenever you need to see the progression of a calculation rather than just the final result. This includes financial projections where each period builds on the last, inventory tracking that updates stock levels with every sale or delivery, and quality control charts that monitor cumulative defects.

The easiest way to know when SCAN is the right tool is to listen for this thought: “I need to use the result from the previous row in this calculation.” When that’s the case, you’re squarely in SCAN territory, and Excel will handle the rest.