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. The following example shows how to set up the formula to calculate the running total.
-
Create a worksheet with this example data.
-
Create a blank workbook or worksheet.
-
Copy and paste the following table into the blank worksheet, starting at cell A1. Include the headers. ​​​​​​​
Date
Product
Quantity
Formula
Expected Formula Result
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
Look closely at the formulas in column D. The first part of the range is an absolute reference (both the column and row are preceded by the $ symbol). 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.
-
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.
-
-
To maintain the running total, add a row for each new entry and copy the formula from column D to that row. You can drag the fill handle to copy the formula into subsequent rows with automatic formula adjustments.
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.
