Running Sum
By Richard Rost

10 months ago

Excel Running Sum / Running Total / Cumulative Total

S M L XL FS | Slo Reg Fast 2x | Bookmark

In this Excel tutorial, I will show you how to calculate a running sum (also called a running total or running balance) and a running average in your spreadsheets using simple formulas and autofill with absolute references. You will learn both the quick and detailed methods for setting up these calculations, how to handle common errors, and why absolute references are important for correct results.

Spencer from Mississauga, Ontario (a Platinum Member) asks: I have a simple spreadsheet that tracks invoices for a customer. I added a new column to calculate the running average of all the invoices. Is there a way to write a formula that I can drag down the column, so it automatically adjusts for each row?

Members

There is no extended cut, but here is the file download:

File Download - Gold Members

Silver Members and up get access to view Extended Cut videos, when available. Gold Members can download the files from class plus get access to the Code Vault. If you're not a member, Join Today!

Prerequisites

Excel Beginner 1

Recommended Courses

Excel Expert Level 5

Learn More!

Questions?

Please feel free to post your questions or comments below. Thanks.

Keywords

TechHelp Excel, Running Total, Running Average, Running Sum, Running Balance, Cumulative Total, Cumulative Sum, Cumulative Running Total, AutoSum Cumulative, Cumulative Formula, Daily Totals, Progressive Total, How to Add Totals Down a Column, Sum to Current Row, Add Up As You Go, Accumulated Total, Dynamic Total, Add Previous Rows, How to add up values step by step, Formula to add each row, Totals that build over time, Beginners running total, Adding up rows automatically, Calculate total so far, running total formula, running sum autofill, running average formula, absolute references, excel sum with autofill, cumulative average, rolling total calculation, excel drag formula, ignore formula error, sum with absolute cell, calculate running totals in table, step by step sum formula, average each row cumulatively, autofill running sum, rolling average formula, trigonometry in excel

Subscribe to Running Sum
Get notifications when this page is updated

Intro

In this video, you'll learn how to calculate a running sum, also known as a running total or cumulative total, in Microsoft Excel. I'll show you step by step how to use the SUM function with absolute references to quickly fill a column with running totals, and how to do the same for running averages using the AVERAGE function. We'll also discuss the difference between absolute and relative references, how to use the autofill handle to copy formulas, and how to deal with common Excel error messages you might see along the way.

Transcript

Welcome to another TechHelp video brought to you by ExcelLearningZone.com. I am your instructor, Richard Rost. Today I am going to show you how to calculate a running sum, also called a running total, running balance, or a cumulative total. It has a million names.

I am also going to show you how to do a running average. The same thing applies, whether you are doing sum or average - it works the same way.

Today's question comes from Spencer in Mississauga, Ontario, one of my Platinum members. Spencer says, "I have a simple spreadsheet that tracks invoices for a customer. I added a new column to calculate the running average of all of the invoices. Is there a way to write a formula that I can drag down the column so it automatically adjusts for each row?"

Yes, we can certainly do this, Spencer. It is called a running sum, running total, or whatever you want to call it. I am going to show you how to do it.

First, I am going to show the quick way, because I know a lot of times people are looking for solutions. They just want the quick solution, "show it to me fast." Then I will go back and explain everything that I do.

Here I have a spreadsheet. We have an order ID, an order date, an amount, and you want to put a running sum here. Here is the fast solution:

Come down to the last row. Put =SUM(). We know our SUM function. Then in the parentheses, do the whole range there, and then press Enter. Now, that is the sum of that whole column.

What you are going to do is click here, come up top to the formula bar, click inside the C2 there (the first cell), and hit F4. That will make that an absolute reference. Press Enter. Nothing appears to change, but watch this.

Now click on it again, grab the autofill handle, click and drag it up the column. And there you go.

You can ignore all these errors that show up here. If you highlight all those and just click on that and go "Ignore This Error," it is just saying the formula omits adjacent cells. That is fine.

There you go. There is your running sum.

It works the same way for averages, too. If you want to do a running average, you can come down here, say =AVERAGE(), open it up, select the whole list of amounts, and press Enter. Right? Click there, click on the C2, hit F4 to make it an absolute reference, and then drag it all the way up like that. And there you go. You can ignore all of these, and there is your running average. Same thing.

That is the fast solution.

Now let me explain what I am doing. Let me get rid of this stuff here.

Normally, if you were here and you wanted to get a sum of this cell and everybody above it, just the first one, you would say sum of this to itself. There is nothing wrong with that. Now, in this one, you would say =SUM() of these guys. That works fine. Next one down, same thing. Sum of these three entries. Press Enter.

If I do this and drag it all the way down (grab the autofill handle and drag it all the way down), that is not right. What happens is autofill is trying to be helpful, and it is not getting the references correct that you want there. So that is not a good solution.

What we have to do is use an absolute reference. The absolute reference says always point to the same exact cell. That means do not move it relatively when I pull the autofill down different rows.

