Account reconciliation template: Free Excel and Google Sheets download with a worked example

An account reconciliation template should include the account and period, the GL balance, an independent supporting balance, each reconciling item with its date, owner, and age, an unexplained difference at zero or within threshold, and dated two-person sign-off. Schedule accounts, such as accruals and prepaids, also need a roll-forward.

• GL balanceThe account's ending balance on the trial balance at period end
• Supporting balanceThe same balance from an independent source, such as an AR aging, an accrual schedule, a bank statement, or the counterparty's ledger
• Reconciling itemA specific, dated amount that explains part of the difference, with an owner and a support reference

Here's where each part sits in the download:

Blue cells on a light yellow fill are inputs, and black cells are formulas. Sheets are protected without a password, so nobody types over a formula by accident. The file has no links to other files. One copy holds up to 150 accounts, 500 reconciling items, 500 schedule lines, and 1,000 trial balance rows; for a larger chart of accounts, use one copy per entity or account group.

You fill in the template in eight steps: set up the period, paste the trial balance, list your accounts, enter the support, log each reconciling item, post the entries, sign off, and carry open items into next month.

1. 1Set up the periodOn Summary, enter the company, period end, status-as-of date, and default threshold in C2:C5. The escalation rules sit in F2:F4.
2. 2Paste the trial balanceExport the trial balance for the period-end date from your accounting software and paste account number, name, debit, and credit as values on the Trial balance tab. Check that Balanced? reads Yes.
3. 3List your accountsAdd one Summary row per balance sheet account with its category, normal balance, risk, support source, and due business day. The GL balance fills in from the trial balance. If you build your task list in the free month-end close checklist generator, match each due day to its reconciliation tasks.
4. 4Enter the supportType the supporting balance in Support per report. For a schedule account, enter its lines on the Support tab and leave Support per report blank, and the Summary uses the schedule total.
5. 5Log each reconciling itemOn Items, enter the date it first arose, a description, the type, the amount as the change to that side's balance, an owner, a target date, and the support reference. Side and Entry needed fill in from the type.
6. 6Post the entriesPost a journal entry for every open item marked Entry needed: Yes. If you post it into the period and paste a refreshed trial balance, enter the period-end date as its cleared date so it drops out of the totals.
7. 7Sign offThe preparer adds a name and date on Summary, and a different reviewer adds theirs after reviewing the Workpaper tab.
8. 8Carry forwardSave a copy for the month. Next month, change the period end, paste the new trial balance, keep open items with their original dates, and enter a cleared date for each item that cleared.

The same steps set up an expense account reconciliation template: add the account to the Summary, pick its support, such as a vendor statement or an allocation schedule, and log items the same way. For a small business, list every account with a balance or activity in the period, plus any account your policy requires, keep the single default threshold, and use one preparer and one reviewer. Name each monthly copy by period, such as "Account reconciliations, October 2026," so last month's open items are easy to find.

In the worked example, Alder Ridge Software reconciles accrued expenses at October 31, 2026: the GL shows $611,400.00, the accrual schedule shows $604,220.00, and four reconciling items bring both sides to $607,720.00. The unexplained difference is $0.00, and three journal entries need to post.

The arithmetic on each side:

• GL side$611,400.00 − $4,800.00 − $6,000.00 + $7,120.00 = $607,720.00
• Support side$604,220.00 + $3,500.00 = $607,720.00
• The gapThe $7,180.00 difference before reconciling items is the $3,680.00 the GL is overstated by plus the $3,500.00 missing from the schedule

The reconciling items

Each item carries a type, an age, and an action, so the reviewer can see what clears it and who owns it. S. Patel owns all four.

AE-00, a September contractor accrual that cleared on October 5, stays on the Items tab as Cleared and out of the totals. The Workpaper's total of open items shown, ($180.00), equals the ($3,680.00) of GL-side items plus the $3,500.00 support-side item, so Items agree to balances reads Yes.

The schedule roll-forward

The roll-forward proves the schedule is complete before you rely on it: $634,050.00 opening + $280,220.00 of additions − $310,050.00 of reductions = $604,220.00.

The opening balance agrees to September's reconciled closing of $634,050.00, so the Opening check reads Agrees. A schedule that ties to its own roll-forward can still miss a line, which is why AE-04 exists. The $3,500.00 broker fee is already in the GL, so the fix is a new schedule line, not a journal entry. The guide to accrued expenses covers how to estimate each accrual on the schedule.

The journal entries

The three GL-side items produce three entries, all dated October 31, 2026.

