Python Excel AutomationPractical guides for developers
Start learning
Guide
Advanced Data Transformation And CleaningDeep dive

Running Totals and Year-Over-Year Growth in pandas

cumsum for running totals, shift and pct_change for period comparisons, rolling for moving averages — and why a year-over-year join beats shift when months go missing.

Running totals and period comparisons are where a spreadsheet's relative references shine and then betray it: the formula is easy to write and silently wrong the moment a row is inserted, a month is missing or the sheet is sorted differently. pandas expresses the same ideas as operations on an ordered column, which makes the ordering assumption explicit. This guide is part of Excel Formula Equivalents in pandas.

Relative-reference formulas and their column operations A running total becomes cumsum, a reference to the row above becomes shift, period growth becomes pct_change, and a dragged average over a relative range becomes rolling with an explicit window. Excel pandas The assumption SUM($C$2:C2) cumsum() row order is the date order =C3-C2 shift(1) no gaps in the series =(C3-C2)/C2 pct_change() prior value is not zero AVERAGE(C1:C3) rolling(3).mean() window fullness at the start YTD with an IF groupby(year).cumsum() the key defines the reset each row carries an assumption the formula never states

Prerequisites

Bash
pip install pandas openpyxl
Python
import pandas as pd

monthly = pd.DataFrame({
    "Month": pd.period_range("2025-01", periods=15, freq="M").astype(str),
    "Region": ["North"] * 15,
    "Revenue": [
        31200.0, 28400.0, 34100.0, 29800.0, 36500.0, 41200.0,
        38900.0, 33400.0, 45100.0, 47800.0, 51200.0, 58900.0,
        35600.0, 32900.0, 39400.0,
    ],
})
monthly["Month"] = pd.PeriodIndex(monthly["Month"], freq="M")

Running totals: cumsum

Python
# =SUM($C$2:C2) dragged down
monthly = monthly.sort_values("Month")
monthly["Running_Total"] = monthly["Revenue"].cumsum()

# Restarting each calendar year — a formula would need an IF on the month
monthly["YTD"] = monthly.groupby(monthly["Month"].dt.year)["Revenue"].cumsum()
print(monthly[["Month", "Revenue", "Running_Total", "YTD"]].head(15))

The sort_values call is not decoration. cumsum accumulates in the frame's current row order, so an unsorted frame produces a running total that is arithmetically correct and meaningless. Sorting explicitly before any cumulative operation is a habit worth forming.

The year-to-date column is the one that shows the difference in expressiveness. In a sheet it needs either a formula that tests whether the year changed or a separate block per year; here it is a grouping key.

cummax and cummin follow the same pattern and answer questions a report often wants: the best month so far, and whether this month set a record.

Python
monthly["Best_So_Far"] = monthly["Revenue"].cummax()
monthly["New_Record"] = monthly["Revenue"] >= monthly["Best_So_Far"]

Period-over-period change: shift

What shift actually does Shifting a column by one position places each row's previous value beside it, after which the change and the growth rate are ordinary column arithmetic. period comparison Revenue the ordered series shift(1) previous value, aligned difference or ratio change and growth the first row is NaN because there is no previous value

A formula referencing the cell above becomes shift(1), which moves the whole column down by one position.

Python
monthly["Prev_Month"] = monthly["Revenue"].shift(1)
monthly["MoM_Change"] = monthly["Revenue"] - monthly["Prev_Month"]
monthly["MoM_Pct"] = monthly["Revenue"].pct_change()

print(monthly[["Month", "Revenue", "MoM_Change", "MoM_Pct"]].head(6))

pct_change() is the shorthand for the change expressed as a proportion, and it handles the first row the way it should: NaN, because there is no previous value. A spreadsheet usually hides that with a blanked first cell, which means the same information is present and undocumented.

Year-over-year on monthly data is shift(12):

Python
monthly["YoY_Pct"] = monthly["Revenue"].pct_change(periods=12)
print(monthly[["Month", "Revenue", "YoY_Pct"]].tail(3))

That works only if the series is complete — twelve rows really is twelve months. When months can be missing, join the frame to itself on a shifted key instead, which is correct regardless of gaps:

Python
prior = monthly[["Month", "Revenue"]].copy()
prior["Month"] = prior["Month"] + 12
prior = prior.rename(columns={"Revenue": "Revenue_LY"})

compared = monthly.merge(prior, on="Month", how="left")
compared["YoY_Pct"] = compared["Revenue"] / compared["Revenue_LY"] - 1

Rolling windows

Python
monthly["Rolling_3"] = monthly["Revenue"].rolling(3).mean()
monthly["Rolling_3_Min1"] = monthly["Revenue"].rolling(3, min_periods=1).mean()
monthly["Rolling_12_Sum"] = monthly["Revenue"].rolling(12).sum()
print(monthly[["Month", "Revenue", "Rolling_3", "Rolling_3_Min1"]].head(5))

min_periods decides what happens at the start of the series: the default leaves the first two rows NaN because a three-month window is not yet full, while min_periods=1 averages whatever exists. The first is more honest in a chart; the second is friendlier in a table. Excel's dragged AVERAGE over a relative range silently does the second by shrinking the range at the top of the column, which is worth knowing when the numbers do not match.

Accumulating within groups

Everything above extends to several series in one frame, which is where the pandas version pulls clearly ahead of a sheet per region.

Python
multi = pd.concat([
    monthly.assign(Region="North"),
    monthly.assign(Region="South", Revenue=monthly["Revenue"] * 0.7),
], ignore_index=True).sort_values(["Region", "Month"])

