# Customer accounts, credit sales and customer statement sheet

> Invoices, payments and returns per customer, running balance after each transaction, credit-limit alerts and a printable customer statement for any period.

Source: https://softwiro.com/templates/customer-ledger
Language: English
Softwiro, softwiro.com

---

Free Excel template

A free Excel sheet for customer accounts and credit sales. You record invoices, payments and returns, and the sheet calculates each customer's balance and running balance after every transaction, warns you about anyone over their credit limit, and gives you a printable statement of account for any customer and any period, line by line.

[Download the free sheet (with a worked example)](https://softwiro.com/downloads/templates/softwiro-customer-ledger.xlsx) [Blank copy, no example](https://softwiro.com/downloads/templates/softwiro-customer-ledger-blank.xlsx)

Excel file (.xlsx) · 242 KB · 5 tabs · works in Excel and Google Sheets · no sign-up

The workbook is in Arabic (right-to-left, Arabic tab and column names). This page explains every tab in English.

The blank copy has the same formulas, charts and lists without the example data; categories and settings are ready.

## 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](https://softwiro.com/templates/cash-customers-account), and if you need a printed invoice with tax, try the [sales invoice sheet](https://softwiro.com/templates/invoice-template). The full step-by-step explanation is in the [customer accounts sheet guide](https://softwiro.com/blog/customer-ledger-free-excel-online.html).

## How to use it

1. In the "العملاء" (Customers) sheet, enter each customer's code, name, phone and credit limit, which is the most credit you allow them.
2. In the "الحركات" (Transactions) sheet, record each credit invoice, payment or return: date, customer code, transaction type and amount.
3. The "العملاء" (Customers) sheet automatically calculates total invoices, amount paid, returns, balance due and each customer's status.
4. 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.
5. 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.

[Download the free sheet (with a worked example)](https://softwiro.com/downloads/templates/softwiro-customer-ledger.xlsx) [Blank copy, no example](https://softwiro.com/downloads/templates/softwiro-customer-ledger-blank.xlsx)

Excel file (.xlsx) · 242 KB · 5 tabs · works in Excel and Google Sheets · no sign-up

The workbook is in Arabic (right-to-left, Arabic tab and column names). This page explains every tab in English.

The blank copy has the same formulas, charts and lists without the example data; categories and settings are ready.

## 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.

[![Cheat sheet: customer accounts and credit (1/3)](https://softwiro.com/uploads/cheatsheets/customer-ledger/en-1.jpg)](https://softwiro.com/uploads/cheatsheets/customer-ledger/en-1.jpg)[![Cheat sheet: customer accounts and credit (2/3)](https://softwiro.com/uploads/cheatsheets/customer-ledger/en-2.jpg)](https://softwiro.com/uploads/cheatsheets/customer-ledger/en-2.jpg)[![Cheat sheet: customer accounts and credit (3/3)](https://softwiro.com/uploads/cheatsheets/customer-ledger/en-3.jpg)](https://softwiro.com/uploads/cheatsheets/customer-ledger/en-3.jpg)

0 downloads · 3 pages

## 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. |

[See Accountwiro](https://softwiro.com/systems/budoor.html) · [Ask for a demo](https://softwiro.com/contact)

## When your work grows: a custom system instead of the sheet

A sheet is a great start. When more than one person works on your data, when it grows, or when you need alerts and reports of your own, we build you a custom system at competitive prices. This is what changes:

| In the sheet | In a custom system |
| --- | --- |
| One file that one person edits at a time, and copies travel by WhatsApp. | A program with a login for each user and permissions you set; everyone sees the same data live. |
| You type and check every number by hand, and one wrong cell breaks the result. | Screens built around your steps: each person records only their part, and the calculations run on their own. |
| Limited size; the file slows down as the data grows. | Holds years of data without slowing down, with instant search and filters. |
| Fixed columns and reports, as the sheet was designed. | Reports, alerts, printing and WhatsApp built around how you work. |
| Backups and security are your own responsibility. | A secured server with automatic backups and a log of who changed what and when. |

### Extra features in the app

- A login for each user with permissions, and a log of who changed what and when
- Automatic reminders and alerts by WhatsApp and email
- Photos, files and documents kept with each record
- Instant search and filters, with reports exported to PDF and Excel
- Works from a phone anywhere, with automatic backups

[Ask for your custom system](https://softwiro.com/contact)

## 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](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](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

[All templates](https://softwiro.com/templates) · [Free tools](https://softwiro.com/tools)