Debits and credits each total $17,920.00. The entries lower accrued expenses by a net $3,680.00, which takes the GL to $607,720.00. They also lower October pre-tax income by $2,320.00: $7,120.00 of new utilities expense less the $4,800.00 legal fee reversal. JE-11-005 moves the balance between two liability accounts and doesn't touch income.

S. Patel prepared the reconciliation on November 4, 2026, and D. Ruiz reviewed it on November 5, its due date, so the status reads Reviewed and the timeliness reads On time.

Track up to 150 balance sheet accounts on the Summary tab: one row per account with its GL balance, supporting balance, unexplained difference, open items, oldest item, preparer, reviewer, due date, and a calculated status. Here are Alder Ridge's 12 accounts for October 2026, as of November 6:

The KPI block at the top of the Summary reads 12 accounts, 8 reviewed (66.7%), 3 overdue, 1 not reconciled, 9 open items, 1 item over the escalation age, 4 entries needed, and Ready to close? No. Deferred revenue is $2,300.00 off with no items logged, and accounts receivable, intercompany, and sales tax still wait for a reviewer. Accounts payable was reviewed a day after its due date, so it reads Reviewed late.

Used each month, the Summary tab doubles as a balance sheet reconciliation checklist in Excel: every account, its preparer and reviewer, its due date, and whether it's done.

Status is never typed. The workbook checks the reasons in a fixed order and shows the first one that applies:

Set a default threshold in Summary C5, $1,000.00 in the example, and override it per account in the Threshold column. Alder Ridge uses 0 for cash and intercompany, so those must agree exactly. Sales tax payable is $272.34 off, inside the $1,000.00 default, so its result reads Within threshold. Thresholds come from your company's reconciliation policy: they set when a difference must be investigated before sign-off, not when it stops mattering. The guide to balance sheet reconciliation covers how to set thresholds by account and how often to reconcile each one.

Due dates count business days after period end and skip weekends and the holidays on the Lists tab. October 31, 2026, is a Saturday, so business day 1 is Monday, November 2. Business day 8 is Thursday, November 12, because the workbook skips Veterans Day on November 11. To schedule the reconciliations alongside the rest of your close tasks, use the free month-end close calendar template.

The account reconciliation format in Excel is one row per account and one row per reconciling item, with unexplained difference = (GL balance + GL-side items) − (supporting balance + support-side items), rounded to two decimals. Each side's items total with SUMIFS, counting only items open at period end. These are the core formulas in the download, shown for row 8 of the Summary tab and row 6 of the Items tab:

TB_Account, TB_Net, Itm_Amount, PeriodEnd, DefaultThreshold, and Holidays are named ranges, so the formulas read the same on every row. A few choices keep the format honest:

• Blank checksExcel treats an empty cell as zero, so without the OR tests a blank row would read Reconciled.
• ROUNDIt removes tiny floating-point remainders that would otherwise show as a difference of 0.00 that isn't zero.
• A type dropdownEvery item's type comes from the Lists tab, and a typed value that isn't on the list turns the result to Check item types.
• One item per rowTotals update when you add a row, and you never retype a sum or a balance.

The workbook uses only IF, IFERROR, AND, OR, N, ROUND, ABS, SUM, SUMIFS, COUNTIF, COUNTIFS, SUMPRODUCT, MAX, INDEX, MATCH, ROW, TEXT, and WORKDAY. It avoids XLOOKUP, FILTER, IFS, MAXIFS, Excel Tables, and array formulas, so it runs in Excel 2010 and later and in Google Sheets.

Age each reconciling item from the date it first arose to the period-end date, not to today, so a signed reconciliation never re-ages, then group open items into 0–30, 31–60, 61–90, and over-90-day buckets. The Items tab does this for every row:

The four buckets and the resolution plan follow Vanderbilt University's Balance Sheet Account Reconciliations policy, effective January 2018. It groups reconciling items into 0–30, 31–60, 61–90, and over-90-day buckets, and it requires a documented resolution plan for items over $10,000 aged more than 90 days. All three limits are template defaults you can change in Summary F2:F4, and the 60-day escalation isn't from the policy.

In the example, AE-01 opened on August 14, when the legal invoice posted to AP and the July accrual stayed in place, so on October 31 it's 78 days old and shows Escalate. It's under $10,000.00, so it needs no resolution plan. The two cash items cleared on November 2 and 3, after period end, so they're still open at October 31, aged 1 and 2 days.

Keep the original date opened when an item carries into a new month. On November 30, 2026, AE-01 will be 108 days old: past 90 days but under $10,000.00, so it still shows Escalate rather than Resolution plan. Aging from the date you found the item would understate how long the balance has been wrong.

You can reconcile any balance sheet account with it, including cash, receivables, prepaids, fixed assets, intercompany, payables, accruals, payroll, deferred revenue, tax, and debt, because only the support source and threshold change.

