Running Total in Excel

Running Total in Excel
Last Updated on 02/10/2023
Reading time: 3 minutes

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).

Running Total animated

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

Running Total first formula

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

Second formula for a running total

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"

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

=SUM($A$2:A2)

Running total with the SUM function and only one cell blocked

Explanation of references

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

Leave a Reply

Your email address will not be published. Required fields are marked *

Running Total in Excel

Reading time: 3 minutes
Last Updated on 02/10/2023

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).

Running Total animated

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

Running Total first formula

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

Second formula for a running total

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"

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

=SUM($A$2:A2)

Running total with the SUM function and only one cell blocked

Explanation of references

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

Leave a Reply

Your email address will not be published. Required fields are marked *