To calculate running totals, Excel’s SCAN function handles everything in a single formula. This guide includes a video and step-by-step instructions. Learn how to easily calculate a running total in Excel with this step by step guide to the SCAN function. Keep track of your data in a systematic way and streamline your calculations with this helpful tool.
Table of Contents
What is a running total?
A running total calculates cumulative sum in each cell in a workbook. This is commonly used in sales spreadsheets, financial dashboards, and other common business templates.
For example, say you want to calculate year-to-date (YTD) revenue for a small business. You may process data weekly, adding sales data on a new line every Monday morning. A running total adds the latest sales data to the cumulative total for the year, so you can immediately see YTD revenue without running additional calculations.
Here are a few reasons you may want to tabulate a running total:
- Bank balances by day, week, month, or year
- Savings goals (including retirement planning spreadsheets)
- Monthly sales figures by rep
- Expense trackers
- Sales quota dashboards
- Inventory calculations (items in stock, revenue from sales, etc)
How to create a running total in Excel
It shouldn’t require multiple cells to calculate running totals. Excel now includes a built-in function to help with this.
Enter, the SCAN function. Below, we’ll talk about the syntax of this function and how to use it to calculate running totals for a range of cells.
Syntax of the SCAN Function
The syntax of the SCAN function looks like this:
=SCAN(initial value, data range, formula)
The initial value in the SCAN function is just what you want to start with. If you’re staring from nothing, enter 0. If you want to start with the value of a specific cell, enter that here.
The data range asks you which cells need to be included in the calculation.
The formula is where it gets a little more complicated. In this case, we’ll use the Lambda function. This allows you to create a function within a function in Microsoft Excel. The Lambda function has syntax of its own:
LAMBDA(parameter or calculation, perameter or calculation 2, . . .)
Note the “parameter or calculation” argument. It’s delightfully vague, and that’s ok. Here’s an example for calculating a running total:
=SCAN(0,A1:A50,LAMBDA(x, y, x+y))
Note that we’re using the variables x and y in this function within a function. Then, we’re showing the SCAN function that we want to add x and y.
Enter this in the cell, then press enter. Voila! That’s the running total for cells A1 through A50.
Uses for The SCAN Function
The SCAN function in Excel has additional uses beyond calculating running totals. In my video (below), you’ll also see how I use it to calculate minimum values from a growing data array.
In that case, I’m using MIN within the SCAN function to calculate the minimum value of a list. That formula looks like:
=SCAN(J7,J7:J25,LAMBDA(a,b,MIN(a,b)))
See how we insert the MIN function within LAMBDA and include that within SCAN? It calculates everything for me, and I only had to enter a single formula for the whole sheet.
How do I calculate the running total in Excel?
Use =SCAN() to calculate running totals in Microsoft Excel workbooks. This function calculates cell values in a range based on criteria you include in a formula typed into the same function.
What is the running total formula in Excel?
Use the following formula to quickly calculate running totals in your workbook.
=SCAN(0,A1:A50,LAMBDA(x, y, x+y))
This range includes A1 through A50 and starts with a value of 0. If you want your running total to start with an absolute reference, you can include that with a cell number or as a hard-coded number in the formula.
Do I need to use COUNTIF and SUMIF or an INDEX function when calculating running totals?
No, you don’t need to use COUNTIF and SUMIF to calculate running totals in Excel. While these have been helpful in the past, the newer SCAN function helps you automatically calculate running totals in a single formula.
Do I need a pivot table for this?
You don’t need a pivot table to calculate running totals in Excel. You can just use the SCAN function in a cell range to calculate alongside your data.
Alternatives for Calculating the Running Total
While the SCAN function is the new and preferred way to find the cumulative sum of data in Excel, you can still use the older methods. The simplest is likely the SUM function for your running total calculation. Here’s an example that shows how to calculate the sum of an entire column:
=SUM(A:A)
This works the very same way as the SUM function in Google Sheets. Just enter two or more cells (or a range of cells) as the arguments inside the function. It sums whichever data you list there.
However, if you want to keep track of previous data instead of overwriting your existing figure when you enter new data, you’ll want a running total column that tracks new totals on new lines. The SCAN function is now the easiest way to do that.
Video Explainer
Here’s the video that explains each part of the process. If you want to use my Excel Scan Function dataset and follow along, you can access it here.
Access DatasetVideo Transcript: Intro
Hi there! In today’s video, I will briefly go over the scan function in Excel, how to use it, when it is useful, and how is it useful for running totals. Now, the scan function is a relatively new function in Excel. It came out in early 2024. A quick way to see if you do have the scan function in Excel is if you start typing in the scan function, it should automatically come up as a usable function.
If it doesn’t, it hasn’t been installed in your version of Excel yet.
So, the scan function is very useful when you are looking at a series of numbers, or an array of numbers, and particularly useful when you are trying to calculate running totals. Prior to the scan function, you’d have to make manual formulas that adds up each of these numbers. If you wanted to, let’s say, calculate a running total for this sample test data that we have, including some sales data for these folks.
You would have to do things like, so you’re going to take this Number here, gonna add this number plus this number. Okay, now for the next one, you’re gonna have to go equals this plus this, right? And so on and so on.
So it’s a little bit complex if you were going to do that.
How =SCAN() Works
The scan function allows you to look at an array and write a function or a formula, and it will actually populate the entire results in a single row without you having to have a formula in each of the cells.
The scan function utilizes the lambda function within it, which I will go over briefly as I create the scan function for you.
The Lambda function is simply function in Excel, which allows you to create a custom function using Excel’s programming language without venturing into the Visual Basic or any type of coding like that. I will get into that briefly as I said when I explain the scan function.
So let’s say I wanted to have to show the cumulative result from these prices here or from these sales that these folks have made.
You will do that by using the scan function.
Using The Excel SCAN Function
So I will go equals scan to open up the formula, or the function rather. So the initial value, if I’m looking to to show running total, the initial value is going to be zero. The array, or series of values that I’m going to be looking at, would essentially be these figures here.
And the function that I’m going to be using is that lambda function, which I’ve just briefly explained previously, lambda. Lambda allows you to create a function within a function, essentially. So I’m going to call out the parameters of this lambda function by using the variables at the beginning. So, you can call the variables anything you want.
Typically you’d use x and y. So we have x and y as the variables within this lambda function. And then, I’m going to just tell it what I want the function to do, which is add these two together, x plus y.
It will close the lambda and then close the scan, and you will see it automatically populates the cumulative totals down this row.
So 119 is $59.50 plus $59.50. $178.50 is these three added together, and so on, and so on.
Testing The Function
If you wanted to test it, you could do a sum at the bottom, and you could add up all these, and then you see the $1,379.20 at the end, and the same thing with the scan function. Another useful example for the scan function would be, let’s say you wanted to keep track of a running total of the coldest temperatures in this example data set here.
You would use very similar syntax this scan function. Scan, the initial value, so this time the initial value, instead of it being zero, it’s going to be this 25, so I will just click that cell. The array, again, is these numbers that we’re evaluating. And this time the function is going to be that lambda.
And you’re going to call out your variables, which is x and y. They could be named anything you want. Let’s use a and b this time. Now, I want to know the minimum temperature in this array. So I’m going to use min a, b, and then you close all of your brackets and you will see it gives kind of a cumulative running total but of the minimum temperature, so it’s 25 here It’s the first one then it goes down to 22, which is correct. 19.
Then it drops to 17, but then it goes up back up to 25 but it will keep the 17 running down the total because it is the minimum temperature Out of all of these numbers you’ll see then it drops to 12.
So then it puts it to 12 You Back up to 17 but it will remain at 12 because it holds the minimum number. That is a brief explanation of the scan function and the lambda function.
For more advice, check out SpreadsheetPoint.com and don’t forget to like and subscribe.