Each cash account's support is its bank reconciliation, which the free bank reconciliation template prepares, so enter the bank statement balance here and log its deposits in transit and outstanding payments as support-side items, as the example does for 1010 Operating cash. Prepaids and accruals add a roll-forward on the Support tab, and the guide to prepaid expenses shows how to build the amortization schedule itself. For an intercompany reconciliation template across several entities, use a matrix of entity pairs, as the guide to intercompany accounting shows in Excel.

The same workbook also works as a general ledger account reconciliation template: enter any GL account, pick its support, and the formulas don't change. A GL account reconciliation proves one account, and a balance sheet reconciliation is the set of those reconciliations across every asset, liability, and equity account.

Paste your trial balance export into the Trial balance tab with account number, name, debit, and credit, and the Summary pulls each account's GL balance with SUMIFS, flipping the sign for credit-balance accounts.

Net is debit minus credit, so a liability shows as a negative net. The Summary multiplies it by −1 when the account's normal balance is Credit. Alder Ridge's accrued expenses sit in the Credit column at $611,400.00, the net is ($611,400.00), and the GL balance reads $611,400.00. Enter every balance on Summary and Support the same way, as a positive natural balance.

Whether you close in NetSuite, QuickBooks Online, Xero, Sage Intacct, or Microsoft Dynamics 365 Business Central, the steps are the same. Run the trial balance for the period-end date, export it to Excel or CSV, and paste the four columns as values from row 6. Two checks at the top of the tab catch a bad paste:

• Balanced?Reads Yes when total debits equal total credits, $49,686,193.54 on each side in the example.
• Summary accounts missing from TBCounts Summary accounts that aren't in the export, 0 in the example. A missing account leaves its GL balance blank and its status at Not started.

If your export has one signed balance column, enter the signed balance in Debit and leave Credit blank. Subaccounts that share an account number add together.

Open the Google Sheets link in the download box and choose Make a copy, or upload the .xlsx to Google Drive and open it with Google Sheets. The formulas, dropdowns, named ranges, and color rules carry over.

1. 1Keep the tab namesFormulas refer to Items, Support, Trial balance, Lists, and Workpaper by name, and to named ranges, which Data > Named ranges lists.
2. 2Paste values onlyPaste a trial balance with Edit > Paste special and paste values only, so the Net formulas in column E stay intact.
3. 3Share for reviewGive the reviewer Commenter or Editor access, and use version history (Last edit, at the top right) to see who changed a balance and when.
4. 4Save one copy per monthUse File > Make a copy and name it by period.
5. 5Print or save a PDFOpen the Workpaper tab, choose File > Print or File > Download > PDF, and pick Current sheet and Landscape.

In Excel, the Workpaper tab already prints to one landscape page. Choose File > Print, or File > Save As with PDF as the file type, to get a printable workpaper for the account in C6. It also serves as a general ledger reconciliation PDF for your audit file.

A reviewer checks that the GL balance agrees to the trial balance, the support is dated at period end, every item has an owner, age, and reference, the difference is within threshold, and the reviewer isn't the preparer. The preparer, usually a staff or senior accountant, owns the reconciliation, and the reviewer, usually an accounting manager or controller, signs off. The template enforces six of the checks:

Because the status is calculated and every item carries a date, owner, and support reference, an auditor can rerun the workpaper. It shows who prepared and reviewed each account, and when. The guide to balance sheet reconciliation linked above has the full reviewer checklist.

A spreadsheet template stops being enough when you reconcile dozens of accounts across entities, items carry for months, copies of the file drift apart, or auditors ask who changed a balance and when.

Spreadsheets also carry error risk of their own. A 2024 literature review by Poon, Lau, Yu, and Tang in Frontiers of Computer Science cites earlier studies that found about 94% of spreadsheets in use contained faults.

Reconciliation software pulls balances and transactions straight from the ledger and its sources. It matches routine items by rule, logs who changed what, and routes each reconciliation to its reviewer. When volume outgrows that, move matching and preparation into software and keep the template for exceptions and one-off accounts. The guide to the best account reconciliation software compares the options.

Our Close Agent prepares reconciliations as tasks on your close checklist: it reconciles the ledger to bank statements, subledgers, and other sources, shows every difference with its amount, date, source, and reference, and prepares the entry and workpaper. Every task has a named reviewer separate from the preparer, and nothing posts to your ERP until a named approver signs off. It reads from and posts approved entries to NetSuite, Oracle, SAP S/4HANA, Microsoft Dynamics 365, Business Central, and QuickBooks. To see it work on your own balance sheet accounts, explore our Close Agent.