Excel Running Total Formula: Step-by-Step

Formulas & functions · Updated November 22, 2026

The simplest Excel formula for a running total in a column is =SUM($B$2:B2) entered in the first data row and copied down. This locks the starting cell with an absolute reference ($B$2) while letting the ending cell (B2) move, so each row sums everything above it.

How the Mixed Reference Works

The formula relies on a mixed reference: the dollar sign before the column letter and row number locks the start point, while the second cell reference stays relative. For example, with sales amounts in B2:B10, put =SUM($B$2:B2) in C2. When you drag the fill handle down to C10, the formula becomes =SUM($B$2:B3), then =SUM($B$2:B4), and so on. Each row’s total includes all previous rows plus the current one. The result in C10 would be the sum of B2:B10.

Setting Up the Data and Formula

Assume your data starts in row 2 (row 1 has headers). Place the running total in the column immediately to the right of your values, or any empty column. Steps:

  • Click the cell where you want the first running total (e.g., C2).
  • Type =SUM($B$2:B2) and press Enter.
  • Select C2, then double-click the fill handle (the small square at the bottom-right corner) to copy the formula down to the last row of data.
  • If you have blank rows, drag the fill handle manually instead.

Excel for the web and Mac support the same formula and fill handle method.

Alternative: Using the SUM Function with a Table

If your data is in an Excel Table (Insert > Table), you can use a structured reference that automatically adjusts as you add rows. With a table named Table1 and a column named Sales, enter this in the first cell of a new column:

=SUM(Table1[Sales] – this alone won’t work as a running total. Instead, use =SUM(Table1[[#Headers],[Sales]]:[@Sales]) which references from the header row to the current row. However, this is more complex and error-prone. The simple =SUM($B$2:B2) is easier for most users and works in any version of Excel.

Choosing Between Running Total and Cumulative Sum

A running total (also called a cumulative sum) adds each new value to the previous total. This is different from a simple total that sums the entire column at once. Use a running total when you need to track progress over time—like daily sales, expenses, or inventory levels. For a quick overall sum, use =SUM(B:B) in a single cell. For a running total, the mixed reference formula is the standard approach.

Common Problems and Fixes

Several issues can arise when creating running totals:

  • Formula not updating when new rows are added: If you use a plain range like =SUM(B2:B2) and drag down, new rows added at the bottom won’t be included. The mixed reference formula handles this because it always starts at the same cell, but if you insert a row in the middle, the absolute reference adjusts automatically.
  • Blank cells or zeros: Blank cells are treated as 0, so they don’t break the total. If you want to skip blanks, use =IF(B2="","",SUM($B$2:B2)) but this can cause issues with charting.
  • Accidental absolute reference on both cells: If you write =SUM($B$2:$B$2), it will always return just the first cell. Make sure the second reference has no dollar signs.
  • Copying formula to a different column: If you copy the formula to a column that isn’t directly to the right, the relative reference will shift. Adjust the column letters accordingly.

Performance and Large Data Sets

For thousands of rows, the SUM formula is efficient because Excel calculates it quickly. However, if you have hundreds of thousands of rows, the repeated SUM calculations can slow down recalculations. In that case, consider using a PivotTable with a running total field, or use Power Query to add an index column and a cumulative sum. For most real-world datasets, the simple formula is fine.

Frequently Asked Questions

How do I make a running total in Excel without dragging?
Double-click the fill handle after entering the formula in the first cell. If the column to the left has data in every row, Excel copies the formula down automatically. If there are gaps, you’ll need to drag manually.

Can I use a running total across multiple columns?
Yes, but you need to adjust the reference. For example, to sum across columns B and C row by row, use =SUM($B$2:B2,$C$2:C2) in D2. This adds both columns’ current rows to the running total.

Why does my running total show 0 or a wrong number?
Check that the first cell reference is absolute (has $ signs) and the second is relative. Also ensure the formula is in a row that has data in the referenced column. If you accidentally typed =SUM(B2:B2) without the absolute reference, each row will only sum that row, not the cumulative total.

Is there a way to reset the running total at certain points?
Yes. Use an IF function to detect a change in a grouping column. For example, if column A has categories, in C2 enter =IF(A2=A1,C1+B2,B2). This resets the total when the category changes.

Does the running total formula work in Google Sheets?
Yes, the same =SUM($B$2:B2) formula works identically in Google Sheets. The fill handle also works the same way.

Safety and Accuracy Notes

Always double-check that your formula references the correct column and starting row. A single wrong dollar sign can produce misleading totals. Before relying on a running total for financial or critical decisions, verify it against a simple sum of the entire column. Excel formulas are only as accurate as the data they reference, so ensure your source data is clean and free of errors.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.