Accounts Receivable Template for Excel & Google Sheets

A well-structured accounts receivable template needs 10 columns to track the full invoice lifecycle from issuance to collection. Columns A through F hold your raw data (invoice details and payments), while columns G through J use formulas to calculate balances, aging, and status automatically.

This structure works for practices managing 5 to 500 clients. For multi-entity bookkeeping, create separate tabs per client or use a Client ID column for filtering. Zera Books processes bank statements from any format and auto-matches deposits to outstanding invoices, eliminating the need for manual payment entry.

AR aging categorizes unpaid invoices by how long they have been outstanding. The 5-bucket system (Current, 1-30, 31-60, 61-90, 90+ days) is the accounting industry standard and matches the aging reports in QuickBooks, Xero, and Sage. Each bucket triggers a different collection action.

The standard formula for aging buckets in Excel is a nested IF:=IF(H2<=0,"Current",IF(H2<=30,"1-30",IF(H2<=60,"31-60",IF(H2<=90,"61-90","90+"))))where H2 is the Days Outstanding column. Pair this with your bank reconciliation process to verify that recorded payments match actual bank deposits.

These 6 formulas power your entire accounts receivable template. Copy them into row 2 and drag down for each new invoice entry. All formulas reference the column structure from Section 1.

Manual formula maintenance breaks when templates scale past 200 invoices. Automated bank statement conversion tools like Zera Books handle AR matching at scale. Upload bank statements and invoice PDFs together, and the AI matches payments to open invoices across all your client accounts simultaneously.

Raw invoice data means nothing without analysis. Add a Metrics Dashboard tab to your accounts receivable template that pulls from the main data and calculates these 4 collection health indicators automatically each month.

Tracking these metrics monthly reveals collection trends before they become cash flow problems. A DSO that increases from 35 to 52 days over three months signals a systemic issue — not just one late-paying client. Use this data alongside your month-end close process to flag collection gaps during financial review.

Your Excel AR template becomes most valuable when it feeds into your accounting software. Each platform requires specific column formats for invoice imports. Map your template columns to the required fields below before exporting.

Manual CSV formatting takes 30-45 minutes per client per month. The Zera Books QuickBooks integration and Xero bank feed setup handle formatting automatically. Upload documents, select the output format, and export pre-formatted files ready for import — including proper date formats, account mapping, and tax calculations.

Most bookkeepers build accounts receivable templates that track invoices but miss the patterns that cause collection failures. These 4 mistakes collectively add 5-8 hours of monthly reconciliation work and inflate your AR balance by 15-25%.

These mistakes compound across multiple clients. Batch processing 50+ statements at once through Zera Books eliminates manual payment matching entirely. The AI detects partial payments, duplicate deposits, and mismatched amounts — catching errors that spreadsheet formulas miss.