Running total (also called cumulative sum) is quite commonly used in many situations. It’s a metric that tells you what’s the sum of the values so far. For example, if you have the monthly sales data, then a running total would tell you how much sales have been done till a specific day from the first day of the month. There are also some other situations where running total is often used, such as calculating your cash balance in your bank statements/ledger, counting calories in your meal plan, etc. In Microsoft Excel, there are multiple different ways to calculate running totals. The method you choose would also depend on how your data is structured. For example, if you have simple tabular data then you can use a simple SUM formula, but if you have an Excel table, then it’s best to use structured references. You can also use Power Query to do this. In this tutorial, I’m going to cover all these different methods to calculate running totals in Excel. So let’s get started!
Calculating Running Total with Tabular Data
If you have tabular data (i.e., a table in Excel which is not converted into an Excel table), you can use some simple formulas to calculate the running totals.
Using the Addition Operator
Suppose you have date-wise sales data and you want to calculate the running total in column C.
Below are the steps to do this. Step 1 – In cell C2, which is the first cell where you want the running total, enter This will simply get the same sale values in cell B2.
Step 2 – In cell C3, enter the below formula:
Step 3 – Apply the formula to the entire column. You can use the Fill handle to select and drag it, or simply and copy-paste the cell C3 to all the remaining cells (which would automatically adjust the reference and give the right result). This will give you the result as shown below.
It’s a really simple method and works well in most cases. The logic is simple – every cell picks up the value above it (which is the cumulative sum till the date before) and adds the value in the cell adjacent to it (which is the sale value for that day). There is only one drawback – in case you delete any of the existing rows in this data set, all the cells below that would return a reference error (#REF!)
If that’s a possibility with your data set, use the next method that uses the SUM formula
Using SUM with Partially Locked Cell Reference
Suppose you have date-wise sales data and you want to calculate the running total in column C.
Below is the SUM formula that will give you the running total.
Let me explain how this formula works. In the above SUM formula, I have used the reference to add as $B$2:B2
$B$2 – this is an absolute reference, which means that when I copy the same formula in the cells below, this reference is not going to change. So when copying the formula in the cell below, the formula would change to SUM($B$2:B3) B2 – this is the second part of the reference which is a relative reference, which means that this would adjust as I copy the formula down or to the right. So when copying the formula in the cell below, this value will become B3
The great thing about this method is that in case you delete any of the rows in the data set, this formula would adjust and still give you the right running totals.
Calculating Running Total in Excel Table
When working with tabular data in Excel it’s a good idea to convert it into an Excel table. It makes it a lot easier to manage the data and also allows makes it easy to use tools such as Power Query and Power Pivot. Working the Excel tables comes with benefits such as structured references (which makes it really easy to refer to the data in the table and use it in formulas), and automatic adjustment of references in case you add or delete data from the table. While you can still use the above formula that I have shown you in an Excel table, let me show you some better methods to do this. Suppose you have an Excel table as shown below and you want to calculate the running total in column C.
Below is the formula that will do this:
The above formula may look a bit long, but you don’t have to write it yourself. what you see within the sum formula are called structured references, which is Excel’s efficient way to refer to specific data points in an Excel table. For example, SalesData[[#Headers],[Sale]] refers to the Sale header in the SalesData table (SalesData is the name of the Excel table that I gave when I created the table) And [@Sale] refers to the value in the cell in the same row in the Sale column. I just explained this here for your understanding, but even if you know nothing about structured references, you can still easily create this formula. Below are the steps to do this: You will also notice that you don’t have to copy the formula in the entire column, an Excel table automatically does it for you. Another great thing about this method is that in case you add a new record in this data set, the Excel table would automatically calculate the running total for all the new records. While we have included the header of the column in our formula, remember that the formula would ignore the header text and only consider the data in the column
Calculating Running Total Using Power Query
Power Query is an amazing tool when it comes to connecting with databases, extracting the data from multiple sources, and transforming it before putting it into Excel. If you are already working with Power Query, it would be more efficient to add running totals while you are transforming the data within the Power Query editor itself (instead of 1st getting the data in Excel and then adding the running totals using any of the above methods covered above). While there is no inbuilt feature in Power Query to add running totals (which I wish there was), you can still do that using a simple formula. Suppose you have an Excel table as shown below and you want to add the running totals to this data:
Below are the steps to do this: The above steps would insert a new sheet in your workbook with a table that has the running totals.
Now if you’re thinking that these are just too many steps as compared to the previous methods of using simple formulas, you’re right. If you already have a data set and all you need to do is add running totals, it’s better not to use Power Query. Using Power Query makes sense where you have to extract data from a database or combine data from multiple different workbooks and in the process also add running totals to it. Also, once you do this automation using Power Query, the next time your data set changes you do not have to do it again you can simply refresh the query and it would give you the result based on the new data set. How does this work? Now let me quickly explain what happens in this method. The first thing we do in the Power Query editor is to insert an index column starting from one and incrementing by one as it goes down the cells. We do this because we need to use this column while we calculate the running total in another column that we insert in the next step. Then we insert a custom column and use the below formula This is a List.Sum formula that would give you the sum of the range that is specified within it. And that range is specified using the List.Range function. The List.Range function gives the specified range in the sale column as the output and this range changes based on the Index value. For example, for the first record, the range would simply be the first sale value. And as you go down the cells, this range would expand. So, for the first cell. List.Sum would only give you the sum of the first sale value, and for the second cell, it would give you the sum for the first two sale values and so on.
Calculating Running Total Based on Criteria
So far, we have seen examples where we have calculated the running total for all the values in a column. But there could be cases where you want to calculate the running total for specific records. For example, below I have a data set and I want to calculate the running total for Printers and Scanners separately in two different columns.
This can be done using a SUMIF formula that calculates the running total while making sure the specified condition is met. Below is the formula that will do this for the Printer columns:
Similarly, to calculate the running total for Scanners, use the below formula:
In the above formulas, I have used SUMIF, which would give me the sum in a range when the specified criteria are met. The formula takes three arguments: Also, in the range and sum_range argument, I have locked the second part of the reference, so that as we go down the cells, the range would keep expanding. This allows us to only consider and add values till that range (hence running totals). In this formula, I have used the header column (Printer and Scanner) as the criteria. you can also hardcode the criteria if your column headers are not exactly the same as the criteria text. In case you have multiple conditions that you need to check, then you can use the SUMIFS formula.
Running Total in Pivot Tables
If you want to add running totals in a Pivot Table result, you can easily do that using an inbuilt functionality in Pivot tables. Suppose you have a Pivot Table as shown below where I have the date in one column and the sale value in the other column.
Below are the steps to add an additional column that will show the running total of the sales by date: The above steps would change the second sale column into the Running Total column.
So these are some of the ways you can use to calculate the running total in Excel. If you have data in tabular format, you can use simple formulas, and if you have an Excel table, then you can use formulas that make use of structured references. I’ve also covered how to calculate running total using Power Query and in Pivot Tables. I hope you found this tutorial useful. Other Excel tutorials you may also like:
How to Sum a Column in Excel Calculating Moving Average in Excel [Simple, Weighted, & Exponential] Excel Quick Analysis Tool – How to Best Use it? 10 Examples!
