The Excel SCAN function simplifies the way you calculate ongoing totals and other cumulative data. It applies a specified operation to each item in your list one by one, and gives you a step-by-step update of the results. This is especially handy for tasks like adding up monthly sales to get a yearly total, or for any similar cumulative analysis.

With SCAN, managing and interpreting your data becomes more straightforward and intuitive.

Syntax

The syntax for the Excel SCAN function is as follows:

=SCAN ([initial_value], array, lambda(accumulator, value) )
  • initial_value (optional): You can start your calculations from a specific number, but if you don’t specify one, Excel starts from the beginning of your list.
  • array: The array of values (series of numbers) you want to apply the SCAN function to.
  • lambda: A custom LAMBDA function being called to scan the array. You define a simple rule for what to do with each number in the list. For example, you could add each number to the total so far.

LAMBDA Structure

The SCAN function uses the LAMBDA function to apply the operations to perform. The Excel LAMBDA function accepts two parameters:

  • accumulator: is the value totaled up and returned as the final result.
  • value: is the calculation that is applied to each element in the array.

Explore More About the Exces LAMBDA Function

The LAMBDA function allows you to create custom, reusable formulas that can significantly streamline complex calculations. Whether you’re managing large datasets, performing intricate mathematical operations, or automating repetitive tasks, mastering the LAMBDA function can enhance both your productivity and the sophistication of your analyses.

Check out the comprehensive article linked here for step-by-step instructions, practical examples, and tips on how to get the most out of this powerful feature.

Creating Running Totals with SCAN Function

Let’s say you have a dataset with monthly sales figures for several years, organized in an Excel Table named “Sales.” You want to track the cumulative total of sales month by month, even across different years.

Here’s how you can easily do this using the SCAN function:

Our first pass over this data will be to add each new month’s sales to the previous running total.  Even if we cross years, we want to continue adding each new month’s sales to the previous total. We’ll reset the formula for each new year in the next step.

In cell E2, enter the formula to start calculating running totals:

