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
Posts 4 By Will Verduzco 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 How to Use LAMBDA in Excel to Create Your Own Functions
Simplify and reuse your most complex formulas.
Posts 6 By Tony Phillips 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.
Posts By Tony Phillips 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:
How-To Geek Report Unlock your tech-savvy potential and master the digital world with How-To Geek. Subscribe How-To Geek Report Unlock your tech-savvy potential and master the digital world with How-To Geek. Subscribe
By subscribing, you agree to receive newsletter and marketing emails, and accept Valnet's Terms of Use and Privacy Policy. You can unsubscribe anytime.
=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 The Beginner's Guide to Nested Functions in Excel
Use multiple functions at the same time.
Posts By Tony Phillips 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.
$100 at Microsoft Expand Collapse