Summary
A statement of account template lists every invoice, credit and payment for one customer, with a balance and a total owed. For Excel, use the columns Date, Activity, Reference, Due Date, Amount, Payments and Balance, with YYYY-MM-DD dates and plain numbers. Paidnice builds the same CSV from Xero or QuickBooks on a schedule.
- Two layouts: An outstanding statement lists only what is unpaid. An activity statement lists every transaction in a period, with a running balance.
- Clean format: Dates as YYYY-MM-DD, amounts with two decimals and no currency symbol or thousands separator, and one file per currency.
- With Paidnice: Build a CSV template from the columns you pick and attach it to scheduled statements, on every plan. The activity layout is for Xero only.
You email your customer their monthly statement as a PDF. Their accounts payable team replies that they need it as a spreadsheet, so they can match it against their own ledger. On Xero Product Ideas, one supplier says some customers now regularly refuse statements as a PDF.
Here is a statement of account template you can copy into Excel, the two layouts, the extra columns AP teams ask for, and the file format that imports without cleanup.
Statement of account template for Excel
This template uses seven columns: Date, Activity, Reference, Due Date, Amount, Payments and Balance, with a totals row at the end. Copy the table into Excel or Google Sheets, or save it as a CSV file.
The example is an outstanding statement from WellPaid co. to Northline Ltd, dated 2026-11-30. The Balance column shows what is still owed on each line.
| Date | Activity | Reference | Due Date | Amount | Payments | Balance |
|---|---|---|---|---|---|---|
| 2026-09-15 | Invoice | INV-1041 | 2026-10-15 | 1200.00 | 0.00 | 1200.00 |
| 2026-10-02 | Invoice | INV-1043 | 2026-11-01 | 850.00 | 350.00 | 500.00 |
| 2026-10-20 | Credit note | CN-0012 | -150.00 | 0.00 | -150.00 | |
| 2026-11-05 | Invoice | INV-1047 | 2026-12-05 | 2300.00 | 0.00 | 2300.00 |
| Total | 4200.00 | 350.00 | 3850.00 |
Above the table, add your business details, the customer's name and account number, and the statement date. Below it, add how to pay. Keep those details out of the data rows if the file will be imported, as the format section further down explains.
Free: Payment reminder email templatesCopy the reminder emails for before, on and after the due date.What must a statement of account show?
A statement of account must show who it is from and to, the statement date, each invoice, credit note and payment on the account, and the total the customer owes on that date.
Put these on every statement, whatever the layout:
- Your business: name, address, and a contact for account questions.
- The customer: their name and the account number you use for them.
- The statement date, and for an activity statement, the period it covers.
- One line per transaction: the date, the type, the reference, the due date and the amount.
- A balance on each line, and the total due at the end.
- How to pay: your bank details or a payment link.
Many businesses also add an aging summary (current, 1 to 30 days, 31 to 60 days and so on) so the customer sees which amounts are late. Our guide to 30, 60 and 90 day aging covers the brackets.
What is the difference between an outstanding and an activity layout?
An outstanding statement lists only the invoices and credits that are still open on the statement date. An activity statement lists every transaction in a period, paid or unpaid, from an opening balance to a closing balance.
Here is the same customer as an activity statement for October 2026. The Balance column is a running balance, so each row shows the account total after that transaction.
| Date | Activity | Reference | Due Date | Amount | Payments | Balance |
|---|---|---|---|---|---|---|
| 2026-10-01 | Opening balance | 1550.00 | ||||
| 2026-10-02 | Invoice | INV-1043 | 2026-11-01 | 850.00 | 2400.00 | |
| 2026-10-10 | Payment | INV-1039 | 350.00 | 2050.00 | ||
| 2026-10-20 | Credit note | CN-0012 | -150.00 | 1900.00 | ||
| 2026-10-28 | Payment | INV-1040 | 700.00 | 1200.00 | ||
| Total | 700.00 | 1050.00 | 1200.00 |
On an activity statement, the totals row shows the closing balance in the Balance column, not a sum. Check it: the opening balance of 1550.00, plus 700.00 of new charges, less 1050.00 of payments, is 1200.00.
| Outstanding layout | Activity layout | |
|---|---|---|
| Rows | One per unpaid invoice or open credit | Every transaction in the period |
| Paid invoices | Not shown | Shown |
| Payments | Only as part of a part-paid line | Each payment on its own row |
| Balance column | What is left on each line | Running balance |
| Totals row | Sum of each money column | Sums, with the closing balance |
| Best for | Monthly collections | Reconciliation and disputes |
Send the outstanding layout when you want to get paid. Send the activity layout when the customer's AP team is reconciling and asks why their balance differs from yours. Our guide to activity statements covers when to send each.
Which extra columns do AP teams ask for?
Accounts payable teams ask for the columns that let them match your statement to their own records: the PO number, the overdue age, the currency, and the tax and net amounts.
| Column | Why AP asks for it | Example |
|---|---|---|
| PO number | Their system files your invoice under their purchase order, not your invoice number | PO-88213 |
| Overdue age | Shows which lines to pay first | 45 |
| Currency | Stops a USD line being read as GBP | USD |
| Tax amount | Their ledger posts tax to its own account | 200.00 |
| Net amount | The amount before tax, to match the bill line | 1000.00 |
| Type | Separates invoices from credit notes and payments | Credit note |
Ask the customer which columns their system needs, and in what order. Many AP teams set up an import mapping once. If your column order or header names change, their mapping breaks.
Long PO numbers are a common complaint with PDF statements. On Xero Product Ideas, a supplier reports PO numbers being cut off on statements sent to clients. In a spreadsheet, the full PO number sits in its own cell.
How do you format a statement CSV so it imports cleanly?
A statement CSV imports cleanly when dates are YYYY-MM-DD, amounts are plain numbers with two decimals, there is no currency symbol or thousands separator, and each file holds one currency.
- Dates as YYYY-MM-DD. 03/04/2026 is 4 March in the US and 3 April in the UK. 2026-04-03 means the same day everywhere. Excel may show it in your local date format when it opens the file, and the value stays correct.
- Plain numbers. Write 1200.00, not $1,200.00. In a CSV the comma separates columns, so a thousands separator can split one amount into two cells.
- A minus sign for credits. Write -150.00, not (150.00). Brackets read as text in many import tools.
- One file per currency. A total that adds USD and GBP lines is wrong. Send one file per currency, with a Currency column if the customer wants it.
- One header row, then data. No logo, no merged cells, no blank rows and no notes between rows. Put your address and payment details in the email or a PDF beside the CSV.
- The same headers every month. Keep the header names and column order fixed, so the customer's import mapping keeps working.
Why does a manually built statement spreadsheet break at scale?
A manually built statement spreadsheet breaks at scale because each customer needs its own file, copied from a report, checked and attached every month. The work grows with every customer who asks for one.
Xero users describe the workaround on Xero Product Ideas: export the Aged Receivables report to Excel, delete every other customer, and attach the file. One user says the export only comes out in their base currency, which does not work for their USD customers. Another splits the full ledger per client and calls it very time-consuming.
The other failures come later:
- Stale balances. A payment that lands after you copied the report is missing from the file, so the customer's total differs from yours.
- Broken formulas. A row inserted above a SUM range drops out of the total.
- Drifting columns. One person adds a column, another renames one, and the customer's import fails.
- Missed months. When the person who builds the files is away, the statements stop.
How does Paidnice build a statement CSV from Xero or QuickBooks?
Paidnice builds a statement CSV from a spreadsheet template with the columns you pick, using your synced Xero or QuickBooks data, and attaches it to your scheduled statements. Statements are on every Paidnice plan. The step-by-step guides cover a Xero statement as a CSV and the QuickBooks Online version.
- Go to Organisation Settings, Statements, open the Custom tab and click New template. Pick Spreadsheet (CSV).
- On Xero, choose Outstanding or Activity. You cannot change this after the template is created. On QuickBooks a spreadsheet is always outstanding.
- Pick the columns. A new spreadsheet starts with Date, Activity, Reference, Due Date, Amount, Payments and Balance. You can add Overdue Age, Tax Amount, Net Amount, Currency, Type, Contact Name, Payment Date and Prompt Payment Discount, plus Statement Memo on QuickBooks.
- Type your own header for each column, up to 30 columns, and turn on the totals row if you want one.
- Click Save columns, preview the file with a real customer's data, click Download CSV to check it, then publish.
- Attach the template to a statement policy under Statement attachments. The policy sends monthly, monthly on weekdays, or weekly.
What is in the Paidnice statement CSV?
Any invoice custom field Paidnice has synced is in the column list too. On QuickBooks Online, a PO number is often a custom field on sales forms, so it can become its own column.
Paidnice writes the file in the format above: dates as YYYY-MM-DD and amounts as plain numbers with two decimals, with no currency symbol or thousands separator. A customer with more than one currency gets one file per currency. The CSV opens in Excel.
How does the CSV reach the customer?
One email can carry the PDF statement for the reader and the CSV for the AP system.
Customers can also download the CSV themselves from the customer portal, where you choose which spreadsheet templates to offer. The full setup is in the help article on editing a spreadsheet template, and the schedule options are on our page about sending customer statements automatically.
For the platform steps, see our guides to Xero statements and automatic QuickBooks statements.
💡 Paidnice insight
An AP team that asks for a spreadsheet is telling you how they decide what to pay. If your statement drops into their import with no cleanup, there is one less reason for your invoices to wait in their queue.
Frequently asked questions
Is there a free statement of account template in Excel?
Yes. Copy the template table on this page into Excel or Google Sheets. It has the columns Date, Activity, Reference, Due Date, Amount, Payments and Balance, with a totals row. Save it as a CSV if the customer wants to import it.
What is the standard customer statement format?
A customer statement shows your business and the customer's details, the statement date, one line per invoice, credit or payment, a balance on each line, and the total due. Outstanding statements list open items only. Activity statements list every transaction in a period.
Can Xero export a customer statement to Excel or CSV?
No. Xero prints or emails customer statements as a PDF only. The common workaround is an Aged Receivables Detail export to Excel, filtered to one customer.
Can QuickBooks Online export a customer statement to Excel?
Intuit's help article on customer statements covers sending a statement by email and printing or saving it as a PDF. It does not list a CSV or Excel export.
What is the difference between a statement of account and an invoice?
An invoice asks for payment for one sale. A statement of account lists all the invoices, credits and payments on a customer's account, and shows the total they owe. It does not create a new debt.
Should a statement of account show paid invoices?
An outstanding statement does not show paid invoices. An activity statement does, with each payment on its own row, so the customer can reconcile the period. Send the activity layout when they query their balance.
Paidnice features in this article
About the editorial team
Ashley is a co-founder and lead developer of Paidnice and has built accounting software for more than 10 years. He builds the ledger connections that put reminders, late fees and payments into Xero, QuickBooks and ERPs such as NetSuite and Business Central. Before Paidnice he co-founded A2X, ecommerce accounting software for Xero and QuickBooks.
App of the Year