A running total is a very useful mathematical operation that is very simple 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).
Step 1: First formula is different of the other
To make a running total, there is a difference between the first formula and the others.
The formula for the first cell is extremely simple. It suffices to recall the reference of the first cell in our series.
=B2
Step 2: Write 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
Step 3: 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 special collage in formula (opens in a new tab) the paste special (option formula) or right-click when copying, then select "Fill Without Formatting"
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.
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.
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)
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.
