Running Total In Excel

Article byWallstreetmojo Team
Edited bySheeba M
Reviewed byDheeraj Vaidya, CFA, FRM

What Is Excel Running Total?

Running Total in Excel, also called the “Cumulative Sum”, is the summation of numbers increasing or growing in quantity, degree, or force by successive additions. It is the total that gets updated when there is a new entry in the data. In Excel, the usual way to calculate the total is by using the SUM function. So, to calculate the running total to see the data changes with every new entry, the first-row reference must be absolute.

For example, in cell D2, enter the formula =SUM(D1,C1), press “Enter“, and drag the formula to the rest of the cells, i.e., D2:D13. We get the output shown below.

Running Total in Excel

You are free to use this image on your website, templates, etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: Running Total In Excel (wallstreetmojo.com)

Key Takeaways

How To Calculate Running Total (Cumulative Sum) In Excel?

We can calculate the Running Total in Excel using the following methods, namely,

  1. Using the Simple Formula.
  2. Using the “SUM” Formula.
  3. Using a Pivot Table.
  4. Using a Relative Named Range.

We will consider these methods in detail with specific examples.

–>> If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

Download Template

This article must help understand Running Total in Excel with its formulas and examples. You can download the template here to use it instantly.

You can download this Running Total Calculation Excel Template here – Running Total Calculation Excel Template

Examples

We will consider examples to calculate the Running Total in Excel.

Example #1 – Performing “Running Total or Cumulative” with Simple Formula

The steps to calculate the Running Total using a Simple or Direct formula are as follows:

  1. First, assume that we have the data on our expenses every month as follows:


    Running Total Example 1

  2. Select cell C14, enter the formula =SUM(C2:C13), and press “Enter”.

    From this data, we can observe that we spent 3,25,000 in total from January to December.

    Running Total Example 1-1

  3. Now, let us see how much of my total expenses were made by the month end.

    We will use a simple formula to calculate, select cell D2, and enter the formula =C2.

    Running Total Example 1-2

  4. First, we should consider the amount spent in a particular month, January, as we contemplate our spent calculation from January.


    Running Total Example 1-3

  5. Now, calculate the money spent for the rest of the months as follows:

    Select cell D3, and enter the formula =D2+C3. (February)

    Running Total Example 1-4

  6. Press “Enter”. The running total for February month is 45,000.


    Running Total Example 1-5

    For the next month onwards, we have to consider the money spent till the previous month and the money spent in the current month. Therefore, for the month of March, select cell D3, and the formula will be =C4+D3.

  7. Similarly, drag the formula from cell D3 to D13 using the fill handle for the rest of the months. The result is shown below.


    Running Total Example 1-6

    From the above result, we can observe that by the end of the year in December, we had spent 3,25,000, which is the total amount from the start of the year. Therefore, this running total will tell us how much we spent on a particular month.

  8. Until July, we had spent 1,88,000. Till November we had spent 2,94,000.


    Running Total Example 1-7

We can also use this data (running total) for certain analyses, as follows:

Q1. If we want to know by which month we had spent 90,000.

Ans. April – We can see the “Cumulative/Running Total” column from the table. It shows that the cumulative spending till April month is 90,000.

Running Total Example 1-8

Q2. Suppose we want to know the % of money spent that we had spent till July.

Ans. 58% – We can take the amount spent till July and divide it by the total amount spent as follows.

Select cell E8, enter the formula =D8/C14, and make C14 absolute by pressing the F9 key.

Running Total Example 1-9

Press “Enter”. We have spent 58% of the money until July, as shown below.

Running Total Example 1-10

Example #2 – Performing “Running Total or Cumulative” with “SUM” Formula

In this example, we will use the SUM in excelSUM In ExcelThe SUM function in excel adds the numerical values in a range of cells. Being categorized under the Math and Trigonometry function, it is entered by typing “=SUM” followed by the values to be summed. The values supplied to the function can be numbers, cell references or ranges.read more instead of the “+” operator to calculate the cumulative in Excel.

Running Total Example 2

But, first, apply the SUM Formula in excel. Select cell D2, and enter the formula =SUM(D1,C2)

Running Total Example 2-1

While using the SUM function, we should consider summing the earlier month spent and the current month spent. But for the first month, we should add earlier cells, i.e., cumulative, which will be regarded as zero.

Press “Enter”. The result in cell D2 is 25000, as shown below.

Running Total Example 2-2

Then, drag the formula to other cells, i.e., from cell D2 to D13, using the fill handle. The final output is shown below.

Running Total Example 2-3

Example #3 – Performing “Running Total or Cumulative” with a PivotTable

