How to Calculate a Running Total in Excel

A running total, also known as a cumulative sum, is a series of partial sums of a given set of data. It shows the sum of the values up to a certain point in time. For example, if you have monthly sales data, a running total would tell you the total sales from the beginning of the year until a specific month.

Running totals are useful for analyzing trends, tracking progress, and comparing performance over time. You can use them for various purposes, such as managing your budget, monitoring your inventory, or measuring your sales quota.

In this article, you will learn how to calculate a running total in Excel using different methods. You will also learn how to create a running total chart to visualize your data.

Method 1: Using the SUM Function with Relative and Absolute References

One of the simplest ways to calculate a running total in Excel is to use the SUM function with a combination of relative and absolute cell references. A relative reference changes when you copy a formula to another cell, while an absolute reference remains the same.

To use this method, follow these steps:

1. Enter your data in a worksheet. For this example, we will use the following sales data for a store.

2. In the cell next to the first value, enter the SUM function with the same value as the argument. For example, in cell C2, enter =SUM(B2). This will return the same value as in cell B2.
3. In the cell below, enter the SUM function with the previous cell and the current cell as the arguments. For example, in cell C3, enter =SUM(C2,B3). This will add the value in cell C2 (the running total for Jan) and the value in cell B3 (the sales for Feb).
4. Copy the formula down to the rest of the cells in the column. You can use the fill handle or the keyboard shortcut Ctrl + D. This will create a running total for each month.
5. To make the formula more robust, you can use absolute references for the first cell in the argument. This will ensure that the formula always refers to the first value in the data, even if you insert or delete rows. To do this, add a dollar sign ($) before the column and row references. For example, change the formula in cell C3 to =SUM(C2,$B$2:B3).

The result should look like this:

Method 2: Using the Addition Operator

Another way to calculate a running total in Excel is to use the addition operator (+) to add the previous cell and the current cell. This method is similar to the previous one, but it does not use the SUM function.

To use this method, follow these steps:

1. Enter your data in a worksheet. For this example, we will use the same sales data as before.
2. In the cell next to the first value, enter the same value. For example, in cell C2, enter =B2. This will return the same value as in cell B2.
3. In the cell below, enter the addition operator (+) with the previous cell and the current cell as the operands. For example, in cell C3, enter =C2+B3. This will add the value in cell C2 (the running total for Jan) and the value in cell B3 (the sales for Feb).
4. Copy the formula down to the rest of the cells in the column. You can use the fill handle or the keyboard shortcut Ctrl + D. This will create a running total for each month.
5. To make the formula more robust, you can use absolute references for the first cell in the operand. This will ensure that the formula always refers to the first value in the data, even if you insert or delete rows. To do this, add a dollar sign ($) before the column and row references. For example, change the formula in cell C3 to =C2+$B$2.

The result should look like this:

Method 3: Using an Excel Table

If you have your data in an Excel table, you can use structured references to calculate a running total. Structured references are a way of referring to table elements, such as columns, rows, or headers, by their names. They make the formulas easier to read and maintain.

To use this method, follow these steps:

1. Convert your data into an Excel table. To do this, select your data and press Ctrl + T. Alternatively, you can go to the Insert tab and click on Table. In the Create Table dialog box, check the option “My table has headers” and click OK.
2. In the cell next to the first value, enter the SUM function with the same value as the argument. For example, in cell C2, enter =SUM([@Sales]). This will return the same value as in cell B2. The [@Sales] refers to the current row in the Sales column.
3. In the cell below, enter the SUM function with the previous cell and the current cell as the arguments. For example, in cell C3, enter =SUM(C2,[@Sales]). This will add the value in cell C2 (the running total for Jan) and the value in cell B3 (the sales for Feb).
4. Copy the formula down to the rest of the cells in the column. You can use the fill handle or the keyboard shortcut Ctrl + D. This will create a running total for each month.
5. To make the formula more robust, you can use the INDEX function with a structured reference for the first cell in the argument. This will ensure that the formula always refers to the first value in the data, even if you insert or delete rows. To do this, replace the previous cell reference with the INDEX function and the column name as the argument. For example, change the formula in cell C3 to =SUM(INDEX([Sales],1),[@Sales]). The INDEX([Sales],1) returns the first value in the Sales column.

The result should look like this:

Method 4: Using Power Query

Power Query is a powerful tool that allows you to import, transform, and analyze data from various sources. You can use Power Query to calculate a running total in Excel without using any formulas.

To use this method, follow these steps:

1. Enter your data in a worksheet. For this example, we will use the same sales data as before.
2. Select your data and go to the Data tab. Click on From Table/Range. This will open the Power Query Editor.
3. In the Power Query Editor, go to the Add Column tab. Click on the Index Column drop-down and select From 1. This will add a new column with sequential numbers starting from 1.
4. Click on the Index column header and hold the Ctrl key. Then click on the Sales column header. This will select both columns.
5. Go to the Add Column tab again. Click on the Standard drop-down and select Sum. This will add a new column with the sum of the selected columns.
6. Click on the Sum column header and go to the Transform tab. Click on the Statistics drop-down and select Running Total. This will calculate the running total for each row.
7. Rename the Sum column as Running Total by double-clicking on the column header and typing the new name.
8. Go to the Home tab and click on Close & Load. This will load the query result to a new worksheet.

The result should look like this:

Method 5: Using a Pivot Table

A pivot table is a tool that allows you to summarize and analyze data from a large data set. You can use a pivot table to calculate a running total in Excel without using any formulas or Power Query.

To use this method, follow these steps:

1. Enter your data in a worksheet. For this example, we will use the same sales data as before.
2. Select your data and go to the Insert tab. Click on PivotTable. In the Create PivotTable dialog box, choose where you want to place the pivot table and click OK.
3. In the PivotTable Fields pane, drag the Month field to the Rows area and the Sales field to the Values area. This will create a simple pivot table with the sum of sales for each month.
4. Right-click on any cell in the Values area and select Show Values As. In the Show Values As dialog box, select Running Total In from the drop-down list. Then, select Month from the Base Field list and click OK. This will calculate the running total for each month.
5. Optionally, you can format the pivot table to make it more readable. For example, you can change the number format, add a title, or apply a style.

The result should look like this: