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

By Ali, Founder, Excel Lessons

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.

Read more about this

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

Lock the start of a range. Leave the end free.

In C2, type:

=SUM($B$2:B2)

That totals savings from Jan to this row.

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

    Lock the start of a range. Leave the end free. In C2, type: =SUM($B$2:B2). That totals savings from Jan to this row.

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

  2. 2

    That formula was filled from C2 to C5. Read =SUM($B$2:B5) and say what it returns.

    Hint. Four months added together.

  3. 3

    C6 was filled without dollar signs, so its whole range moved. Fix C6.

    Hint. Pin the start of the range.

  4. 4

    There is a second way to build a running total. In C7, add C6 and B7.

    Hint. C6 plus B7.

  5. 5

    In B9, total B2:B8 with SUM. That should match the last balance in C8.

    Hint. B2 to B8.