How to set up a running total column in Excel

Have you needed to set up a running total column in Excel? Perhaps you needed to maintain an account balance in the far right column of a worksheet. And then did you find yourself using one formula for the first row in the column, and then another formula to start adding the current row to the previous row? Well, as you know that method works. It’s just a bit quirky looking — and if you want to use that method in a table you aren’t able to take advantage of the autofilling capabilities of the table.

Here is a simpler way. It uses a mix of absolute and relative references on a range within the sum function.

So, if you originally had a series of formulas that looked like the following (say, starting in column B1):

=A1
=A2 + B1
=A3 + B2

… autofill to bottom of column …

You can use the following instead:

=SUM(A$1:A1)

… autofill to bottom of column …

This method is easier to see and since its the same formula being autofilled, it will work in a table in Excel as well.

Watch the method in action

This brief video on YouTube illustrates the running total column in Excel. Also, you’ll notice that it’s being used in an Excel table where it’s best to keep the formulas the same for each of the rows in a column.

Why does it work?

The SUM function above is adding up the values in the entire range. Initially the range is from cell A1 to cell A1. In other words, just cell A1. In the next cell, the range will become A1 to A2. Why? Because the “$” character in front of the “1” indicates the reference is absolute. Or rather, does not change. Any reference that is not absolute is relative. Meaning, it will change up or down as you autofill up or down. So, the second “1” becomes a “2” as you fill down, and so on.

Click here to read my post about absolute versus relative Excel references.

There you go.