Running Total in Excel (Cumulative Sum, by Group, in a Table)

Two ways to write a running total

Step 1: Running balance from the previous row

The Cash Book lists October’s transactions with money in (column C) and money out (column D). The opening balance of 8,500.00 is in F3. The first balance in E6 starts from it, and every row below adds to the balance above:

=$F$3+C6-D6

=E6+C7-D7

Copy the second formula down. Excel treats blank cells as 0, so a row with only money out still works. After 20,692.30 in and 15,147.45 out, October ends at 14,044.85.

Step 2: Running total with an expanding SUM

An expanding range has a locked start and a relative end. In F6, copied down:

=$F$3+SUM($C$6:C6)-SUM($D$6:D6)

In row 9 the ranges become $C$6:C9 and $D$6:D9: everything from the first row to the current row. The $ signs keep the start fixed (press F4 to add them). For a plain cumulative sum without an opening balance, it is just =SUM($C$6:C6). The Check column compares both methods: Ties on every row.

Step 3: Keep empty rows blank and tie out the result

Prepare rows for future transactions and the previous-row method repeats the last balance all the way down (rows 18 and 19, in red). Wrap the formula in IF so empty rows stay blank:

=IF(AND(C6="",D6=""),"",$F$3+SUM($C$6:C6)-SUM($D$6:D6))

Then prove the running balance with an independent calculation: opening + total in – total out. To get the last non-blank running balance, LOOKUP finds the last number in a column:

=LOOKUP(2,1/(F6:F19<>""),F6:F19)

Both give 14,044.85: Ties. =MIN(F6:F19) adds a useful cash figure: the lowest balance of the month, 5,105.50 after payroll on Oct 12. It tells you how much buffer the account really had.

The closing balance is what you agree to the bank statement in a bank reconciliation, and the same running balance drives a petty cash log.

Step 4: Running total in an Excel table

In an Excel table (Ctrl + T), formulas use column names instead of cell addresses. In the Running total column of the Sales table:

=SUM(INDEX([Amount],1):[@Amount])

INDEX([Amount],1) is the first cell of the Amount column and [@Amount] is the current row, so the range runs from the top to the current row. Type the formula once and the table fills the whole column; add an invoice at the bottom and its running total appears automatically. The eight October invoices run up to 11,980.19, equal to =SUM(Sales[Amount]).

Step 5: Running total by category or by month

To restart the running total for each group, use SUMIFS with expanding ranges for both the values and the criteria:

=SUMIFS($C$4:C4,$B$4:B4,B4)

Each row adds the amounts above it that have the same category. Supplies runs to 817.05 by Oct 14 and ends at 882.50. Swap the criteria column for a month column (first day of each month, =DATE(YEAR(A4),MONTH(A4),1)) and the total restarts each month: Oct 2 starts again at 171.25. For month totals rather than running totals, see how to sum monthly values with SUMIFS.

The summary below the list checks that the last running total for each category equals the category total from a normal SUMIFS. Because the amounts are positive, the last running total is also the largest, so =SUMPRODUCT(MAX(($B$4:$B$13=A16)*$E$4:$E$13)) finds it in any Excel version. Sort the data by date first: the formulas add rows in the order they appear.

Step 6: Year-to-date sales against a target

A YTD column is a running total of months. Leave future months blank:

=IF(B4="","",SUM($B$4:B4))

The YTD target is the annual target spread evenly, =$B$2/12*ROWS($B$4:B4), and YTD vs target is YTD sales divided by the YTD target. Through October, sales of 122,630.00 against a YTD target of 125,000.00 is 98.1%, with 27,370.00 still to sell to reach the 150,000.00 annual target. A running average, =AVERAGE($B$4:B4), uses the same expanding range.

Use the YTD numbers in a 12-month cash flow forecast, or chart the YTD and target columns as two lines to show whether the year is on track.

Running total in a PivotTable (no formulas)

1. Build the PivotTable with Date (grouped by month) in Rows and Amount in Values.
2. Right-click a value and choose Show Values As > Running Total In.
3. Pick the base field (Date, or Months if you grouped the dates) and click OK.
4. Add Amount to Values a second time if you want the monthly amount and the running total side by side.

Choose % Running Total In instead to show how much of the year’s total has been reached by each month. A PivotTable running total updates when you refresh, so it suits large lists; the formula versions above suit balances you keep row by row.

Frequently asked questions

Final checklist

• The data is sorted by date before the running total.
• The start of each expanding range is locked with $ and the end is relative.
• Empty rows return blank instead of repeating the total.
• The last running balance equals opening + in – out.
• Group running totals tie to the group totals.
• Totals in tables use structured references; quick totals can use AutoSum (Alt + =).