The simplest running total formula in Excel is =SUM($B$2:B2) entered in cell C2 and copied down, where column B holds the numbers you want to accumulate. This locks the first cell reference with a dollar sign while letting the second one move, so each row sums everything from the top down to that row.
What a Running Total Is and Why You Need It
A running total, also called a cumulative sum, adds each new value to the sum of all previous values. Instead of showing just one month’s sales, you see the year-to-date figure after each entry. This is useful for budgets, inventory, expense tracking, and any time you want to see progress toward a goal. The formula recalculates automatically when you change or add data, so your totals stay current without manual updates.
The Core Formula: Absolute and Relative References Explained
The trick behind a running total is mixing absolute and relative references. In =SUM($B$2:B2), the first $B$2 is absolute (the dollar signs lock the row and column), and the second B2 is relative (it changes as you copy the formula down). When you drag the fill handle from C2 to C3, the formula becomes =SUM($B$2:B3), then =SUM($B$2:B4), and so on. Each cell sums from the fixed starting point to the current row.
For example, with numbers in B2:B5 as 10, 20, 30, and 40, the formulas in C2:C5 return 10, 30, 60, and 100. The last value equals the sum of all entries, which is a good way to check your work.
How to Set Up a Running Total in Excel (Step by Step)
- Assume your data is in column B, starting at B2 (with a header in B1). Leave column C empty for the running total.
- Click on cell C2, type
=SUM($B$2:B2), and press Enter. - Select C2 again. Move your cursor to the bottom-right corner until it becomes a thin black cross (the fill handle).
- Double-click the fill handle to fill down to the last row of your data, or drag it manually.
- If your data has blank rows, the fill handle stops at the first blank. In that case, drag the handle to the desired last row instead.
On a Mac, the steps are identical; the fill handle works the same way. In Excel for the web, you can also use the fill handle, though double-clicking may behave slightly differently if there are gaps.
Alternative Methods: SUM, Structured References, and PivotTables
Besides the classic SUM formula, you have a few other options:
- Using the SUM function with a defined name: If you name the range B2:B100 as “Sales,” you can use
=SUM(Sales:INDEX(Sales,ROW()-1))but this is more complex and rarely needed. - Using a table (structured references): If your data is in an Excel Table (Insert > Table), you can enter
=SUM(Table1[Sales])in the first data row of a new column, but this gives the total, not a running total. For a running total in a table, use=SUM($B$2:B2)as before; the table will auto-fill the formula down. - Using a PivotTable: Add the field to the Values area, then right-click and choose Show Values As > Running Total In. This is great for large datasets but less flexible for quick calculations.
For most users, the simple =SUM($B$2:B2) is the fastest and easiest to understand.
Common Problems and How to Fix Them
Several issues can trip you up when creating running totals:
- Blank cells in the data column: If B3 is blank, the SUM formula still works, but the running total stays the same for that row. That’s usually fine, but if you want to skip blanks, use
=IF(B3="","",SUM($B$2:B3))to leave the cell empty. - Text or errors in the data: If a cell contains text, SUM ignores it, but an error like #N/A will propagate. Use
=IFERROR(SUM($B$2:B2),"")to hide errors. - Accidentally moving the starting cell: If you insert a row above the data, the absolute reference may shift. To keep the start fixed, consider using
=SUM($B$2:B2)and then adjust if you insert rows. - Copying the formula to a different row: If you copy C2 to C10, the formula becomes
=SUM($B$2:B10), which is correct. But if you copy it to a cell in a different column, the relative reference changes to that column; that’s usually not what you want.
Running Total with Conditions: SUMIF and FILTER
Sometimes you want a running total only for certain categories, like sales for a specific product. Use SUMIF with a growing range. For example, with products in A2:A10 and amounts in B2:B10, and you want a running total for “Widget” in column C, enter in C2: =SUMIF($A$2:A2,"Widget",$B$2:B2) and copy down. This sums only the Widget rows from the start to the current row.
If you have Excel 365, you can also use the SCAN function (new in 2022) to create a running total in one dynamic array formula. For example, =SCAN(0,B2:B10,LAMBDA(a,b,a+b)) returns an array of running totals. This is more advanced but avoids dragging formulas.
Frequently Asked Questions
How do I create a running total in a pivot table?
Right-click any value in the pivot table, choose Show Values As, then select Running Total In. Pick the base field (usually the row label) and click OK. This adds a cumulative column without writing formulas.
Why is my running total not updating when I add new rows?
If you type a new value in B11 but your formula only goes down to C10, the running total won’t include it. Drag the fill handle down to C11, or convert your data to an Excel Table so formulas auto-fill.
Can I use a running total with dates to get a month-to-date sum?
Yes. Use =SUMIFS($B$2:B2,$A$2:A2,">="&DATE(YEAR(A2),MONTH(A2),1)) where A contains dates and B contains amounts. This sums from the first of the month to the current row.
What is the difference between a running total and a cumulative average?
A running total adds values; a cumulative average divides that running total by the count of items so far. For a cumulative average, use =AVERAGE($B$2:B2) which also expands as you copy down.
Does the running total formula work in Google Sheets?
Yes, the same =SUM($B$2:B2) works in Google Sheets. The fill handle and absolute references behave the same way.
How do I reset the running total when a new category starts?
Use an IF formula: =IF(A2=A1,SUM($B$2:B2),B2) if your data is sorted by category. This restarts the cumulative sum each time the category changes.
Related
- Excel Sales Tracking Template Weekly: How to Build One
- Excel Sales Tracking Template with Commission: How to Build One
- Excel Sales Tracking Template by Region
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Paste Special: Copy Values, Formulas, and MoreTo use Paste Special in Excel for Microsoft 365 on Windows, copy the source cells, select the destination,…
- Excel Simple Timesheet Template: Setup and FormulasTo make a simple timesheet in Excel, enter Date, Start, End, Break (minutes), and Hours in columns A:E,…
- How to Use CONCAT in Excel to Join TextTo join text in Excel, enter a formula such as =CONCAT(A2," ",B2) in an empty cell, replacing A2…
- VLOOKUP vs XLOOKUP: Which to UseVLOOKUP and XLOOKUP are both lookup functions in Excel, but XLOOKUP is the modern replacement that solves most…
- VLOOKUP and HLOOKUP in Excel: Differences and ExamplesVLOOKUP searches down the first column of a table and returns a value from a column to its…
- Break-Even Excel Formula: Calculate Units and SalesThe basic break-even formula in Excel is fixed costs divided by the contribution margin per unit: =B2/(B3-B4). If…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.