multi["Running_Total"] = multi.groupby("Region")["Revenue"].cumsum()
multi["MoM_Pct"] = multi.groupby("Region")["Revenue"].pct_change()
multi["Rolling_3"] = multi.groupby("Region")["Revenue"].transform(
    lambda s: s.rolling(3, min_periods=1).mean()
)
print(multi.groupby("Region").tail(2))

Every one of those would be a separate block of formulas per region in a workbook, kept in step by hand. Here the group key does the work, and adding a third region changes nothing.

Filling the gaps before you accumulate

Every calculation in this guide assumes a row exists for each period. Real data rarely obliges: a region with no sales in August has no August row, and a running total or a shift then quietly treats September as the month after July. Reindexing over a complete period range fixes it before anything downstream can go wrong.

Python
import pandas as pd

full_range = pd.period_range(monthly["Month"].min(), monthly["Month"].max(), freq="M")

complete = (
    monthly.set_index("Month")
           .reindex(full_range)
           .rename_axis("Month")
           .reset_index()
)
complete["Revenue"] = complete["Revenue"].fillna(0.0)
complete["Region"] = complete["Region"].ffill()
print(complete.tail(4))

The two fill choices are deliberate and different. Revenue becomes zero because no sales genuinely means zero; the region is forward-filled because it is a label rather than a measurement. Getting that distinction wrong — filling a measure forward, or zeroing a label — produces a report that looks complete and is fabricated, which is worse than one with a visible gap.

For grouped data the same reindexing runs per group, and building a full cross-product of periods and groups first is the reliable way to do it:

Python
skeleton = pd.MultiIndex.from_product(
    [multi["Region"].unique(), full_range], names=["Region", "Month"]
)
dense = multi.set_index(["Region", "Month"]).reindex(skeleton).reset_index()

Fill Missing Values in Excel with pandas fillna covers the choice of fill strategy in more depth; the point here is that it has to happen before the accumulation, not after.

Presenting the result

A growth column is only useful if the reader can tell a good number from a bad one at a glance, and that is a formatting decision rather than a calculation. Writing the raw proportion and applying a percentage number format keeps the value sortable and chartable while showing it the way a reader expects.

Python
import pandas as pd

with pd.ExcelWriter("growth.xlsx", engine="xlsxwriter") as writer:
    monthly.to_excel(writer, sheet_name="Monthly", index=False)
    book, sheet = writer.book, writer.sheets["Monthly"]
    percent = book.add_format({"num_format": "0.0%"})
    money = book.add_format({"num_format": "#,##0"})
    sheet.set_column("C:C", 14, money)
    sheet.set_column("F:F", 12, percent)
    sheet.conditional_format("F2:F200", {"type": "3_color_scale"})
    sheet.freeze_panes(1, 0)

A three-colour scale over the growth column turns a table of numbers into something readable in a second, and it costs one line. The wider set of options is in Apply Conditional Formatting with XlsxWriter.

Common pitfalls

SymptomCauseFix
Running total looks randomThe frame was not sorted by datesort_values before any cumulative operation
A group's total continues from the previous groupcumsum applied to the whole columngroupby(key)[col].cumsum()
Year-over-year is wrong after a missing monthshift(12) assumes a complete seriesMerge on a shifted period key instead
The first rows of a rolling mean are NaNThe window is not yet fullPass min_periods deliberately
Percentages are 100 times too smallpct_change returns a proportion, not a percentageMultiply by 100, or format as a percentage in the sheet
Growth is infiniteThe prior period was zeroGuard the division, or report the absolute change instead

Performance and scale

Shift assumes a complete series; a join does not Shifting by twelve rows gives the wrong comparison when a month is missing from the data, while joining the frame to itself on a year-shifted period key stays correct regardless of gaps. shift(12) counts rows, not months wrong after a gap no warning merge on a shifted key matches by period gap becomes NaN correct either way year on year when months can be missing, join rather than shift

Cumulative and rolling operations are single passes over the column, so they are fast and stay fast. The cost that does appear is in the grouped transform with a lambda, which runs the function once per group — fine for a dozen regions, noticeable for a hundred thousand customer identifiers.

Where a rolling calculation must run per group over many groups, sorting once and using the built-in grouped rolling is materially quicker than a lambda:

Python
# Faster than transform with a lambda when there are many groups
rolled = (
    multi.sort_values(["Region", "Month"])
         .groupby("Region")["Revenue"]
         .rolling(3, min_periods=1).mean()
         .reset_index(level=0, drop=True)
)
multi["Rolling_3"] = rolled

The reset_index step is what aligns the result back to the original frame — grouped rolling returns a multi-level index, and dropping the group level restores the row alignment.

Conclusion

cumsum replaces a running-total formula and groupby().cumsum() restarts it per year or region. shift replaces any reference to the row above, pct_change gives period-over-period growth, and rolling gives moving averages with explicit control over the start of the series. The one rule that matters throughout: sort the frame first, because every one of these operations follows row order and none of them will warn you when that order is wrong.

Frequently asked questions

Why does my running total restart in the wrong place? Because cumsum follows the frame's current row order, not a date column. Sort by the date before accumulating, and if the total should restart per group use groupby(key)col.cumsum().

What is the equivalent of a formula referencing the row above? shift(1), which moves a column down by one row. A month-over-month change is col - col.shift(1), and the first row is NaN because there is no previous value — which is the honest answer Excel hides behind a manually blanked cell.

How do I compare against the same month last year? Set a period index and use shift(12) on monthly data, or merge the frame against itself on a year-shifted key. The second is safer when months may be missing, because shift assumes a complete series.

Can I do a rolling three-month average? rolling(3).mean(), optionally with min_periods to control what happens at the start of the series. Excel needs an AVERAGE over a relative range dragged down the column, which breaks whenever a row is inserted.