= SCAN(0, Sales[Sales]

Here, 0 is the initial value, which means the calculation starts from zero. Sales[Sales] refers to the column named “Sales” in your table.

Next, we write the calculation rule: We need to specify what happens to each value in the “Sales” column. This is done using a LAMBDA function inside the SCAN function.

The Excel LAMBDA function needs two parameters:

  1. The first parameter is the accumulator. You can think of it as ‘previous_total’. It holds the running total from the previous calculation. If this is the first iteration, then this holds the value declared in the [initial_value]
  2. The second parameter is a placeholder that holds the current iteration’s value. Think of it as ‘current_sales’ because it holds the value of the current month’s sales.

You can name these parameters anything you wish. These names can be something like “previous_total” and “current_sales”, but they can also be something as simple as “a” and “b”. So, your LAMBDA function looks like this:

=SCAN(0, Sales[Sales], LAMBDA(a, b,

Now, put it all together in the formula:

=SCAN(0, Sales[Sales], LAMBDA(a, b, a + b) )

This formula will add each month’s sales (‘b’) to the total from the previous months (‘a’), giving you a running total in each row.

Understanding the Initial Value

If you had started with an initial value other than zero, say 1 million, the first calculation would have been the first month’s sales plus 1 million. This is why we start at zero—to accurately reflect the total sales from the start of your records.

Featured Course

Master NEW Excel Functions in Office 365 & Office 2021

Ready to supercharge your Excel skills? This course will help you master the new set of functions introduced to Excel for Office 365. You’ll create professional-grade reports in a fraction of the time it used to take you.
Learn More
Excel new functions course cover

Understanding the SCAN / LAMBDA Calculation

To see how the Excel SCAN function works with a LAMBDA function, let’s look at how it does running totals. We’ll examine the first three iterations using the data from the example above.

Iteration #1

  • “a” = 0 (this is the [initial_value])
  • “b” = 8,603 (this is the value in the array’s first cell)
  • New total “a + b”: 0 + 8,603 = 8,603 (the initial total plus this month’s sales)

Iteration #2

  • “a” = 8,306 (this is the total from the previous step)
  • “b” = 9,687 (This is the value from the second cell in the sales column)
  • New total “a + b”: 8,603 + 9,687 = 18,290 (the previous total plus this month’s sales)

Iteration #3

  • “a” = 18,290 (this is the total from the previous iteration)
  • “b” = 7,811 (this is the value from the third cell in the sales column)
  • New total “a + b”: 18,290 + 7,811 = 26,101

Each month, this method adds that month’s sales to the running total. It’s a simple way to track monthly sales and see how they add up.

Featured Course

Power Excel Bundle

10x your productivity in Excel 💪. This bundle includes Master Excel Power Query course & Master Power Pivot and DAX course. It’s Excel’s Ultimate Power Tools in ONE convenient (cost savings) bundle. Learnings apply to Power BI as well.
Learn More
Power Excel Bundle course cover

Creating a Year-To-Date Formula

If you need to calculate Year-To-Date (YTD) values that reset every January or the start of a fiscal year, you can customize Excel’s SCAN function to do just that.

Here’s how to set it up step-by-step. Like before, you start with the SCAN function:

=SCAN(0, Sales[Sales], LAMBDA(a, b,

Here, 0 is the starting point (initial_value), and Sales[Sales] refers to the sales data.

Adjusting for New Year or Fiscal Year Start

You need to determine if the current data point (month of sales) is the start of a new year or fiscal period. To do this, use the Excel OFFSET and MONTH functions to check the month of each data entry.


The OFFSET function helps you refer to the month data related to each sales entry. Assuming the month data is in the column directly before the sales data, the OFFSET function would look like this:

= OFFSET(b, 0, -1)

This function points to the cell immediately to the left of each sales entry (b): it starts where ‘b’ happens to be at the moment, then moves zero rows up or down (0), then moves 1 column to the left (-1).

Using the MONTH Function

The Excel MONTH function extracts the month number from the date:

= MONTH(OFFSET(b, 0, -1))

Applying the IF Statement

Decide what to do based on the month number: If it’s not January (or another start month, say October for fiscal years), continue adding to the total:

= IF(MONTH(OFFSET(b, 0, -1)) <> 1, a + b,

💡 If your business uses a fiscal year that starts in October, reset the Year-To-Date calculations in October.

IF(MONTH(OFFSET(b, 0, -1) ) <> 10, a + b

Resetting for New Year

For January, the formula should not simply add the current month’s sales to the previous total but start over from zero.

The complete formula will look like this if January is the reset month:

= SCAN(0, Sales[Sales], LAMBDA(a, b, IF(MONTH(OFFSET(b, 0, -1)) = 1, 0 * a + b, a + b)))

In this formula:

  • IF(MONTH(OFFSET(b, 0, -1)) = 1 checks if the month of the current sales entry is January.
  • If it is January (TRUE), the formula sets the total to the sales of January only (b), effectively resetting the total.
  • If it’s not January (FALSE), it continues to accumulate the total (a + b).

The Advantages of Using Excel Tables with Formulas

One of the best things about using properly formatted Excel Tables is their dynamic nature, especially when combined with powerful formulas like SCAN and LAMBDA.

Here’s why this is so beneficial:

  • Automatic Adjustment: When you add new rows to an Excel Table, the table automatically extends its size. This is particularly advantageous because formulas that reference the table will automatically update to include these new rows. There’s no need to manually adjust your formulas to accommodate additional data, which saves significant time and effort.
  • Consistent Formula Application: As your table grows with each new entry, any formulas using the table as a reference will continue to apply their calculations across all entries, old and new. This ensures that your analyses, summaries, or calculations remain consistent and comprehensive without any extra work on your part.
  • Reduction in Errors: By using Excel Tables, you reduce the risk of errors that can occur when updating or expanding your datasets. Since the table updates itself, you avoid common mistakes like missing out on new data rows in your calculations or accidentally referencing incorrect cell ranges.

Download the Workbook

Enhance your learning experience by downloading our workbook. Practice the techniques discussed in real-time and master the Excel SCAN Function with hands-on examples. Download the workbook here and start applying what you’ve learned directly in Excel.

Excel Download Practice file

Leila Gharani

I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.

My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.