Credit: Lucas Gouveia/How-To Geek

Published 8 minutes ago

Tony Phillips is an experienced Microsoft Office user with a dual-honors degree in Linguistics and Hispanic Studies. Prior to starting with How-to Geek in January 2024, he worked as a document producer, data manager, and content creator for over ten years, and loves making spreadsheets and documents in his spare time.

Tony is also an academic proofreader, experienced in reading, editing, and formatting over 3 million words of personal statements, resumes, reference letters, research proposals, and dissertations. Before joining How-To Geek, Tony formatted and wrote documents for legal firms, including contracts, Wills, and Powers of Attorney.

Tony is obsessed with Microsoft Office! He will find any reason to create a spreadsheet, exploring ways to add complex formulas and discover new ways to make data tick. He also takes pride in producing Word documents that look the part. He has worked as a data manager in a secondary school in the UK and has years of experience in the classroom with Microsoft PowerPoint. He loves to encounter problems in Microsoft Office and use his expertise and legal-level training to find solutions.

Outside of the Microsoft world, Tony is a keen dog owner and lover, football fan, astrophotographer, gardener, and golfer.

Sign in to your How-To Geek account

For years, I’ve relied on running totals in Microsoft Excel, but the traditional methods are fragile and unreliable. Since Microsoft introduced the SCAN function, I’ve switched to a brand-new approach that is flexible, self-healing, and doesn’t break when my data grows.

The SCAN function is available to those using Excel for Microsoft 365, Excel for the web, the Excel mobile and tablet apps, and one-off versions of Excel released in 2024 or later.

HTG Wrapped: Our favorite tech in 2025

24 days of our favorite hardware, gadgets, and tech

Why the old-school method is problematic

Before the SCAN function was introduced, if I wanted a running total, I would use the absolute-relative trick.

In this spreadsheet, column B contains monthly profits, and I want to display a running total in column C.

In cell C2, I would type:

=SUM($B$2:B2)

Then, I would double-click the fill handle to apply the formula to the remaining rows.

On the surface, this looks like it works well. However, suppose I later realize I missed out a month. When I insert a new row, Excel fails to fill the formula into that new blank row, leaving a gap that needs to be addressed manually.

Also, if I predate column A to the end of the year and drag the formula in column C to the bottom, unless I drastically change the formula to incorporate an IF statement, I get an untidy, confusing repeated total.

In the examples above, the data is in a regular range. However, if I format my data as an Excel table, things become even more complicated. Structured references don’t have a built-in way to anchor the start of a range, so I would either need to override the table’s logic with regular cell references or construct a new formula that leverages the INDEX function.

=SUM(INDEX([Profit],1):[@Profit])

The solution: The SCAN function

The SCAN function lives in a single cell and spills the results down the column, ensuring my totals are always structurally and mathematically correct.

The SCAN syntax

The SCAN function has three arguments:

=SCAN([initial_value],array,lambda)

where:

• initial_value is the starting point for the running total.
• array is the array to be scanned in creating the running total.
• lambda is a customized function that is called to scan the array.

The LAMBDA structure

The third argument, lambda, accepts three parameters:

=LAMBDA(accumulator,value,body)

where:

• accumulator is the value that is totaled and returned as the result.
• value is the current value in the array.
• body is the calculation applied to each element in the array.

Related

Important points to note before you begin

At first glance, SCAN looks more complicated than a simple SUM. But there’s a difference between a formula that’s hard to type and one that’s hard to manage. SCAN requires a bit of learning up front, but once it’s in place, it’s a set-it-and-forget tool that protects your data from the manual errors that plague traditional running totals.

While the SCAN function is great for processing running totals, there is one caveat: it doesn’t work inside an Excel table. This is because tables require every row to have its own independent formula or value, but the SCAN function spills results across multiple rows. This means it must be used in a regular range. That said, it’s still preferable to format the source data as an Excel table for reasons I’ll come to soon.

To follow along as you read, download a free copy of the Excel workbook used in the examples. After you click the link, you’ll find the download button in the top-right corner of your screen.

Example 1: Calculating a basic running total

In this spreadsheet, the Month and Profit columns are formatted as an Excel table (named T_Profits), and the Running Total column is in a regular range in column D. The blank column C acts as a buffer so that the table doesn’t "grab" the external column.

Here’s the formula I’ll type into cell D2:

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