So, here we have to basically say =SUM() of this cell, but make it absolute. That is that F4 again, or you could type in the dollar signs. When you do that, though, sometimes in that first cell, that is why I like to start at the bottom. Sometimes it does that, it gets rid of it for you automatically. It thinks it is being helpful.

Come up here and click right there and do that and press Enter. See? We got that. That is absolute, and that one is relative. That means it is relative to whatever row you are in.

So, as I drag this down now, it works because we are going down with the second value relatively to the row that we are on, but the C2 always stays the same. So this is always the sum of that and the sum of that and the sum of that. See that? The second term can move.

Then, of course, you have to ignore all these errors. Same thing with the average: =AVERAGE(). I am going to type in $C$2:C2, press Enter, and then we can autofill that down. Boom. Then ignore these errors.

There are ways to turn that off. I talk about that in my full course.

That is it. It is not that hard to do as long as you understand absolute references. Do not make that term move if you drag with the autofill handle.

If you want to learn more about this kind of stuff, go watch this video next. It is about moving averages, also called rolling averages. Essentially, for whatever number you pick, like every five, the average is that one plus the five before it. So the next one is that one plus the five before it. So it creates more of a rolling average, and that is where the average changes over time.

Also, for those of you who follow my channel, you probably know that most of what I do is Microsoft Access videos, Access database stuff. In this video and its extended cut, I show you how to do a running sum in a check register database. I will show you how to do it in a report and also in a form. Check that out.

You will find links to all of this stuff down below. If you want to learn more about running balances and running totals, I cover them in my Excel Expert Level 5 class. We cover all kinds of stuff: running balances, SUMIF, SUMIFS, rounding, all kinds of cool stuff. Trigonometry in Excel is something you can do. I have a whole ton of Excel lessons available on my website, from beginner through expert level. Lots and lots of stuff. Beginner Level 1 is absolutely free. You can find it on my YouTube channel or you can come to my website and watch it. I will put links down below.

There you go, folks. That is going to do it for your TechHelp video for today. Hope you learned something. Live long and prosper, my friends. I will see you next time.

I am excited to announce that I am creating a brand new series of lessons focused on programming in Microsoft Excel VBA. If you have been looking to take your Excel skills to the next level and learn how to automate tasks, write custom macros, and unleash the full power of Excel VBA, these lessons are for you. Sign up now on my website at the link shown. You will find a copy in the description down below the video window, and I will send you more information on this exciting new series.

If you would like to see me make more Excel TechHelp videos, post a comment down below and say "I want more Excel." The vast majority of my videos are for Microsoft Access, the database program, because that has been my forte for the past three decades or so. However, I love Excel and I am more than happy to make more videos for Excel, Word, PowerPoint, and all the other topics that I teach. As you know, the squeaky wheel gets the grease, so make your voice heard and let me know you want more Excel videos.

TOPICS:
Calculating a running total in Excel
Calculating a running average in Excel
Using the SUM function for running totals
Using the AVERAGE function for running averages
Creating absolute references with F4
Dragging formulas with autofill
Difference between absolute and relative references
Fixing autofill issues with formula references
Ignoring formula error messages in Excel

COMMERCIAL:
In today's video, we're learning about how to calculate a running sum, also known as a running total or cumulative total, in Microsoft Excel. I'll show you the quick solution using the SUM function and absolute references so you can quickly fill your column with running totals, plus how to apply the same technique for running averages using the AVERAGE function. After that, we'll talk about why absolute references are so important and what to do if you see errors pop up. If you want to learn about things like moving averages or how this works in Access databases, I'll show you where to find more information and related lessons. You'll find the complete video on my YouTube channel and on my website at the link shown. Live long and prosper my friends.

Quiz

Q1. What is a running total also commonly known as?
A. Cumulative total
B. Moving average
C. Conditional sum
D. Absolute value

Q2. In the context of the video, what function in Excel is primarily used to calculate a running sum?
A. SUM
B. VLOOKUP
C. COUNT
D. IF

Q3. Which of the following is necessary to ensure the running total formula works when dragging the formula down a column?
A. Using an absolute reference for the starting cell
B. Using only relative references
C. Highlighting all cells before applying the formula
D. Formatting the column as currency

Q4. What key should you press in Excel to convert a cell reference into an absolute reference while editing a formula?
A. F4
B. F2
C. Ctrl+C
D. Alt+Tab

Q5. What error message does Excel typically display in the running total column that the instructor says you can ignore?
A. The formula omits adjacent cells
B. Division by zero
C. Circular reference warning
D. Data type mismatch

Q6. How does the running average formula compare conceptually to the running total formula in Excel?
A. The same logic applies, just replacing SUM with AVERAGE
B. You must use COUNT instead of SUM
C. You need to use IF statements for running average
D. The formula must be entered as an array

Q7. What is the purpose of using an absolute reference in these formulas?
A. To always point to the same starting cell regardless of row
B. To make the formula visible in all cells
C. To lock the column formatting
D. To remove formatting from the cell

