SCAN in Google Sheets

What is SCAN in Google Sheets?

The SCAN function in Google Sheets iteratively applies a LAMBDA function to each element in a range. It returns an array of intermediate results at each step. It processes values in order, either by row or by column, and keeps track of accumulated values as it moves through the array. Thus, one can perform running calculations like cumulative sums, products, or custom aggregations, without having to write repetitive formulas.

For example, let’s say we some values in cells A1:A4. To calculate a running total for each row, we can use the following formula:

=SCAN(0, A1:A4, LAMBDA(acc, val, acc + val))

Here, acc is the accumulator which starts at zero. val represents each value in the range as SCAN iterates downward. The formula returns the result shown above which are the cumulative sums of the values in each step.

Syntax

The SCAN function in Google Sheets formula is as follows:

=SCAN(initial_value, array_or_range, lambda)

1. initial_value: The starting value of the accumulator before the scan begins.
2. array_or_range: The input array or range over which the LAMBDA function is applied.
3. lambda: A LAMBDA function with two parameters, Usually it is LAMBDA(accumulator, value, formula_expression). The accumulator represents the running total and the value corresponds to each element in the array. Named functions can also be used here.

How To Use SCAN Function in Google Sheets?

Now, let us look at how the SCAN function in Google Sheets can be used. We will give you a simple example with an explanation of how it works. The function can be entered in Google Sheets in two ways:

• Manually enter SCAN
• Enter through the Google Sheets menu bar

The SCAN function is especially useful when working with cumulative data. This is useful in scenarios like calculating running totals, progressive averages, or cumulative profits. It allows you to perform calculations across a range without copying formulas into each row manually.

Let’s explore how to use it with a simple example.

Entering SCAN Manually

With the help of an example, we’ll calculate the running total of monthly sales of a store. Let us see how we can enter SCAN in Google Sheets manually.

Step 1: To enter the function manually, we must supply the initial value, the range, and the LAMBDA function.

Enter the sales data into a Google Sheet, as shown above in cells A1:B6.

Step 2: Click on an empty cell and type the following formula manually:

1. Begin with the = sign followed by the function name and open parentheses.
2. Enter the initial value (starting accumulator), the range, and the LAMBDA function with arguments.
3. Close the parentheses for both LAMBDA and SCAN.

=SCAN(0, B2:B5, LAMBDA(acc, val, acc + val))

This formula starts with an initial value of 0 and adds each sales value in the range B2:B5 one by one, generating a running total.

Step 3: Press Enter. You will now see the result as an array expanding down the column. Each element in the resulting array shows the cumulative total up to that month.

Insert Through Google Menu Bar

1. Go to Insert → Function → Array → SCAN.
2. Select the range to which you want to apply the LAMBDA function.
3. Enter the arguments.
4. Press Enter to view the resulting cumulative values.

Examples

In real-life data, we often wish to calculate cumulative totals and running averages that build upon previous results. These scenarios are where SCAN in Google Sheets is extremely useful. It enables users to perform iterative calculations across rows or columns without manually copying formulas. Below are a few interesting SCAN function examples that demonstrate its flexibility and power.

Example #1

in this example, a business wants to track its cumulative revenue over several months. They wish to know how much total revenue was earned up to each month to monitor growth. We can use the SCAN function automates the running total in a single step.

Step 1: Enter the monthly sales data in a new Google Sheet as shown below.

Step 2: Click on an empty cell and enter the following formula:

=SCAN(0, B2:B7, LAMBDA(acc, val, acc + val))

Explanation:

• 0 is the initial value of the accumulator.
• B2:B7 is the range containing the weekly sales data.
• LAMBDA(acc, val, acc + val) adds each week’s sales to the cumulative total.

Step 3: Press Enter. The formula will return an array showing the running total after each month.

This helps the business quickly calculate their growth trends and track performance over time without manually summing each value.

Example #2

In academic or performance-based analysis, we often use the SCAN function in Google Sheets to track how scores change over time. For example, a teacher might want to know the progressive score of a student after each new test result.

Step 1: Enter the student’s test scores in the Google Sheet as follows:

Step 2: Let us enter the following formula in cell C2.

=SCAN(0, B2:B5, LAMBDA(acc, val, (acc + val)))

Now, we get the cumulative sums with the above formula.

Step 3: Press Enter. The output will show how the total score changes with each new test. This gives educators insights into how the student’s performance evolves across tests.

Example #3 – Combining SCAN with ARRAYFORMULA Function

Sometimes, one may want to apply a running calculation across multiple columns or automatically extend it to new data entries. Combining SCAN with ARRAYFORMULA helps create such dynamic, scalable formulas.

In this example, we have the monthly expenses of a home under different categories. We wish to compute the cumulative total for each category automatically.

Step 1: Enter the data as shown below.

Step 2: Enter the following formula to compute the running total for all categories:

=ARRAYFORMULA(SCAN(0, B2:D4, LAMBDA(acc, val, acc + val)))

Explanation:

• ARRAYFORMULA is used to apply the SCAN function dynamically across multiple columns.
• LAMBDA(acc, val, acc + val) accumulates totals for each new row across the selected columns.
• The result is a cumulative summary for each category month by month.

Step 3: Press Enter. You will see a dynamic table displaying the running totals for each category as new data is added.

This approach is especially useful for budget tracking, project management, and other cumulative data analysis tasks.

The SCAN function in Google Sheets is a powerful tool for performing iterative calculations such as running totals, averages, or other cumulative metrics. By combining it with functions like ARRAYFORMULA, users can handle complex datasets dynamically and efficiently—without repetitive formulas or manual updates.

Important Things to Note

1. If the LAMBDA function in SCAN has an incorrect number of parameters or an invalid expression, Google Sheets will return a #VALUE! error.
2. The SCAN function outputs an array containing cumulative or progressive results for each element in the range.
3. The results of SCAN in Google Sheets automatically spill down the sheet, filling as many rows as the input range.
4. The initial value parameter in SCAN plays a key role by setting the starting point for calculations. If you want to start from zero, specify 0.
5. The SCAN function is highly efficient for dynamic datasets, allowing you to perform running totals, moving averages, or sequential operations to avoid writing multiple formulas.

Frequently Asked Questions (FAQs)

Download Template

This article must help understand the SCAN Function in Google Sheets, with its formula and examples. We can download the template here to use it instantly.

Recommended Articles

Guide to What Is SCAN Function In Google Sheets. We explain how to use the SCAN Function In Google Sheets with examples and points to remember. You can learn more from the following articles. –

MIDB in Google Sheets

BYROW in Google Sheets

UNICHAR in Google Sheets