where:

• The initial_value is 0 because row 2 contains the first entry in my dataset (I’m not carrying over any figures from 2023).
• T_Profits[Profit] is the list of profit values in the Profit column of the T_Profits table that it will scan through.
• *LAMBDA(a,b,a+b) *considers a as the total so far, b as the value in the profit column on the current row, and a+b as the calculation it needs to perform.

You can use any notation for the three LAMBDA arguments. I use a and b to keep the formula as short as possible, but you could use more descriptive names.

The benefit of using this method instead of the old-school approach becomes clear when I need to insert a row in the middle of the dataset—it handles this structural change, ready for me to enter the monthly total in the blank cell in column B.

I also don’t need to predate column A or drag any formulas down, because when I type a new month into cell A22, the whole structure accommodates this addition automatically.

What’s more, if I need to delete a row from the middle of the dataset, it handles this amendment without breaking a sweat.

Related

5 Ways to Improve Data Structure in Microsoft Excel

Getting things organized in your spreadsheets is worth the effort.

Example 2: Tracking moving averages

To calculate a moving average that updates as I add data, I combine the SCAN and SEQUENCE functions. While the running total in the example above *adds *values, a moving average *divides *the total by the number of entries processed so far.

To do this, in cell D2, I’ll enter:

=SCAN(0,T_Profits[Profit],LAMBDA(a,b,a+b))/SEQUENCE(ROWS(T_Profits[Profit]))

where:

• The SCAN part of the formula calculates the running total exactly as I did in Example 1.
• The SEQUENCE part creates a dynamic list of numbers (1, 2, 3...) that corresponds to the row index. For example, in the fifth row, the sequence returns the number 5.
• By dividing (/) the running total by the sequence number, Excel returns the average of all the values from the start to the current row.

This formula requires me to make sure there aren’t any internal blank rows. For example, if there’s no data for May 2025, the SEQUENCE function treats the blank cell as a zero, which inaccurately lowers the average.

Example 3: The year-to-date reset

In the examples so far, the SCAN function is used on the assumption that I want to carry the running total over 2024 and 2025. However, suppose I want the calculation to accumulate through December 2024 and then reset the moment I hit January 2025.

Using the old-school method, I would need to manually break my sum chain every 12 months. With SCAN, however, I can build a reset switch directly into the LAMBDA.

Here’s the formula I need to use:

=SCAN(0,SEQUENCE(ROWS(T_Profits[Profit])),LAMBDA(a,i,IF(MONTH(INDEX(T_Profits[Month],i))=1,INDEX(T_Profits[Profit],i),a+INDEX(T_Profits[Profit],i))))

where:

• SEQUENCE(ROWS(T_Profits[Profit])) creates a list of numbers (1, 2, 3...) representing each row.
• In the LAMBDA, i represents the current row number.
• INDEX(T_Profits[Month],i) looks at the date in the current row. If it’s January (1), the reset switch flips, returning just the current profit (INDEX(T_Profits[Profit],i)). If it’s any other month, it performs the usual *a+profit *calculation.

Related

Final steps: Cleaning up the output

While the formulas above work well as they are, the final polish is what makes my spreadsheet the best it can be:

• The "one cell" rule: Remember that because these are spill formulas, they only ever need to be typed into the top cell. If I ever need to change the logic (like switching from a running total to a running average), I only have to edit that one single cell.
• The spill obstacle: If I ever see a #SPILL! error when I type my formula and press Enter, it’s often because something is blocking the dynamic array from spilling. In this scenario, as soon as I clear out any old manual formulas or stray text in the column, the SCAN results flow down instantly.

---

While the SCAN function is a game-changer for running totals, it’s just one of a larger release of game-changing functions that have shifted how Excel works—thanks to dynamic arrays. Functions like FILTER, SORT, and UNIQUE have essentially replaced many of the old functions I used to rely on. Instead of having to deal with a dataset row by row, these new tools let a single cell handle the heavy lifting across a whole range. By prioritizing this spill behavior, I’m building spreadsheets that are immune to the classic manual errors that used to disrupt my workflow.

Microsoft 365 Personal

OS Windows, macOS, iPhone, iPad, Android

Free trial 1 month

Microsoft 365 includes access to Office apps like Word, Excel, and PowerPoint on up to five devices, 1 TB of OneDrive storage, and more.