To perform running total using a PivotTable in Excel, we should first generate a PivotTable. Create a pivot tablePivot TableA Pivot Table is an Excel tool that allows you to extract data in a preferred format (dashboard/reports) from large data sets contained within a worksheet. It can summarize, sort, group, and reorganize data, as well as execute other complex calculations on it.read more by selecting the table, and clicking on the PivotTable option from the “Insert” tab.

Running Total Example 3

Once the PivotTable is generated the “PivotTable Fields” pane appears on the right. Now, drag the “Month” column into the “Rows” field, and the “Amount Spent” column into the “Values” field. The table would be as follows:

Running Total Example 3-1

Again, drag the “Amount Spent” column into the “Values” field to create a total running value. Then, right-click on the column as follows:

Click “Show Value As” option, and you will get an option of “Running Total In”.

Example 3-2

Now, we can see the table with a column having cumulative values as follows:

Example 3-3

We can change the table’s name by editing the cell with a “Sum of Amount Spent2”.

Example #4 – Performing “Running Total or Cumulative” with a Relative Named Range

We need to make temporary changes in the Excel options to perform running total with a relative named range.

Change Excel referenceExcel ReferenceCell reference in excel is referring the other cells to a cell to use its values or properties. For instance, if we have data in cell A2 and want to use that in cell A1, use =A2 in cell A1, and this will copy the A2 value in A1.read more style from A1 to R1C1 from Excel options as below:

First, in a workbook, select the “File” tab.

Example 4 - 1

Select the “Options” option, as shown below.

Example 4 - 2

The “Excel Options” window appears. Select the “Formulas” option on the left, and on the right check, the “R1C1 reference style” checkbox, which refers to “Row 1” and “Column 1”, and click “OK”.

Example 4 (Excel Options)

Then, this style can find positive and negative signs used for a reason.

In Rows: –

  • The +(positive) sign refers to a “Downward” direction.
  • The – (negative) sign refers to an “Upward” direction.

Ex- R[3] connects the cell 3 rows below the current cell. R[-5] connects the cell 5 rows above the current cell.

In Columns: –

  • The +(positive) sign refers to the “Right” direction.
  • The – (negative) sign refers to the “Left” direction.

Ex- C[2] refers to connecting the cell, which is 2 columns right to the current cell, and C[-4] refers to connecting the cell, which is 4 columns left to the current cell.

To use the reference style to calculate the running total, we must define a name with certain criteria.

In our example, we have to define the name by “R[-1]C” because we are calculating the cumulative sum of the cell’s previous row and column with every month’s expense.

The procedure to Define a name in Excel as desired, here “Cum” is,

Go to the “Formulas” tab, and select “Define Name”.

Running Total Example 4-1

Then, the “New Name” window pops up, and give the name per your wish and the condition you want to perform for the name you defined. Here, we take R[-1]C because we will sum the previous row of the cell and column with every month’s expense.

Example 4-2

Once the name is defined, then go to the column of “Cumulative/Running Total”, select cell C2, enter the formula =SUM(RC[-1],Cum), and use the defined name in the SUM function as follows:

Example 4-3

Press “Enter”. It tells us to perform SUM with the cell “RC[-1}” and “Cum” (Which is already defined), and in the first cell, we get the same expense incurred in January.

Example 4-4

Then, drag down the formula to the end of the table. We can see the cumulative results, as shown below.

Example 4-5

Important Things To Note

  • A relatively named range running total is performed to avoid the problems with inserting and deleting rows from the data. This operation will refer to the cell as per the condition given, even though we insert or delete rows or columns.
  • The “Total Sum” and “Running Total” are different. The key difference is the computation we do. The “Total Sum” will perform the sum of each number in the data series. At the same time, the “Running Total” will sum the previous value with the current value from the data.

Frequently Asked Questions (FAQs)

1. What is the purpose of Running Total in Excel?

In a large dataset with multiple formulas, we may have to keep on updating few formula cells or data cells, that may change or displace the formulas.
Here, Running Total in Excel is ideal because it is the sum of numeric values in a dataset that keeps on increasing despite of modifications or additions in data.

2. Are Running Total and Total Sum the same?

The “Total Sum” and “Running Total” are different. The key difference is the calculations performed to derive the results.
The “Total Sum” finds the sum of each selected number in the data series.
The “Running Total” will add the previous value with the current value in the dataset.

3. Name the methods available to find the Running Total in Excel?

We can calculate the Running Total in Excel using the following methods, namely,
1) Using the Simple Formula.
2) Using the “SUM” Formula.
3) Using a Pivot Table.
4) Using a Relative Named Range.

Recommended Articles

This article is a guide to Running Total in Excel. Here we calculate Running Total using SUM, PivotTable, Relative named range, example, downloadable template. You may learn more about Excel from the following articles: –

Reader Interactions

Leave a Reply

Your email address will not be published. Required fields are marked *