This video shows how to create a Pivot Table using data from different sheets in a workbook. Download the sample workbook, and a full transcript is below the video.
NOTE: For written step-by-step instructions, go to the Sum Functions page
To create a running total in each row of an Excel table, use the SUM function, with starting row locked as an absolute reference. Watch this video to see the steps.
On this worksheet, we have a list of bank transactions, starting with the opening balance, and then withdrawals and deposits after that.
I'd like to see the current total, and I can do that by using the SUM function in this cell.
Start with an equal sign, and then S-U-M, open bracket,
I'll click on the button at the top of Column C, and that puts the whole column into the formula. Close the bracket, and press enter.
There's currently $110 in Column C, based on the opening balance, and then subtracting for withdrawals, and adding the deposits.
We could also show a running total as we go along. In this cell, D2, I could see what we've accumulated to date, and then copy that formula down, so it will show the total at each row.
In this cell, I'll start with an equal sign, and again, it's the SUM function, and an open bracket, and in this cell, we want to get from C2, and a colon, we want to end at C2 as well. I'm going to close the bracket and press enter.
Here we can see that the total from C2 to C2 is 100. Now, I want to copy this down, and we'll see what happens in its current state when we copy down.
I will double click on that button in the corner. That fills it down. And we can see that in each row, it's just adding that row. Here, it's saying C6 to C6, but what we really want is C2 to C6.
I'm going to change this formula. Instead of changing everything as it moves down these rows, I want it to change the end position, but I want it to keep Row 2 as the starting point.
I'm going to click in front of the two and type a dollar sign, so this row reference is absolute. It won't change as we copy the formula down.
I'm going to press enter again. Click on this cell, and double-click in the fill handle at the corner, and that copies it down again.
This time, we can see the total here. The current total is the same as our total for that whole column, so we know it's correct now.
If we look at this formula, it's always starting at C2, and then going down to the current row. So this is in Row 6. It's adding up everything to Row 6.
In Row 4, it goes from two to four.
So we create a running total by making the starting row absolute, and keep a relative reference for the end point, so that will change as you copy it down.
Download the zipped Sum functions sample workbook. The workbook contains the Running Total example, and other ways to sum in Excel. The zipped file is in xlsx format, and does not contain any macros.
Last updated: June 7, 2018 11:17 AM