Excel Practice
Lessons Lesson 23

Running Totals

About this lesson

A running total is SUM over a range with a locked start and a relative end. Each filled-down copy adds one more row.

By the end you can

  • write a running total with a range whose start is locked.
  • fix a filled total that sums only its own row.
  • check a running total against the plain SUM of the column.

Practises SUM

The idea

A SUM whose range runs from $B$2 to B2 is the most useful formula in this module, and it looks like a typo. The start is locked and the end is not. Fill the formula down and the range stretches: $B$2:B3, $B$2:B4, all the way to the bottom. Every row shows the total so far.

Compare it with =C2+B3, the previous balance plus this row. Both work. The growing SUM survives a row being deleted from the middle. The chain of additions breaks there with #REF!. And the last row of either must equal the plain SUM of the column. That is the cheapest check there is. Keep it on the sheet.

The mistake to watch for

A running total filled down without the lock on the start of the range. Each row sums only itself. The column looks like a set of balances, but it is a set of copies. The check is free: the last balance must equal a plain SUM of the whole column. When they differ, a formula in the balance column has moved.

Where this comes up again

This lesson needs JavaScript to run. Everything below is the lesson in full, but you cannot type into the grid or be marked.

Type a formula

In C2, start a running balance: SUM of everything saved from B2 down to this row. Lock the start of the range and leave the end free. Then the range grows when the formula is filled down.

To begin, type it exactly:

=SUM($B$2:B2)

C2
Row A B C D E F G
1 Month Saved Balance
2 Jan 120
3 Feb 80
4 Mar 150
5 Apr 60
6 May 200
7 Jun 90
8 Jul 130
9 Year so far

Every step

  1. 1

    In C2, start a running balance: SUM of everything saved from B2 down to this row. Lock the start of the range and leave the end free. Then the range grows when the formula is filled down. To begin, type it exactly: =SUM($B$2:B2).

    Hint. A range from $B$2 to B2.

  2. 2

    Filled down to C5, the formula reads =SUM($B$2:B5). What does C5 show?

    Hint. January to April.

  3. 3

    C6 was filled down from a version without the dollar signs. Its range moved as a whole, so it now sums just one month. Fix it to be June's running balance.

    Hint. Pin the start of the range.

  4. 4

    There is a second way. In C7, take the balance above and add this month's saving: C6 plus B7.

    Hint. C6 plus B7.

  5. 5

    In B9, total the whole year so far with SUM. It should agree with the last balance in C8.

    Hint. B2 to B8.