Try the sheet here, on the example
The sheet is open below with sample numbers. Change any white cell and watch the results update, or press the blank-copy button to type your own data from scratch. Nothing you type leaves your browser.
Example from the sheet
| الكود | الاسم | إجمالي الفواتير | المدفوع | الرصيد المستحق | الحالة |
|---|---|---|---|---|---|
| C001 | مكتبة النور | 4,200.00 | 2,200.00 | 1,800.00 | عليه رصيد |
| C002 | بقالة الأمل | 4,600.00 | 500.00 | 3,800.00 | تجاوز الحد |
| C003 | محل الزهراء للملابس | 6,500.00 | 6,500.00 | 0.00 | مسدد |
| C004 | ورشة السلام | 1,500.00 | 2,000.00 | -500.00 | دائن |
| C005 | مطعم الواحة | 5,000.00 | 2,200.00 | 2,800.00 | عليه رصيد |
Example from the “العملاء” tab (Arabic), with the numbers and formulas that are in the file.
What is inside
- "العملاء" (Customers): For each customer: code, name, phone and credit limit, then total invoices, amount paid, returns, balance due, credit-limit usage % and status (owes, settled, credit balance, over limit), with the total due from all customers.
- "الحركات" (Transactions): Each invoice, payment or return with its date, customer code, document number and note. It shows the customer's name and their balance after each transaction, and warns you about a code that is not registered. It holds up to 2,000 transactions.
- "كشف حساب" (Statement of account): You choose the customer code and type the period. It shows the opening balance, the period's invoices, payments and returns, and the closing balance, then the customer's transactions line by line: date, type, document number, debit, credit and balance after each transaction.
The file opens on a start tab (“ابدأ هنا”, Start here) that explains how to use it.
What makes it different
The statement builds itself from the transaction log. You do not copy a customer's transactions or add them up by hand: choose the code and the period and the full statement appears, with the opening balance and the balance after every line, ready to print or send to the customer.
Customer statement of account template for Excel
A customer statement of account is what you send a customer so they know what they owe: their balance at the start of the period, then every invoice they took (debit) and every payment or return (credit), the balance after each transaction, and at the end the amount due now.
In the "كشف حساب" (Statement of account) sheet, choose the customer code from the list and enter "من تاريخ" (From date) and "إلى تاريخ" (To date) if you want a particular month or quarter, or leave them empty for all transactions. The opening balance is the total of the customer's transactions before the first day of the period, and the closing balance = opening balance + invoices − payments − returns. The header row repeats on every page when printed, and the statement holds up to 300 transactions.
Example statement from the sheet
In the example in the file, the statement for "بقالة الأمل" (Al-Amal Grocery, C002) runs from 10 September to 31 October 2026. Its opening balance is 1800, from an invoice on 3 September, then:
- 16 September, invoice INV-106, debit 2200, balance 4000
- 25 September, return RET-301, credit 300, balance 3700
- 7 October, payment REC-207, credit 500, balance 3200
- 11 October, invoice INV-110, debit 600, balance 3800
So its closing balance is 3800, and its status is "تجاوز الحد" (Over limit) because its credit limit is 3000.
Customer accounts sheet for tracking credit sales and collections
If you sell on credit, the first thing you need from a customer sheet is the answer to: who owes money, how much, and who has gone over the limit you allow? The "العملاء" (Customers) sheet answers this with one row per customer. It colours customers over their credit limit red, customers who have paid more than they owe ("دائن", credit balance) yellow, and settled customers green.
If you want the cash box and customer accounts in one file, try the cash box and customer accounts sheet, and if you need a printed invoice with tax, try the sales invoice sheet. The full step-by-step explanation is in the customer accounts sheet guide.
How to use it
- In the "العملاء" (Customers) sheet, enter each customer's code, name, phone and credit limit, which is the most credit you allow them.
- In the "الحركات" (Transactions) sheet, record each credit invoice, payment or return: date, customer code, transaction type and amount.
- The "العملاء" (Customers) sheet automatically calculates total invoices, amount paid, returns, balance due and each customer's status.
- For a customer statement, choose the customer's code in the "كشف حساب" (Statement of account) sheet and enter "من تاريخ" (From date) and "إلى تاريخ" (To date) for a particular period, or leave them empty. The transactions appear line by line with the balance after each one, ready to print.
- To see one customer's transactions, filter the customer code column in the "الحركات" (Transactions) sheet. The filter is already switched on in the header row.
Before you start:
- An invoice increases the balance; a payment and a return reduce it.
- A negative balance means the customer has paid more than they owe, and their status shows "دائن" (Credit balance).
- The sheet holds 300 customers and 2,000 transactions. Leave the credit limit empty if you do not set a limit for a customer.
Free cheat sheet for this field
Terms, abbreviations, tables and diagrams over several pages, in Arabic and English. Download it as a PDF or images, including a phone-size version.
Still running this on Excel?
This sheet needs someone to copy every invoice and payment into it. In Accountwiro an invoice and a payment are recorded the moment they happen, and the customer's balance shows in a single statement of account.
| In this sheet | In Accountwiro |
|---|---|
| You copy every invoice and payment into the sheet by hand. | It shows the customer's invoices, payments and outstanding balance in a single statement of account. |
| Invoices are typed or copied by hand. | It creates an invoice by scanning a barcode or searching by product name, calculates discounts and taxes, and sends it to the customer over WhatsApp. |
| Stock shortages are discovered late. | It updates quantities after every sale and alerts you when a product drops below the required level. |
| Anyone who opens the sheet sees every number. | Permissions per user: the sales employee issues invoices, and the warehouse employee manages stock without access to financial data. |
Frequently asked questions
How do I see how much each customer owes me in Excel?
Record every invoice, payment and return in a line. The sheet shows the customer's balance after each transaction, gives you a statement of account for any customer, and warns you about anyone over their credit limit.
How do I get a customer statement for a single month?
In the "كشف حساب" (Statement of account) sheet, choose the customer code, then type the first day of the month in "من تاريخ" (From date) and the last day in "إلى تاريخ" (To date). It shows the opening balance at the start of the month, the month's transactions line by line, and the balance at the end of the month.
Why does a customer's balance show as negative?
A negative balance means the customer has paid more than they owe, so the difference is owed to them, and their status shows "دائن" (Credit balance). When you record a new invoice for them, this balance is deducted from it automatically.
Do I have to record transactions in date order?
It is better to, because the balance after each transaction and the statement lines follow the order of the rows in the "الحركات" (Transactions) sheet. The totals, the opening balance and the closing balance are calculated by date, so they stay correct whatever the order.
Is the template free?
Yes, with no sign-up and no email address. Use it, edit it and share it as you like.
Does it work in Google Sheets and on mobile?
The sheet uses formulas supported by Excel 2016 and later, Google Sheets and LibreOffice, so you can upload it to Google Drive and open it with Google Sheets on a computer or a phone.
What is the difference between the white and the gray cells?
You type in the white cells; the gray cells are calculated automatically. Don't type in a gray cell, or you will erase its formula.
Can I use the sheet from an AI assistant?
Yes. Add our MCP server (URL https://softwiro.com/api/mcp, no key needed) to your assistant and ask it to do the calculation in this template. The numbers come from the sheet's own formulas, and we do not store your data. The steps and a ready-made prompt are in this article: https://softwiro.com/blog/mcp-excel-templates-ai-agent-guide.html
More free templates for: Shops and stores
More free templates
- Cash Book Template for Excel
- Invoice Template with Invoice Log (Excel)
- Clothing shop inventory sheet by colour and size
- Cash book and customer accounts sheet
- Installments and collection tracker
- General shop inventory tracker (stock in/out, reorder, slow movers)
- Supplier purchase orders and payables tracker
- Multi-warehouse and branch inventory sheet with transfers
- Periodic stock count and count-difference reconciliation sheet
- Barcode, batch and expiry inventory sheet
- Grocery store management sheet
- Stationery shop and photocopy management sheet
- Electronics shop sheet: serials, warranty and repairs
- Wholesale clothing trade sheet: traders, stock and collection
- Food wholesale and distribution sheet in Excel: routes, expiry and collection
- Delivery company sheet: shipments and cash collection
- Student attendance sheet
- Study planner and lesson tracker
- Journal entries, trial balance and financial statements sheet
- Weekly study routine sheet
- Online store orders and profit sheet
- Real estate leads and units tracker
- Rental portfolio tracker: properties, units, tenants and contracts





