Creating a running total in Excel is useful for tracking the cumulative sum of your data, helping you analyze trends over time.
In this article, we’ll explain three common methods to set up a running total, using the example shown in the image. Let’s dive in!
Method 1: Using Simple Addition Formulas
The most straightforward way to calculate a running total is by adding each month's sales to the previous running total.
- In cell C2, reference the first sales value:
=B2 - In cell C3, add February's sales to January's running total:
=B3 + C2 - Copy this formula down the column to create a running total for each month.
Explanation:
The formula B3 + C2 adds the February sales (B3) to the cumulative total of January (C2). Continue this pattern down the column to get a cumulative total for all months.
Method 2: Using the SUM Function with a Mixed Reference
Another method uses the SUM function with mixed references to calculate the running total more efficiently.
- In cell C2, write the following formula:
=SUM($B$2:B2) - As you can notice, only the first reference is dollars, not the second one. That's the trick 😉
- Copy this formula down the column.
- The last formula is:
=SUM($B$2:B13)Because only the second reference is relative
Explanation:
The dollar signs in $B$2 lock the starting point of the range, while B3 updates as you copy the formula down. This method recalculates the running total for all values from January to the current month.
Important: This method uses more memory because it recalculates the entire range each time, while the first method only adds two values, making it simpler and more efficient.
Method 3: Using Quick Analysis for a Running Total
Excel’s Quick Analysis tool provides a faster way to calculate a running total without writing formulas manually.
- Select the sales data range (B2).
- Click on the Quick Analysis icon at the bottom right corner of the selection.
- Choose Totals and then select Running Total from the options.
Explanation:
The Quick Analysis tool generates a running total, saving you from manually writing formulas. However, the formula created is the same as the second method, with extensive memory use.
