Softwiro
Free Excel template

Customer accounts, credit sales and customer statement sheet

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.

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

Example from the “العملاء” tab (Arabic), with the numbers and formulas that are in the file.
الكودالاسمإجمالي الفواتيرالمدفوعالرصيد المستحقالحالة
C001مكتبة النور4,200.002,200.001,800.00عليه رصيد
C002بقالة الأمل4,600.00500.003,800.00تجاوز الحد
C003محل الزهراء للملابس6,500.006,500.000.00مسدد
C004ورشة السلام1,500.002,000.00-500.00دائن
C005مطعم الواحة5,000.002,200.002,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

  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.

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)Cheat sheet: customer accounts and credit (2/3)Cheat sheet: customer accounts and credit (3/3)
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 sheetIn 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 · Ask for a demo

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

All templates · Free tools