How to Calculate a Running Total in Excel, and Year to Date Against Last Year

Calculate a running total in Excel that survives tables and sorting, restart it each year with SUMIFS, compare YTD with last year and use SCAN or a pivot.

Founder, Glow Reports. , 4 min read
  • Excel
  • Formulas
  • Pivot Tables
A staircase of stacked coin columns growing step by step beside a calendar, with a new low stack starting after the last step
On this page
  1. How do you calculate a running total in Excel?
  2. Why does my running total break when I add or sort rows?
  3. How do you make a running total restart each year?
  4. How do you compare year to date with last year?
  5. How do you show a running total in a pivot table?
  6. How do you write a running total with SCAN?
  7. Where does Glow Reports fit?
  8. Common questions

A running total adds each new value to everything above it, so the last row always shows the total so far. In Excel, the classic formula is copied down the column. It works on a plain range, then goes wrong when the data turns into a table, gets sorted or gets new rows. Below: that formula and why it breaks, the fixes that hold, a total that restarts each January, year to date (YTD) against last year, the pivot table option and SCAN in Microsoft 365 and Google Sheets.

How do you calculate a running total in Excel?

Anchor the start of the range and leave the end free. In C2, type and copy it down. The dollar signs lock B2 as the first cell, while the second B2 moves one row with each copy, so each row adds everything from the top to itself.

The examples use monthly revenue from January 2025 to March 2026: the first day of each month in column A, revenue in column B, the running total in column C and the year to date in column D.

MonthRevenueRunning totalYear to date
Jan 202512,40012,40012,400
Feb 202511,80024,20024,200
Mar 202513,10037,30037,300
Apr 202512,90050,20050,200
May 202514,20064,40064,400
Jun 202513,60078,00078,000
Jul 202512,10090,10090,100
Aug 202511,500101,600101,600
Sep 202514,800116,400116,400
Oct 202515,300131,700131,700
Nov 202516,100147,800147,800
Dec 202518,400166,200166,200
Jan 202614,100180,30014,100
Feb 202613,200193,50027,300
Mar 202615,600209,10042,900
Running total in C16, 209,100
=SUM($B$2:B16)

SUM "adds values", and a range inside SUM updates when you add or delete rows within it Microsoft Support. The year to date column restarts in January. It is covered further down.

Why does my running total break when I add or sort rows?

The formula only knows positions, not dates. Insert a row in the middle and the new row has no formula until you copy one in. Sort by revenue and the column becomes a running total of the sorted order, which no longer means anything over time. Inside an Excel table, adding a row can rewrite the last formula.

The table case surprises people most. In a Microsoft Tech Community thread, a user saw the formula in the last row of a table change its reference to the new last row each time the table grew. The answer from a regular contributor: Excel "remembers what $B3 is in the last row of the table, and when you expand the table it automatically substitutes the reference", and that is by design Microsoft Tech Community. The same thread gives the fix: "with tables it's better to use structured references".

In a table with a Revenue column, anchor the start with INDEX instead of a dollar sign:

Running total in a table
=SUM(INDEX([Revenue],1):[@Revenue])

always returns the first cell of the column, whatever rows you add, and is the cell on the current row. The formula fills down by itself as the table grows.

To survive sorting too, compute the total from the dates instead of the positions. This version adds every month up to the date on the row, wherever that row sits:

Running total by date, survives sorting
=SUMIFS([Revenue],[Month],"<="&[@Month])

With daily data, two rows on the same date get the same running total, since both include all of that day. For a total by month, that is often what you want anyway.

How do you make a running total restart each year?

Add a second condition so SUMIFS only counts dates from 1 January of the row's year. SUMIFS "adds all of its arguments that meet multiple criteria" Microsoft Support, so one criterion sets the start of the year and the other stops at the current row.

Year to date in D14, 14,100
=SUMIFS($B$2:$B$16,$A$2:$A$16,">="&DATE(YEAR(A14),1,1),$A$2:$A$16,"<="&A14)

In row 14, January 2026, the total starts again at 14,100, and March 2026 reaches 42,900. The formula reads the dates, so it gives the same answer after a sort. In a table, the same logic reads:

Year to date in a table
=SUMIFS([Revenue],[Month],">="&DATE(YEAR([@Month]),1,1),[Month],"<="&[@Month])

For a fiscal year that starts in April, replace with the last 1 April on or before the row's date: . For more on combining conditions, see how to use SUMIFS with multiple criteria.

How do you compare year to date with last year?

