Excel Formulas for Carrying an Unpaid Balance Forward
By Hupp Goods ·
A plain monthly grid resets every time the calendar page turns. If a bill didn't get fully paid in August, a fresh September tab has no memory of that. People run into this a lot: forum threads on tracking overdue invoices and outstanding balances show up regularly on Microsoft's own support forums and general Excel help boards, independent of any specific product. The fix is two formulas working together: a running balance, and a separate total for what's still unpaid.
The running balance formula
Microsoft's own support documentation gives the simplest version of this. With deposits in
column A, withdrawals in column B, and a running balance in column C, the first row is
just =SUM(A2-B2). Every row after that references the row above it:
Worked example (illustrative numbers, not a real person's data)
Row 2: Deposit $1,000, Withdrawal $625, Balance =SUM(A2-B2) = $375.
Row 3: Deposit $1,245, Withdrawal $740, Balance =SUM(C2,A3-B3) = $880.
Each new row's balance formula points at the balance directly above it, so the total
carries forward automatically instead of resetting. Copy that formula down and it keeps
working for every new row you add.
This works well for a simple deposits-and-withdrawals ledger. It gets more useful once you add a way to separate what's still unpaid from what's already settled, which is the part most people ask about next.
Totaling only what's still unpaid
A recurring question on Excel help forums (MrExcel, Microsoft Q&A) is how to sum only the bills marked unpaid, ignoring anything already settled. If your bill list has an amount in column D and a paid/unpaid marker (say, "YES" or "NO") in column I, a straightforward SUMPRODUCT formula handles it:
Worked example (illustrative numbers, not a real person's data)
=SUMPRODUCT((D2:D100)*(I2:I100="NO"))
This multiplies every amount in D2:D100 by 1 if the matching row in I2:I100 says "NO"
(unpaid) or by 0 if it doesn't, then adds up the result. The total only includes rows
still marked unpaid, which is exactly what a "currently outstanding" figure needs to
show, regardless of which month those bills originally came from.
Flagging overdue amounts by due date
Once unpaid bills are totaled, the next common request is separating "overdue" from "not due yet." A simple version compares today's date to the due date and only counts an amount if it's both unpaid and past due:
Worked example (illustrative numbers, not a real person's data)
If column B holds the due date and column I holds "YES" for paid, one working pattern
from a general Excel formula board is:
=SUMPRODUCT((D2:D100)*(I2:I100="NO")*(B2:B100<TODAY()))
This adds an amount only when it's unpaid AND its due date has already passed. Pair it
with conditional formatting (a formula like =AND($B2<TODAY(),$I2="NO")
applied to the row) to highlight overdue rows visually as well as total them.
Where this gets fragile by hand
These formulas work, and building them yourself is a completely reasonable way to learn how Excel handles running totals and conditional sums. The honest tradeoff is maintenance: every new bill or month means extending ranges, checking that paid/unpaid markers stay consistent, and making sure protected cells don't get typed over by accident. That upkeep is exactly what a prebuilt dashboard automates. The Paycheck Bill Tracker uses this same running-balance and unpaid-total logic behind the scenes, already wired to keep unpaid bills visible across months, with protected formula cells and yellow input cells so typing in the wrong spot doesn't break anything. It's still a manual Excel workbook (you enter every bill and payment yourself, there's no bank sync or automatic import), just with the formula-building step already done.
Sources
Formulas and reader questions referenced above came from: Microsoft Support, "Calculate a running balance", MrExcel Message Board, "Formula to Sum Outstanding Amount Due", and Microsoft Q&A, "Excel conditional formatting to track overdue invoices", accessed September 2026.