You're building a balance tracker that takes a list of transactions, calculates a running balance, and shows you only the last twelve months of activity. The end state: you paste a new transaction at the bottom of the source table, and the rolling-12-month view updates itself. No formulas to drag, no references to fix, no "remember to extend the range."
You'll need Excel for Microsoft 365 (SCAN and FILTER are required). A starting opening balance and a list of dated transactions, real or fake.
Why this approach
The default move is a helper column for running balance, a helper column for "is this in the last 12 months," and a manual range that gets extended every quarter. That falls apart the moment someone forgets to extend. It also gets ugly fast when the transaction count grows past a few hundred.
The approach below uses two structured tables and two dynamic-array formulas. Once it's built, the only thing that ever changes is the contents of the source transaction table. Everything downstream recalculates.
Step 1: Set up the source table
In a fresh sheet, create a table with three columns:
| Date | Description | Amount |
|------|-------------|--------|
| 2025-08-15 | Opening balance | 1250 |
| 2025-08-22 | Salary | 4800 |
| 2025-08-28 | Rent | -2100 |Select the range, Ctrl+T, confirm "My table has headers." In the Table Design tab, rename the table to Transactions. This is the only table you'll ever edit by hand. Everything else flows from it.
Step 2: Add the running balance
In an empty cell next to (or on a separate sheet from) the Transactions table, say F2, enter:
=SCAN(0, Transactions[Amount], LAMBDA(acc, val, acc + val))This spills the running balance down from F2, one row per transaction, in the same order as the table. Because it points at the table column, not a fixed range, it extends automatically when new transaction rows are added.
The starting value of 0 is the accumulator's initial state - the first transaction's "Amount" already contains the opening balance. If you'd rather treat the opening balance as a separate input, change the 0 to a cell reference and remove the opening-balance row from the table. Either pattern works.
Step 3: Stitch the running balance back into a usable view
The spilled SCAN result is correct but it's not in the same table - it's a floating array of numbers. To get something you can actually query, build a second array next to it that combines the source dates with the running balance.
In another cell, say H2:
=HSTACK(Transactions[Date], SCAN(0, Transactions[Amount], LAMBDA(acc, val, acc + val)))This produces a two-column spilled array: dates from the source table and the running balance at each date. Add headers manually in H1 and I1 ("Date", "Balance"). You now have a date-aligned running balance that grows with the source table.
Step 4: Filter to the last 12 months
In a third cell, say K2:
=LET(
data, HSTACK(Transactions[Date], SCAN(0, Transactions[Amount], LAMBDA(acc, val, acc + val))),
cutoff, EDATE(TODAY(), -12),
FILTER(data, INDEX(data, 0, 1) >= cutoff, "No transactions in the last 12 months")
)What's happening:
databuilds the two-column array of dates and running balance, same as Step 3.cutoffcomputes the date twelve months ago using EDATE - more reliable thanTODAY() - 365because it handles month-length and leap-year edge cases correctly.FILTERkeeps only the rows where the date column is on or after the cutoff.INDEX(data, 0, 1)extracts the first column of the array to use as the filter criterion.- The third argument to FILTER is the fallback if no rows match, so the cell shows a readable message instead of a
#CALC!error in a brand-new workbook.
The result spills as a two-column block: dates and balances for the last twelve months. Headers go in K1 and L1.
Step 5: Test the self-update
Go back to the Transactions table. Add a new row at the bottom with today's date and an amount. The running balance in step 2 extends by one row. The HSTACK in step 3 extends. The FILTER in step 4 picks up the new row automatically. If a transaction at the top of the source range is now more than 12 months old, FILTER drops it from the rolling view on the next recalc.
If all three behave as expected, the tracker is self-maintaining.
Common mistakes
Pointing SCAN at a fixed range instead of a table column.
SCAN(0, B2:B100, ...) works, but the moment the data grows past row 100 the running balance stops updating. Use the table column reference. The whole point of this build is that the formulas extend themselves.
Forgetting that SCAN's output isn't a table column.
The spilled balance sits next to the Transactions table, but it's not part of it. If you sort the Transactions table, the spilled SCAN result doesn't reshuffle to match - it always reflects the table's current row order, but the values in the spill are repositioned, not the rows themselves. Don't try to read across from a sorted source row to the spilled SCAN cells; they're independent ranges.
Using TODAY() - 365 instead of EDATE.
Close enough most of the time, off by a day on leap years, and wrong any time the user actually cares about "the last calendar 12 months" rather than "the last 365 days." EDATE handles the month-arithmetic correctly. Use it.
Building the FILTER without the fallback string.
If the Transactions table is empty - genuinely empty, like in a freshly-set-up workbook - FILTER returns #CALC! and the spill collapses. The "No transactions in the last 12 months" argument is one of those quiet quality-of-life details that costs nothing and prevents a confusing error.
Where to go from here
The same SCAN pattern works for cumulative totals, running maxima, and any other order-dependent calculation - see Magic Monday: SCAN (May 11) for the full mental model and Hot Take (May 13) for why this category of formula is worth the apparent complexity. The Power Pivot/DAX equivalent for when the running balance needs to live inside the data model (where the worksheet pattern won't scale) is covered in Between the Sheets: Part 4 (May 7).
Frequently Asked Questions
Can I build a rolling 12-month balance tracker in Excel without macros or Power Query?
Yes. With SCAN, FILTER, HSTACK, and EDATE - all available in Excel for Microsoft 365 - you can build a self-updating rolling balance using two dynamic-array formulas. No macros, no Power Query, no helper columns. New rows in the source table feed through automatically.
Why use a structured table instead of a regular range?
Structured tables expand automatically when rows are added, and table column references (Transactions[Amount]) extend with the table. Fixed ranges (B2:B100) don't - they stop including new rows the moment the data grows past the upper bound. The whole self-maintaining property depends on the table reference.
Why use EDATE(TODAY(), -12) instead of TODAY() - 365?
EDATE returns the date exactly twelve calendar months before today, accounting for varying month lengths and leap years. Subtracting 365 gives you 365 days ago, which is off by one day in leap years and doesn't honour month boundaries. For a rolling month-based window, EDATE is correct; for a rolling 365-day window, subtraction is correct. Most "rolling 12 months" requirements mean the former.
What happens if I sort or filter the source table?
Sorting the source table re-orders the rows it contains. The SCAN that points at Transactions[Amount] always reflects the current order, so the running balance recalculates against the new sequence. This is usually unwanted - running balances are normally date-ordered. The safest pattern is to keep the source table sorted by date ascending and avoid manual reorders.
Can I extend this to multiple accounts?
Yes. Add an Account column to the source table, then wrap the SCAN in a per-account pattern using BYROW or a SUMIFS-style approach against the running balance. The pattern gets more involved - if you're maintaining more than two or three accounts, it's usually cleaner to move the calculation into Power Pivot, where a measure with USERELATIONSHIP and SUMX can do the per-account running balance natively.
