Customer & Vendor Ledger in Excel

Q: How do I make a running balance in Excel?

Use `=PreviousBalance + Debit − Credit` and copy it down. The first balance cell holds the opening balance; each later row adds that row's debit and subtracts its credit.

Q: Is the template free?

Yes — free to download, no sign-up. ---

Last updated: 27 June 2026 · Ledger columns, running-balance formula and a free template.

A party ledger in Excel is a date-wise statement of account for one customer or vendor — every bill, payment and adjustment in two columns, Debit and Credit, with a running balance that shows exactly what that party owes you or you owe them at any point. The whole ledger turns on one formula: each row's balance equals the row above, plus that row's debit, minus its credit.

Key takeaways

One ledger per party — six columns: Date, Particulars, Voucher No., Debit, Credit, Balance.
Running balance is the heart of it: =Previous Balance + Debit − Credit.
For a customer (debtor), sales are Debit, receipts are Credit — a positive balance means they owe you.
For a vendor (creditor), purchases are Credit, payments are Debit — a balance means you owe them.
SUMIF across all parties' entries rolls up to your receivables and payables totals.
A clean party ledger is what you send for ledger reconciliation and to settle disputes.

Fact box. A party ledger (or "khata" / statement of account) records every transaction with one party in date order. The running balance column is what makes it a ledger rather than a list — it answers "what is the position right now?" on every single line.

What is a party ledger?

A party ledger is the running account you keep for a single customer or supplier. Every dealing with that party — invoices raised, payments made or received, debit/credit notes, discounts — goes in as a dated line, and the balance updates row by row. At the bottom you see the closing balance: the net amount outstanding between you.

It's the Excel version of the traditional khata and the same statement your accounting software prints as a "ledger account".

What columns does a party ledger need?

Column	Purpose	Example
Date	Transaction date	02-06-2026
Particulars	What it was	To Sales / By Receipt UPI
Voucher / Ref No.	Bill or receipt number	INV-0142 / RCPT-88
Debit	Amount on the debit side	84,000
Credit	Amount on the credit side	40,000
Balance	Running net	`=Above Balance + Debit − Credit`

The opening balance sits in the first Balance cell (as a brought-forward figure). Every row after that uses the running-balance formula, so the closing balance is always the last cell in the column.

What is the running balance formula?

The single formula that drives the ledger:

Balance (this row) = Balance (row above) + Debit (this row) − Credit (this row)

In Excel, if Debit is column D, Credit is column E and Balance is column F, then in F3:

=F2 + D3 − E3

Copy it down the whole ledger. The first balance cell (F2) holds the opening balance you type in. That's the entire mechanism — debits push the balance up, credits pull it down.

Fact box. Running balance in Excel = =PreviousBalance + Debit − Credit. Whether the closing figure means "they owe you" or "you owe them" depends on which side you record sales versus purchases — see the customer vs vendor rule below.

Customer ledger vs vendor ledger — what's the difference?

The columns are identical; only the sides flip, because of who owes whom:

	Customer (debtor) ledger	Vendor (creditor) ledger
Your invoice / their purchase	Debit (increases what they owe)	—
Your purchase from them	—	Credit (increases what you owe)
Money received from them	Credit	—
Money you pay them	—	Debit
Positive closing balance means	They owe you	You owe them

So a customer ledger normally carries a debit (receivable) balance, and a vendor ledger a credit (payable) balance. Keep the convention consistent across all party ledgers so totals roll up correctly.

How to build it in Excel (step by step)

Make one sheet (or one block) per party with the six columns above; put the party's name and opening balance at the top.
Enter the opening balance in the first Balance cell.
Type the running-balance formula =F2 + D3 − E3 in the next Balance cell and copy down.
Add each transaction as a dated row — fill Debit or Credit, not both.
Format as a Table (Ctrl+T) so new rows inherit the balance formula.
Show the closing balance clearly at the bottom — that's the statement figure you share with the party.
Roll up across parties with SUMIF on a combined entries sheet to get total receivables and payables.

A free customer and vendor ledger template is available to download, no sign-up — one tab per party with the running balance already wired in.

How Ankeshan helps: Ankeshan keeps a live ledger for every customer and vendor inside Excel — each invoice, receipt and payment posts automatically and the running balance and statement update themselves, ready to share or reconcile. It's launching soon; join the waitlist.

Frequently asked questions

What is a party ledger? It's a date-wise statement of account for one customer or vendor, recording every bill and payment in Debit/Credit columns with a running balance, so you always know the net amount outstanding with that party.

How do I make a running balance in Excel? Use =PreviousBalance + Debit − Credit and copy it down. The first balance cell holds the opening balance; each later row adds that row's debit and subtracts its credit.

What is the difference between a debtor and a creditor ledger? A debtor (customer) ledger normally shows a balance the party owes you; a creditor (vendor) ledger shows a balance you owe the party. The format is the same — the sales/purchase entries sit on opposite sides.

Does a party ledger replace my receivables tracker? They complement each other. The ledger is the full transaction history per party; the receivables and payables trackers summarise open invoices across all parties.

Is the template free? Yes — free to download, no sign-up.

Sources

Microsoft Excel function reference — SUMIF, cell references and running totals: support.microsoft.com.
General accounting practice for ledger accounts and statements of account.

General information, not professional advice. Verify on the official portal for your case. Reviewed by a Chartered Accountant; last updated 27 June 2026.