You sell on credit and you want to know, at any moment, how much each customer owes and who has gone past what you agreed to let them owe. Without a system this means leafing through invoice copies and receipts and adding them up by hand, usually when a customer calls to dispute the figure. This free Excel sheet does the adding for you. You record each invoice, payment and return once, and the balance follows. (The template and its page are in Arabic; the sheet itself is in Arabic too.)
What the template gives you
The workbook has these sheets:
- Start here. A short guide to filling it in.
- Customers (العملاء). One row per customer: code, name, phone and credit limit, which is the most you allow them to owe. The sheet then shows total invoices, total paid, returns, the balance due and a status. The total owed by all customers sits above the table.
- Movements (الحركات). Every invoice, payment or return with its date, customer code and amount. The last column shows that customer's balance after each movement, so you can see when the debt rose and when it was paid down.
- Statement (كشف حساب). Type a customer code and you get their name, totals and balance in one place.
- Why still on Excel? A short note on when a spreadsheet stops being enough.
The rule behind all of it is simple: an invoice raises the balance, a payment or a return lowers it. A negative balance means the customer paid more than they owed.
A walkthrough with the sample numbers
The numbers below are sample data that ship with the template, not real customers.
| Customer | Invoices | Paid | Balance | Status |
|---|---|---|---|---|
| Al-Nour Bookshop (C001) | 4,200 | 2,200 | 1,800 | has a balance |
| Al-Amal Grocery (C002) | 4,600 | 500 | 3,800 | over the limit |
| Al-Zahraa Clothes (C003) | 6,500 | 6,500 | 0 | settled |
| Al-Salam Workshop (C004) | 1,500 | 2,000 | -500 | creditor |
| Al-Waha Restaurant (C005) | 5,000 | 2,200 | 2,800 | has a balance |
Look at the two interesting rows. Al-Amal Grocery has a credit limit of 3,000 and a balance of 3,800, so its status reads "over the limit" and the row turns red. Al-Salam Workshop paid 2,000 against a single invoice of 1,500, so the balance is -500 and the status is "creditor": you owe them or you should apply it to the next invoice. Note also that Al-Nour's balance (1,800) is not simply invoices minus payments: it is 4,200 minus 2,200 paid minus 200 of returns. The total balance due across the five sample customers is 7,900.
Open the statement sheet with code C002 and you see Al-Amal Grocery with 4,600 in invoices, a balance of 3,800 and the status "over the limit", plus the number of movements behind it.
Features and why they matter
- No manual totals. Invoices, payments and returns are summed by customer code, so a forgotten addition cannot happen.
- Automatic status. Over the limit, creditor, settled or has a balance, without you reading each row.
- A running balance per movement. You can answer "when did this customer last pay?" from the sheet.
- Typos are caught. A customer code that does not exist in the customers sheet is flagged instead of silently disappearing from the totals.
- Works in Excel 2016 and later, Google Sheets and LibreOffice. Free, and you do not sign up for anything.
Using the online version
On the template page the sheet opens directly on a worked example with numbers. Edit the white (input) cells right in the page and the grey calculated cells update immediately. You can import your own .xlsx file made from this template, export your work to .xlsx, or download the original Excel file. Your data stays in your browser and is not sent to the server, so closing the tab without exporting loses it: export when you are done.
The template page is here: customer ledger template. The full list is at free templates.
Common mistakes, and when a sheet stops being enough
The usual errors are recording a payment as a negative invoice (use the "payment" type instead), typing the customer code differently in two places, and leaving the credit limit blank for customers who should have one (a blank limit means no limit). The sheet holds 300 customers and 2,000 movements. If someone has to copy every invoice and payment into it by hand, the weak point is the copying, not the formulas. Accountwiro is the Softwiro system for this: the invoice and payment are recorded when they happen, and the customer's balance shows in one statement. Before choosing any system, read what to settle before you buy business software.
FAQ
How do I see only one customer's movements? In the movements sheet, filter the customer code column; the filter is already on in the header row. Or type the code in the statement sheet to see the totals.
What if a customer has no credit limit? Leave the limit empty. They will never show "over the limit", only their balance.
Can I use it for suppliers? The logic is the same, but the sheet is written for customers, so rename the labels yourself or keep a separate copy.
Next step
Open the template page, look at the example, replace the five sample customers with your own and record this week's invoices.