Q8. What happens if you only use relative references in your running sum formula and drag it down a column?
A. The formula does not calculate the correct running total
B. It calculates the sum of the entire column every time
C. The sheet becomes locked
D. It deletes the data

Q9. What is a moving (rolling) average, as briefly described at the end of the video?
A. The average of a specified number of recent values for each row
B. The average of all historical values up to each row
C. The maximum value across the range
D. The sum divided by 100

Q10. If you want to automate tasks and write custom macros in Excel, which tool would you use according to the instructor?
A. VBA (Visual Basic for Applications)
B. Pivot Tables
C. Conditional Formatting
D. Data Validation

Q11. In the running average formula, what does $C$2:C2 represent?
A. A mixed absolute/relative range that starts at C2 and expands as you copy the formula down
B. A static range that never changes
C. Only the first cell in column C
D. Only the last cell in column C

Q12. What happens when you autofill the correctly structured running total formula down your column?
A. Each row shows the sum of all entries from the top down to that row
B. Each cell simply copies the initial sum for all rows
C. Each row sums only the current cell
D. Each row averages all columns

Q13. According to the instructor, which other function (apart from SUM and AVERAGE) is commonly used for advanced totals in Excel?
A. SUMIF or SUMIFS
B. CONCATENATE
C. LEN
D. LEFT

Q14. What is the main concept you need to understand to create running totals or averages in Excel?
A. Absolute vs. relative references
B. Conditional formatting
C. Text-to-columns
D. Data validation

Q15. What action does the instructor suggest if you want to learn more about related topics like moving averages?
A. Watch the next recommended video
B. Use only Access for all running totals
C. Never use Excel for running totals
D. Contact Microsoft support

Answers: 1-A; 2-A; 3-A; 4-A; 5-A; 6-A; 7-A; 8-A; 9-A; 10-A; 11-A; 12-A; 13-A; 14-A; 15-A

DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.

Summary

Today's TechHelp tutorial from Excel Learning Zone focuses on how to calculate a running sum, also known as a running total, running balance, or cumulative total, in Microsoft Excel. I will also walk through how to calculate a running average, since the process is essentially the same for both.

This topic comes from a question asked by a student who tracks customer invoices in a spreadsheet and wanted a formula for calculating a running average that could be easily applied to each row simply by dragging it down the column. This is a very common scenario, and I will show you a fast solution as well as explain why it works.

To start, imagine a spreadsheet with columns for order ID, order date, and the amount. If you want to quickly add a running sum in the next column, you can use the SUM function. Go to the row you want to calculate, type the SUM function, and select the range you want included in the total. Make sure to use an absolute reference for the starting cell in the range. You can do this by clicking into the formula and pressing F4, which adds dollar signs to the cell reference, locking it in place. This ensures that when you drag the formula down the column using the autofill handle, the first cell in the sum stays fixed while the second cell reference updates as you move down each row.

You might see Excel display warnings about the formula omitting adjacent cells. These warnings can be ignored in this case, since you are forming a custom range for each row.

The process works the same way for averages using the AVERAGE function. Write the formula with an absolute reference to the starting point of your data and then drag it down. Excel will calculate the average for all entries up to each row.

To clarify, you might be tempted to use the autofill feature by simply dragging down a basic SUM formula, but without absolute references, Excel will adjust both the starting and ending points of the range in each formula, which will not give you the correct running total. An absolute reference tells Excel exactly which cell to always use as the starting point, while the other endpoint can be left as a relative reference so it changes for each row. This is the key to making running totals and averages work as you drag the formula down.

If you are interested in exploring related topics, such as moving averages (sometimes called rolling averages), where you take an average over a certain number of previous entries, I have another tutorial on that as well. A moving average helps smooth out changes over time by always using a set number of previous values in the calculation.

If you are someone who uses Microsoft Access, I also have lessons on how to perform running sums in an Access check register database, including how to do this in both reports and forms.

You can find links to these related tutorials and my full Excel classes below. For those new to Excel, my Beginner Level 1 course is absolutely free and available on my YouTube channel and website. In more advanced classes, such as my Expert Level 5 course, I go deeper into topics like running balances, conditional summing functions, rounding, and more. I even touch on how to use trigonometric functions in Excel.

Finally, I am launching a new series of lessons dedicated to programming in Excel VBA. If you want to learn how to automate your spreadsheets, write custom macros, and unlock advanced features of Excel, be sure to sign up for updates on my website. The link is below.

If you would like to see more Excel-specific tutorials from me, let me know in the comments. While I focus heavily on Access, I am very happy to produce more content for Excel, Word, and PowerPoint if there is enough interest.

You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends.

Topic List

Calculating a running total in Excel
Calculating a running average in Excel
Using the SUM function for running totals
Using the AVERAGE function for running averages
Creating absolute references with F4
Dragging formulas with autofill
Difference between absolute and relative references
Fixing autofill issues with formula references
Ignoring formula error messages in Excel