--- type: Template title: Accounts Receivable Reconciliation Template (Free Excel) description: A free accounts receivable reconciliation template for Excel. Reconcile your AR sub-ledger to the general ledger control account and flag differences automatically. resource: https://accounting.events/resources/accounts-receivable-reconciliation-template/ tags: [accounts receivable reconciliation template, accounts receivable reconciliation example, ar reconciliation template excel, reconcile accounts receivable] timestamp: 2026-09-28 author: Harry P. Smart fact_checked_by: Julie Turnbridge fact_checked: 2026-10-26 --- AR Reconciliation # Accounts receivable reconciliation template A free Excel template to reconcile your accounts receivable sub-ledger to the general ledger control account, with differences flagged automatically. By Harry P. Smart, CPA and Lead Reviewer. Fact-checked by Julie Turnbridge on October 26, 2026. Published June 9, 2026. Updated September 28, 2026. 7 min read. Quick answer **Accounts receivable reconciliation is the process of checking that the total of your customer balances (the AR sub-ledger) agrees with the accounts receivable control account in your general ledger.** When the two do not match, something has been posted to one but not the other, and the difference needs investigating. This free template compares each customer's sub-ledger balance with their statement, flags differences, and reconciles the total to the GL control account, where the difference should be zero. It works in Excel or Google Sheets. If your invoices are in Xero or QuickBooks Online, Paidnice posts late fees and interest straight to the ledger, so those charges already match when you reconcile. ## Key facts about AR reconciliation - The sub-ledger total must equal the GL AR control account. In the example, 14,860 against 15,860 leaves a gap of 1,000. - Receivables arise under the accrual method, which counts income when earned ([IRS Publication 538](https://www.irs.gov/publications/p538)). - Keep reconciliations 3 years, or 7 years for a bad debt deduction ([IRS](https://www.irs.gov/businesses/small-businesses-self-employed/how-long-should-i-keep-records)). Preview Xaccounts-receivable-reconciliation-template.xlsxAccounts Receivable ReconciliationCustomerSub-ledgerStatementDifferenceNorthwind Trading4,2004,2000Blue Mountain Cafe1,9601,9600Harbor Logistics2,2003,200-1,000InvestigateVertex Builders6,5006,5000Sub-ledger total14,860Reconciliation to the general ledgerGL AR control account15,860Less: sub-ledger total14,860Difference (should be 0)1,000Customer differences flagged; sub-ledger total reconciled to the GL control account. Free AR reconciliation template Excel (.xlsx) that flags customer differences and reconciles to the GL control account, plus a CSV. [Download Excel](https://accounting.events/resources/accounts-receivable-reconciliation-template/accounts-receivable-reconciliation-template.xlsx)[Download CSV](https://accounting.events/resources/accounts-receivable-reconciliation-template/accounts-receivable-reconciliation-template.csv) ## How the reconciliation works There are two checks. First, per customer, your sub-ledger balance should match what the customer agrees they owe. Second, the total of all customer balances should equal the AR control account in the general ledger. The core check **Sub-ledger total (sum of all customer balances) = General ledger AR control account** If they differ, the gap is a reconciling item to find and fix. At higher volumes an [integrated receivables](https://accounting.events/resources/what-is-integrated-receivables/) platform matches each payment to its invoice before the reconciliation starts. ## Filled-in example | Customer | Sub-ledger | Statement | Difference | | --- | --- | --- | --- | | Northwind Trading | 4,200 | 4,200 | 0 | | Blue Mountain Cafe | 1,960 | 1,960 | 0 | | Harbor Logistics | 2,200 | 3,200 | -1,000 | | Vertex Builders | 6,500 | 6,500 | 0 | | **Total** | **14,860** | **15,860** | **-1,000** | The statement total of 15,860 matches the GL, so one missing 1,000 Harbor Logistics invoice explains the gap. Post it and the difference clears. ## What should an AR reconciliation template include? A reconciliation template needs four parts so anyone can follow the check and see who signed it off: - **Header information:** the entity, the period end date and the GL account being reconciled. - **Balance comparison:** the sub-ledger total against the GL control account, with the difference. - **Reconciling items:** each item that explains the difference, such as unrecorded payments or timing differences. - **Sign-off:** who prepared the reconciliation, who reviewed it, and the dates. ## How do you reconcile accounts receivable? Total your AR sub-ledger (the sum of all customer balances) and compare it to the accounts receivable control account in your general ledger. Investigate any difference, correct the errors, and confirm the two balances agree. ## What is accounts receivable reconciliation? It is the control check that your detailed customer balances add up to the single AR figure in your general ledger. ## What are common reconciling items? Unrecorded or deposited-in-transit customer payments, invoices posted to the ledger but not the sub-ledger (or vice versa), credit notes or write-offs applied to one record only, period-end timing differences, and misposted or duplicated amounts. ## How often should I reconcile AR? Most businesses reconcile accounts receivable monthly, as part of the period-end close. ## Does the template work in Google Sheets? Yes. Open the Excel file or use the CSV. SUM and IF work identically in both. ## What mistakes should you avoid with this template? - **Plugging the gap.** A 1,000 plug entry hides the missing Harbor invoice. Fix: find and post the real item. - **Checking only the total.** A 1,400 error on Harbor and a 400 error on Vertex also net to a 1,000 gap. Fix: clear each customer line first. - **Using different dates.** A 1,200 payment posted on the 31st in one record and the 2nd in the other creates a false 1,200 gap. Fix: use one report date. ## What if the sub-ledger and general ledger do not match? Trace each reconciling item until the difference is 0. In the example, one missing Harbor Logistics invoice explains the 1,000. ## Should a write-off be posted to both records? Yes. A write-off posted to only one record creates a gap equal to its amount. ## Do I need customer statements to reconcile AR? Not for the control total, but statements catch errors the total hides. Bottom line: this template suits a business with a few dozen customer accounts and one person closing the books. Move to software when matching payments takes hours each month. ## Related reading Independent software reviews and guides from Accounting.Events. [Template ### AR ledger template The customer sub-ledger this reconciliation checks. Read more →](https://accounting.events/resources/accounts-receivable-ledger-template/)[Template ### AR aging report template Group unpaid invoices into aging buckets automatically. Read more →](https://accounting.events/resources/accounts-receivable-aging-report-template/)[All resources ### Resources, templates and calculators Every free AR guide, template and calculator we publish. Read more →](https://accounting.events/resources/) **About the author: Harry P. Smart, CPA and Lead Reviewer.** Harry P. Smart is a CPA based between London and New York City. Harry writes and leads the review of the software guides on Accounting.Events. **Fact-checked by Julie Turnbridge, Freelance writer and bookkeeper**, on October 26, 2026. Checked prices, review counts, awards, integrations and dates against each primary source. Last updated September 28, 2026. This guide is general information, not accounting, tax, or financial advice. ## Sources Sources checked by Julie Turnbridge on October 26, 2026: 1. [IRS Publication 538](https://www.irs.gov/publications/p538) (irs.gov/publications/p538) 2. [IRS](https://www.irs.gov/businesses/small-businesses-self-employed/how-long-should-i-keep-records) (irs.gov/businesses/small-businesses-self-employed/how-lon...)