Sum this year from 1 January to the report date, then the same window one year earlier. With the report date in F1, gives the same day last year, so both totals cover the same number of months.

YTD 2026 through March, 42,900
=SUMIFS($B$2:$B$16,$A$2:$A$16,">="&DATE(YEAR(F1),1,1),$A$2:$A$16,"<="&F1)
YTD 2025 through March, 37,300
=SUMIFS($B$2:$B$16,$A$2:$A$16,">="&DATE(YEAR(F1)-1,1,1),$A$2:$A$16,"<="&EDATE(F1,-12))

With 31 March 2026 in F1, this year's YTD is 42,900 and last year's is 37,300.

+15.0% is the change from 37,300 to 42,900, January to March 2026 against the same three months of 2025.

The trap is comparing this year's partial YTD with last year's full year, 166,200, which makes every spring look like a collapse. Always cut last year at the same date. To turn the two totals into a growth rate, see how to calculate percentage change in Excel.

How do you show a running total in a pivot table?

Change how the value field is shown, not the formula. Right-click a number in the Values area and choose Show Values AsRunning Total In, then pick the field that runs, usually the month. Microsoft describes the option as showing "the value for successive items in the Base field as a running total" Microsoft Support.

  1. Build the pivot table with the date in Rows and revenue in Values. Group the dates by Months and Years, as shown in how to create a pivot table by month.
  2. Drag revenue into Values a second time, so you keep the monthly figure next to the running one.
  3. Right-click a number of the second field and choose Show Values AsRunning Total In.
  4. In the Base field box, pick Months.

Put Years in Columns and Months in Rows, and the running total restarts in each year column, which gives YTD side by side for both years. shows the same thing as a share of the total. The pivot table adds in its own month order, so sorting the source data does not affect it.

How do you write a running total with SCAN?

In Excel for Microsoft 365 or Excel 2024, returns the whole running total column from a single cell. SCAN applies a LAMBDA to each value and "returns an array that has each intermediate value" Microsoft Support.

Running total column with SCAN, ends at 209,100
=SCAN(0,B2:B16,LAMBDA(total,value,total+value))

The first argument is the starting value, 0. The LAMBDA takes the total so far and the current value, and returns the new total. The result spills down 15 rows, so keep the cells below it empty. Microsoft's page lists SCAN for Microsoft 365 and Excel 2024 only, so use the SUM or SUMIFS formulas in older versions.

Google Sheets has the same function with the same arguments. Its help page shows a running total example, , which starts from 5 Google Docs Editors Help. The SUMIFS formulas above work in Sheets as well Google Docs Editors Help.

Where does Glow Reports fit?

The formulas above are fine for one sheet. A monthly report repeats them every month on a fresh export: a running total by month, YTD against last year, the growth rate, then a chart. Each time, an anchor can slip or last year can end on the wrong date.

Glow Reports starts from that export. You upload the CSV or Excel file and ask, in plain language, for revenue year to date by month against last year. The AI writes the SQL, builds the chart and drafts the text. The SQL sits under each figure, so you can check that both years stop at the same date and edit the query if they don't. Reports export to PDF and stay in the workspace. The free plan gives 300 credits once, and a report costs 100 credits. For the rest of a monthly report, see how to build a monthly sales report in Excel.

Common questions

What is the difference between a running total and year to date?

A running total keeps adding from the first row of the data, across years. Year to date is a running total that restarts on 1 January, or on the first day of the fiscal year. In the example, March 2026 shows 209,100 as a running total and 42,900 as YTD.

Why does my running total show the same number on several rows?

The SUMIFS version by date adds every row up to and including the row's date. When several rows share a date, they all include each other. Use on a range sorted by date, or SCAN, when you need one step per row.

How do I calculate a running total in Google Sheets?

Use copied down, as in Excel, or one SCAN formula: . Both return the same column.

Can I show a running total as a percentage of the total?

Yes. Divide the running total by the grand total, , and format it as a percentage. In a pivot table, choose from .

El Mehdi Benane
Founder, Glow Reports

I am a product engineer and the founder of Glow Reports. I spent more than 6 years building products in tech, data and cybersecurity, for large groups, startups and SMEs. In most organizations I saw data treated as a technical topic, when it drives business decisions. I built the reporting tool I was looking for, so the people who run small and mid-sized organizations can read their own numbers without a data team.

Upload your sales export to Glow Reports and get a year to date comparison by month, with the SQL behind each figure.

Keep reading