Account Reconciliation Template in Excel: A Copyable Layout

Quick Answer: A usable account reconciliation template in Excel has five blocks: a header (account, period end, preparer, reviewer), the balance per the general ledger, the balance per the supporting source, a list of reconciling items with an age and an owner, and a difference cell that must reach zero before anyone signs. Build one tab per balance sheet account and keep the layout identical across tabs.

A balance sheet reconciliation template is a one-page worksheet that proves a ledger balance is real. It puts the general ledger figure next to independent evidence, such as a bank statement, a loan schedule or an aged payables report, lists every item that explains the gap, and records who prepared and reviewed it. The layout below works for cash, receivables, prepaid expenses, accruals and loans.

What does a balance sheet reconciliation template need to contain?

Most templates that circulate online fail in one of two directions. Some are a bank reconciliation stretched to fit every account, with "deposits in transit" rows that make no sense for a prepaid insurance balance. Others are a blank grid with "Balance" and "Notes" and nothing that forces the preparer to explain anything. A good balance sheet account reconciliation template sits between those: fixed structure, flexible detail.

Every tab should carry these fields, in this order:

1. Header. Entity, account number and name from the chart of accounts, period-end date, preparer, preparation date, reviewer, review date.
2. Balance per GL. The ending balance from the trial balance for that date, typed or linked, never estimated.
3. Balance per support. The number from the outside or subsidiary source: the bank's closing balance, the lender's principal statement, the fixed asset register total, the AR aging total.
4. Reconciling items. One row per item, each with a date, description, amount, category (timing difference or error), the person responsible for clearing it, and the date you expect it to clear.
5. Unexplained difference. GL balance minus support, minus the total of reconciling items. This cell is the whole point of the sheet.
6. Sign-off. Initials and dates, plus a short comment field for the reviewer.

The separation between timing differences and errors matters. A check that has been written but not cashed is a timing difference and will clear by itself. A vendor bill posted twice is an error and needs a journal entry. If both sit in one undifferentiated "adjustments" column, nobody can tell which items need action.

A layout you can copy

Set up the worksheet with the header in rows 1 to 6 and the calculation block underneath. The column letters below assume the labels sit in column A and values in column B.

Rows 13 onward hold the reconciling items table: date in column A, reference in B, description in C, category in D, amount in E, owner in F, expected clear date in G and age in days in H, calculated with =$B$3-A14. Format it as an Excel table so new rows inherit the formulas.

Two details make the template survive real use. First, use ABS and a half-cent tolerance in the status cell, because floating-point arithmetic in spreadsheets will sometimes leave 0.0000001 where you expect zero. Second, decide one sign convention and write it on the sheet: "positive amounts in the reconciling items table are added to the support balance to reach the GL." Without that sentence, every reviewer argues about signs.

If you keep a separate summary tab, SUMIFS pulls totals by category from the item tables. Microsoft documents the syntax as SUMIFS(sum_range, criteria_range1, criteria1, …) on its SUMIFS function page, and up to 127 range and criteria pairs are allowed, which is far more than a reconciliation summary needs.

Which supporting document goes with which account?

The template is only as good as the number in row 8. Here is what usually counts as independent support for common small-business accounts:

For cash and card accounts, the statement often arrives as a PDF. You can type the closing balance into row 8 by hand, but the reconciling items are easier to find if the transaction list is in a spreadsheet next to the ledger export. The bank's own CSV download is the better source when it covers your period; when it does not, or you only have the PDFs, a converter such as QuickBankConvert turns the statement into rows with date, description, amount and running balance, and reads the opening and closing balances so you can drop them straight into the template. A free account gets CSV and Excel downloads; the limits are on the pricing page.

For the step-by-step bank version of this work, including the matching formulas, see bank reconciliation in Excel.

How should the workbook be organized for a full month?

One workbook per month, one tab per account, plus three fixed tabs at the front:

• TB. Paste the trial balance as of period end. Every "Balance per GL" cell links here with XLOOKUP or INDEX/MATCH on the account number, so a late adjusting entry flows through when you re-paste.
• Index. A list of every balance sheet account, its owner, the tab name, the unexplained difference pulled from that tab, and the sign-off status. This is what a controller or outside accountant reviews first.
• Thresholds. The materiality rule for the month, for example "investigate any unexplained difference above $50 and any reconciling item older than 60 days." Writing the rule down stops each preparer from inventing their own.

Copy last month's workbook to start the new one, clear the item tables, and roll the period-end date. Items that did not clear carry forward deliberately: copy them, keep their original date, and let the age column show how long they have been open. An item that keeps aging is the most useful warning the template can give you.

What makes a reviewer reject a reconciliation?

Reviewers tend to send sheets back for the same handful of reasons. Building the checks into the template saves the round trip.

• The GL number is typed, not linked. If the trial balance changes after an adjusting entry, a typed figure is silently wrong. Link it.
• Support is not attached or referenced. Put the file name or the document location in the header. A balance with no evidence is an assertion, not a reconciliation.
• A plug. A reconciling item called "difference" or "rounding" that is larger than a few cents means the work stopped early.
• Old items with no owner. A deposit in transit from four months ago is not in transit. Either it was never received or it was recorded twice.
• Mixed periods. The supporting balance is dated the 28th because that is when the statement cycle closed, but the GL is at month end. Note the cut-off difference as its own item or get a statement that matches.

If you are new to the underlying process rather than the layout, how to do account reconciliation walks through it account by account, and reconciling the balance sheet covers the month-level view.

Excel template or accounting software?

QuickBooks Online, Xero and similar packages have a bank reconciliation screen that is better than any spreadsheet for cash and card accounts, because the matching happens against the transactions already in the books. Use it when it exists. The Excel template is still worth keeping for three jobs those screens do not cover well: non-cash balance sheet accounts such as prepaids and accruals, the month-end index that shows every account's status in one place, and a reviewer sign-off trail that lives outside the software.

Dedicated close-management tools exist for larger finance teams and add workflow, attachment storage and audit logs. For a business with a dozen balance sheet accounts and one or two people closing the books, a well-structured workbook does the job, and the IRS's recordkeeping guidance is clear that any system is acceptable as long as it clearly shows income and expenses and is backed by supporting documents.

Related guides

• Month-end closing checklist in Excel
• Account reconciliation meaning
• Bank reconciliation example
• PDF bank statement to Excel