Free Accounts Receivable Templates for Excel

An accounts receivable template is a ready-made spreadsheet for recording customer invoices, tracking what is still owed, and grouping balances by how overdue they are. The four free Excel templates on this page cover the core AR tasks for B2B finance teams: an invoice tracker, an aging report, a month-end reconciliation, and a KPI dashboard. Each one calculates due dates, days past due, and totals automatically.

What Is an Accounts Receivable Template?

An accounts receivable template is a pre-built spreadsheet with columns and formulas for managing money customers owe. A typical AR template records the customer, invoice number, invoice date, payment terms, amount, and payments received, then calculates the due date, open balance, days past due, and aging bucket for each invoice.

Templates give small and mid-size AR teams a consistent structure without setting up a system, and give larger teams a quick way to analyze an export from their ERP.

4 Free Accounts Receivable Templates

Every template works in Microsoft Excel and Google Sheets, includes sample data you can replace, and uses blue text for the cells you edit. Shaded cells contain formulas.

AR Invoice Tracker Template

The AR invoice tracker is a single list of every open invoice that updates each invoice's status as payments are recorded. It is the template to use for day-to-day collections work.

What it includes:

Best for: Collectors and AR specialists who need a working list of what to follow up today.

Accounts Receivable Aging Report Template

The aging report template groups every customer's open balance into current, 1 to 30, 31 to 60, 61 to 90, and over 90 days past due. It is the standard view for prioritizing collections and supporting the allowance for doubtful accounts.

What it includes:

Best for: AR managers and controllers who review aging weekly or at month end. Each bucket signals a different collection priority, which is why accounts receivable aging reports are the starting point for most collection plans.

Accounts Receivable Reconciliation Template

The reconciliation template compares the AR aging total to the general ledger accounts receivable balance and lists every item that explains the difference. It shows the adjusted balances and confirms when the difference reaches zero.

What it includes:

Best for: Staff accountants and controllers completing the month-end close. The step-by-step method behind the template follows the accounts receivable reconciliation process.

AR Dashboard Template

The AR dashboard template turns an invoice list into the metrics leadership asks for: total open AR, past due, percentage past due, over 90 days, disputed amount, DSO, and an aging chart. It also includes a by-customer aging view.

What it includes:

Best for: Finance leaders who want a quick weekly snapshot of receivables. For a one-off DSO figure without a spreadsheet, the DSO calculator gives the result in seconds.

Using the Templates: Step by Step

Each template follows the same five steps: set the as-of date, replace the sample rows with your invoices, enter payments as they arrive, review the calculated columns, and update the file on a fixed schedule.

1. Set the as-of date

Open the Settings tab and enter today's date or your period-end date. Days past due and aging buckets calculate from this date.

2. Add your invoices

Replace the sample rows with your open invoices, or paste an export from your ERP into the blue columns.

3. Record payments

Enter amounts paid as they arrive. Open amounts, status, and totals update automatically.

4. List your customers

On the aging summary or by-customer tab, enter each customer name once so balances group correctly.

5. Update on a schedule

Refresh daily for collections, weekly for management review, and at month end for the close.

Excel AR Template Format: Columns to Include

A well-structured accounts receivable format in Excel includes columns for customer, invoice number, PO number, invoice date, payment terms, due date, invoice amount, amount paid, open amount, days past due, aging bucket, status, promise-to-pay date, dispute flag, collector, and notes. These fields cover what collectors, managers, and auditors need from the same file.

The two formulas that matter most are days past due and aging bucket. Days past due is the as-of date minus the due date, with a minimum of zero. The aging bucket then assigns each open invoice to current, 1 to 30, 31 to 60, 61 to 90, or over 90 days based on that number. Both are built into every template on this page.

Tips for Keeping an Excel AR Tracker Accurate

An Excel AR tracker stays accurate when payments are recorded the day they arrive, disputed invoices are flagged rather than chased, the as-of date is updated before every review, the aging total is tied to the general ledger every month, and only one shared version of the file is used.

When Spreadsheets Stop Working for AR

Spreadsheets work well for a few hundred open invoices and a small team, but they stop working when invoice volume grows, several collectors need the same data at once, payments must be matched daily, or leadership needs current numbers rather than last week's export. At that point, version conflicts, manual updates, and missed follow-ups start to cost cash.

Quick Receivable replaces the tracker, aging report, and dashboard with live views inside Salesforce. The accounts receivable dashboard shows total AR, overdue AR, current due, and DSO, along with results by collector and the top overdue accounts. Aging updates automatically from Current through 120+ days, and reports export to PDF or Excel when you still need a file. Reminders, promises to pay, and disputes are tracked on the same invoices through accounts receivable collections software, so nothing depends on a spreadsheet being up to date.

To estimate what moving off spreadsheets could save your team, try the AR ROI calculator.

Frequently Asked Questions