Customer Account Tracking Excel
A statement table that tracks the debit–credit balance per customer or supplier account across all transactions and gives due-date warnings.
What is an account ledger and how do you track it?
An account ledger (cari hesap) is a date-ordered record of the debt-and-credit relationship between you and a customer or supplier. Every sale and purchase is recorded as a debit, and every collection and payment as a credit; the difference between the two is that company's balance. To track it in Excel, columns for account code, transaction type, debit, credit and running balance are enough.
How are debits and credits recorded?
An invoice you issue to a customer means they owe you: it goes in the Debit column. Money you receive from them reduces the debt: it goes in the Credit column. On a supplier account the direction is reversed — goods you buy are your debt.
The most practical way to avoid confusion is to separate customer and supplier accounts with different code prefixes (such as C-1001, S-2001).
Running balance formula
To see each account's balance up to that point on every row, use a cumulative SUMIF: =SUMIF($C$2:$C2,C2,$G$2:$G2)-SUMIF($C$2:$C2,C2,$H$2:$H2). Thanks to the fixed start ($C$2) and the moving end (C2) in the formula, the range grows as you move down the rows and the balance runs on.
This structure lets you track dozens of accounts in the same file without mixing them up; the only condition is that the same company is always written with the same code.
Due date tracking and collection discipline
A receivable without a due date is a receivable nobody follows up. Write a due date on every debit row and set up an automatic warning with the logic =IF(DUE<TODAY(),"OVERDUE","NOT DUE").
Once a week, apply the "OVERDUE" filter and sort by amount; by calling the three largest items you collect a significant part of what's owed.
Issuing a statement
A statement is essential when settling accounts with a customer. When you select the relevant account in the filter and print, you get a record containing date, document number, debit, credit and balance. Before sharing the statement, make sure the description column is filled in.
To turn this record from your tracking file into a letterhead document to send to the customer, we prepared a separate template: the account statement sample Excel template automatically fills an A4 statement with stamp-and-signature areas as soon as you enter the transactions.
We explained what each column of a statement means and how to read it, with a sample table, on the what is an account statement page. Comparing the statement with the other party and getting the figures to match is a separate job: how to reconcile accounts. For the document itself that you send for the other party to sign, you can fill in the account reconciliation form Excel template.
Where do Excel's limits begin?
Once you have more than 50 accounts or more than 400 transactions a month, the margin for error in manual entry rises noticeably. If you need automatic payment reminders, mobile access, sharing the statement via WhatsApp and invoice-to-collection matching, using an app is safer.
If you have passed this threshold, the Account Ledger: Debt & Credit app does the same job together with due-date reminders, cash tracking and cheque/promissory note tracking; the column structure in your template can be imported there from Excel.
Frequently asked questions
What does it mean if an account balance comes out negative?
In this template, a negative balance means the amount you collected from that company is more than its debt — that is, you received an advance or an overpayment was made.
How do I enter an opening balance?
Open the first row with the "Opening" transaction type and write the current debt in the Debit column. You don't need to enter past transactions one by one.
What if the same customer has several branches?
Give each branch a separate account code but write the same company name; you can then get reports by branch and by total.