Running Total in Excel

A running total is a very useful mathematical operation which is very simply to build in Excel.

When to do a running total?

A running total allows you to accumulate numerical data in order to show the evolution of a trend.

In the following image, you have the sales (column B) and the running total of the sales in a store month by month (column C).

Running Total animated

Construction of a Running Total

To make a cumulative sum, take cells 2 to 2 with a difference for the first formula.

First formula

The formula for the first cell is extremely simple. It suffices to recall the reference of the first cell in our series.

=B2

Running Total first formula

The other formulas

To calculate the cumulative amount of January and February, all you have to do is add up the February turnover (in B3) with the previous cumulative amount (i.e. C2).

The formula in C3 is then written

= C2 + B3

Second formula for a running total

Copy of the formula

It only remains now to copy this formula for the rest of the document.

But beware, with a "classic" copy to the fill-handle, you will destroy the format of your document. To copy only the formulas, it's better to use the paste special, option formula or right-click when copying, then select "Fill Without Formatting"

Running Total animated

Why does it work?

In fact, all the references in our cells are relative references . That is to say that the references will follow the direction of the copying. As you can see, the row's references have been increased by one automatically.

The references change during the copy of the formula

There are other situations where you must build a formula where the references don't move when copying a formula. These are called absolute references and these references are characterized by the appearance of the symbol $. It's the case in this article to calculate the VAT.

Formula to add the tax directly to the price

Another technique: block a single reference.

It's possible to make a running total with the SUM function by blocking only a part of the range of cells. This method uses a lot the memory of your computer but it impresses all your colleagues at work 😎

Construction of the formula

  • Write a SUM function
  • Block the first reference
  • Leaving the second reference free

The formula is as follows

The formula is as follows

=SUM($A$2:A2)

Running total with the SUM function and only one cell blocked

Explanation of references

  • By copying down, the first reference always remains in A2 (absolute reference with the $).
  • Then, the copy progressively increase the reference of the second cells.
  • Thus the parameters of the Sum function always correspond to the n preceding cells.

Related posts


Permanent link to this article: https://www.excel-exercise.com/running-total-in-excel/

Leave a Reply

Your email address will not be published.