Calculate a running total of a column of cells in Excel

If you have a column of numbers and you want to calculate a running total of the numbers in each row, you have two options open two you:

1. You can create a simple addition formula
2. You can write a formula using the SUM() function which utilizes absolute and relative references.

Imagine your spreadsheet has values in cells A2 through to B16. You want to put a running total of sales (column B) in column C:

As you can see, column C shows the cumulative running total of the sales in column B.

Option 1 – create a simple addition formula in the second row, and copy it down the column

To use this method, you need two simple formulas

First, enter this formula into C2 (using the example above)

=B2

Then, in C3, enter this formula:

=C2+B3

Finally, copy and paste the formula from C3 into C4, C5, C6 and so on down the column.

You should end up with a spreadsheet that looks like the example below. Column D shows the formulas that have been entered into column C.

• Cell C2 is equal to the value in B2
• C3 takes the value in C2 and adds the value in B3. This is the running total so far.
• C4 takes the value in C3 and adds to it the value in B4 to get the running total for row 4.
• This continues down the table.

This method works well, but has one key limitation. The spreadsheet is currently sorted by Date. If you sort the table of data by a column other than Date, such as Sales, the formula will break. This is because all the values from cell C3 downwards rely on the value in C2. If the value in C3 is moved elsewhere in the table, the running totals will no longer calculate correctly, as you’ll see in the example below. The table has been sorted by Sales, from the smallest sales to the largest, and the formula that was in B2 is no longer first in the list. The result in this example will look like this:

As you can see, there is a #VALUE error in C2. Also, the formula that was in C1 is now in column C2, so the value in B2 is no longer being included in the overall total.

Notice how the formula in C2 now tries to add the value in C1 to the value in B2. The #VALUE error in C2 indicates that the formula cannot calculate a result because of a problem with the values in the cells it is referencing. In this case, the problem is that C1 contains words, not numbers.

Notice how this table changes if we sort it so that Sales go from largest to smallest:

In this example, the long list of #VALUE errors means you could be forgiven for thinking that your whole spreadsheet has been broken. However, all that has happened is that the formula that was in the first row of data is now last. All of the other formulas are returning a #VALUE error because of the formula in C2, which attempts to add words to a number. The formula in C3 then tries to add #VALUE to a number, which also return a #VALUE error – so the problem cascades down to the end, where the formula in C16 finally returns a number – the value in B16.

Option 2 – use the SUM function with absolute and relative references

Let’s look at another method of calculating a running total that doesn’t have the same problem when it is sorted. This method works because of the way it uses the SUM function plus a combination of absolute and relative references.

Here’s our original example, with column D now showing that a different formula is being used in column C:

To reproduce this you would do the following:

• In cell C2, enter the following formula:
  • =SUM($B$2:B2)
• This will put the value of B2 into C2. The SUM function here is adding up all the cells between $B$2 and B2 – which is just one cell, B2.
• Remember that $B$2 is an absolute reference. This means that anywhere you copy this formula, it will always refer back to B2.
• Then, copy this formula and paste it into cell C3. The formula should now look like this:
  • =SUM($B$2:B3)
• This will put the sum of all the cells between B2 and B3 into C3.
  • Note that, as promised, $B$2 hasn’t changed even though you have copied the cell from one place to another.
  • On the other hand, B2 was a relative reference in the previous cell, so it changed from B2 to B3 when you copied and pasted the formula from C2 to C3.
• Once you’ve verified that this works as described, you can then copy the formula into each cell from C2 to C16. The final cell, C16, will contain the total of all the numbers in the cells from B2 to B16 (i.e.  $B$2:B16).

Now, let’s check what happens if we sort this table. The following two examples show the table sorted first from the lowest Sales value to the highest, and then sorted from the highest Sales value to the lowest, just  as in Option 1. This time, however, the running total still works, with no #VALUE errors:

Sales lowest to highest – formulas continue to refer back to B2 as the starting point

Sales highest to lowest

Note that there are some scenarios where a formula like this is more than you need. Check out this comment to see another way to do it that allows to calculate a running balance based on two columns of numbers.

Finally, a common requirement is to calculate a running total based on data between two dates. Click the link to see now to do this.

Do you have any questions about on this lesson, or a specific problem you need to solve? If so, please leave a comment below.

Lesson Subject – Excel Formulas and Functions
Related Lessons – 16, 22, 233, 19