Tuesday, October 13, 2020

Calculate a running total in excel

You can use a running total to watch the values of items in cells add up as you enter new items and values over time. To calculate a running total, use the following procedure.

  1. Set up a worksheet like the following example.

    The example may be easier to understand if you copy it to a blank worksheet.

    How to copy an example

    1. Create a blank workbook or worksheet.

    2. Select the example in the Help topic.

      Note: Do not select the row or column headers.

      Select sample data

    3. Press CTRL+C.

    4. In the worksheet, select cell A1, and press CTRL+V.

    5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

      1

      2

      3

      4

      5

      6

      7

      8

      9

      10

      11

      12

      13

      A

      B

      C

      D

      E

      Date

      Product

      Quantity

      Formula

      Running total

      2-Jan

      Beach toys

      5

      =SUM($C$2:$C2)

      5

      2-Jan

      Sunglasses

      3

      =SUM($C$2:$C3)

      8

      3-Jan

      Beach toys

      9

      =SUM($C$2:$C4)

      17

      3-Jan

      Sunglasses

      8

      =SUM($C$2:$C5)

      25

      3-Jan

      Swing set

      1

      =SUM($C$2:$C6)

      26

      4-Jan

      Beach toys

      6

      =SUM($C$2:$C7)

      32

      4-Jan

      Sunglasses

      4

      =SUM($C$2:$C8)

      36

      5-Jan

      Beach toys

      11

      =SUM($C$2:$C9)

      47

      5-Jan

      Sandbox

      1

      =SUM($C$2:$C10)

      48

      5-Jan

      Sunglasses

      5

      =SUM($C$2:$C11)

      53

      5-Jan

      Swing set

      2

      =SUM($C$2:$C12)

      55

      If you look closely at the formulas in column D, you will see that the first part of the range is an absolute reference (both the column and row are preceded by the $ symbol), and that the second part of the range is a mixed reference (only the column is preceded by the $ symbol). Entering the formula in this way enables it to work as a running total when you copy it to adjacent rows.

  2. To maintain the running total, add a row for each new entry and copy the formula from column D to that row.

    You do not need to manually enter or copy the formula in each row. For example, you can enter the formula in just cell D2. Then simply copy the formula to other rows that contain data by dragging the fill handle to cell D13.

Note: A running total differs from a running balance, in which you watch the sum of values grow or shrink (for example, a checkbook register) as new entries are added.

  1. Set up a worksheet like the following example.

    The example may be easier to understand if you copy it to a blank worksheet.

    How to copy an example

    1. Create a blank workbook or worksheet.

    2. Select the example in the Help topic.

      Note: Do not select the row or column headers.

      Select sample data

    3. Press COMMAND + C .

    4. In the worksheet, select cell A1, and then press COMMAND + V .

    5. To switch between viewing the results and viewing the formulas that return the results, press CONTROL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

      1

      2

      3

      4

      5

      6

      7

      8

      9

      10

      11

      12

      13

      A

      B

      C

      D

      E

      Date

      Product

      Quantity

      Formula

      Running total

      2-Jan

      Beach toys

      5

      =SUM($C$2:$C2)

      5

      2-Jan

      Sunglasses

      3

      =SUM($C$2:$C3)

      8

      3-Jan

      Beach toys

      9

      =SUM($C$2:$C4)

      17

      3-Jan

      Sunglasses

      8

      =SUM($C$2:$C5)

      25

      3-Jan

      Swing set

      1

      =SUM($C$2:$C6)

      26

      4-Jan

      Beach toys

      6

      =SUM($C$2:$C7)

      32

      4-Jan

      Sunglasses

      4

      =SUM($C$2:$C8)

      36

      5-Jan

      Beach toys

      11

      =SUM($C$2:$C9)

      47

      5-Jan

      Sandbox

      1

      =SUM($C$2:$C10)

      48

      5-Jan

      Sunglasses

      5

      =SUM($C$2:$C11)

      53

      5-Jan

      Swing set

      2

      =SUM($C$2:$C12)

      55

      If you look closely at the formulas in column D, you will see that the first part of the range is an absolute reference (both the column and row are preceded by the $ symbol), and that the second part of the range is a mixed reference (only the column is preceded by the $ symbol). Entering the formula in this way enables it to work as a running total when you copy it to adjacent rows.

  2. To maintain the running total, add a row for each new entry and copy the formula from column D to that row.

    You do not need to manually enter or copy the formula in each row. For example, you can enter the formula in just cell D2. Then simply copy the formula to other rows that contain data by dragging the fill handle to cell D13.

Note: A running total differs from a running balance, in which you watch the sum of values grow or shrink (for example, a checkbook register) as new entries are added.

No comments:

Post a Comment