Wednesday, 17 April 2013

Add a Running Total Column to your Spreadsheet

A running total column can be added to a spreadsheet to maintain a cumulative total for a series of values. This could be used to track a list of inventory, or a list of account transactions.

In the example below I want to add a running total of column B into column C.

List of values

Add a Running Total Column

Click in cell C2 and enter the formula =SUM($B$2:$B2).

This formula uses an absolute reference for the beginning of the range and then a relative reference to the row number at the end of the range. This will ensure that the number of rows added will change as the formula is copied down (Read more about relative and absolute cell references). 

As more values are added to column B, this formula can be copied down to keep the running total going.

After copying the formula down you may encounter a warning message like the one shown below.

Warning about formula with additional values

This message warns us that the formula does not include all the values from column B and that we may have made a mistake. Do not worry if this message does not appear. It can be disabled which we will look at now.

Modify the Error Checking Rules

Excel will display an alert if it believes you have made a mistake in a formula. It has a variety of alerts to handle common formula errors. These alerts can be disabled and enabled at your discretion.

1.  Click the File tab on the Ribbon and select Options

2.  Select the Formulas category on the left

3.  Uncheck the Formulas which omit cells in a region checkbox

4.  You may recognise some of the other error checking options available. Customise as required.

Formula error checking options

5.  Click Ok to save and close the Excel Options dialog box


Watch the Video

1 comment:

  1. Dear,
    I have daily inventory report the quantity is say 100 Ctns (Cell say B5) need to detected 1 or 2 or or daily ( Cell say B1), want to reduce quantity using cell B1 each day i right number in cell B1 quantity detected from cell B5 ( I mean I use 2 Cells only one to right the quantity each day and the other quantity comes down as much as number each day i put.
    Thank you for your attention looking to have your reply through my email.