A general ledger reconciliation template in Excel is a one-sheet workpaper that ties an account's ledger balance to its subledger or source, lists the reconciling items with their age, and records who prepared and reviewed it. Fill the header, show both balances, explain the gap, and sign. A filled accrued liabilities example follows.
Which fields belong in a general ledger reconciliation template?
A general ledger reconciliation template needs enough fields that a stranger can open the sheet and understand the account without asking anyone. Put the identifying details at the top, the two balances in the middle, the reconciling items below them, and the sign-off at the foot. Everything else is optional.
Header fields
The header says what the workpaper covers. Without it, a printed copy is just numbers.
- Company or entity and account number and name, exactly as they appear on the trial balance.
- Balance date, the day the balances are struck, such as March 31.
- Period, if the account is reconciled monthly.
- Currency, when the company books in more than one.
- Source document, the name and date of the outside record, such as "Vendor statement dated April 2".
- Frequency and risk rating, so the reviewer knows what level of care to expect.
Balance block
Two lines carry the weight: the balance per general ledger and the balance per source or subledger. Under them sit the difference, the total of reconciling items, and an unexplained difference that should read zero or fall under the stated threshold. Build these as formulas, not typed numbers, so the sheet recalculates when an item changes.
Reconciling items table
Each reconciling item gets a row with these columns: item number, description, date originated, amount, type, owner, expected clear date, age in days, and a reference to the support. The type column can be as simple as "timing", "error" or "stale". It tells the reviewer at a glance what kind of item sits there.
Sign-off block
Add four cells: prepared by, date prepared, reviewed by, and date reviewed. Preparer and reviewer should be different people. Add a notes cell for anything unusual, such as a change of source or a decision to leave a small difference unexplained.
For the bank version of this layout, with its own column set for outstanding checks and deposits in transit, see which columns a bank reconciliation template needs. This guide covers the version that works for any other balance sheet account.
Tying the general ledger balance to the subledger or source
Tying the balance means showing, on one sheet, that the general ledger figure and the supporting figure agree once reconciling items are taken into account. The sheet does this with a short block of arithmetic that a reviewer can read from top to bottom without clicking into any cell.
A subledger is the detailed record that sits behind a control account in the general ledger. The accounts receivable control account, for example, is supported by an invoice-level subledger. A source is an outside document, such as a lender statement. The template treats both the same way: one side is the ledger, the other is the support.
The tie-out in four lines
- Start with the balance per general ledger, taken from the trial balance on the balance date.
- Add or subtract reconciling items that belong on the ledger side, such as an entry not yet recorded in the books.
- The result is the adjusted ledger balance.
- Compare it with the balance per source, adjusted for any items that belong on the source side. The two adjusted figures should match.
Keep each reconciling item on only one side. An item that is a timing difference in the source, such as a payment in transit, adjusts the source balance. An item that is a missing entry in the books adjusts the ledger balance, and usually needs a journal entry to correct it. Showing which side each item sits on is how the reader knows whether a correction is required.
Linking the numbers
Use cell references so the support stays connected. The ledger balance cell can point to a pasted trial balance tab. The source balance cell can point to the total of an imported schedule. If a figure is typed in by hand, mark it with a comment saying where it came from. A reviewer who sees a hand-typed number with no source reference has little reason to trust it.
Listing and aging reconciling items on the GL template
Reconciling items belong in a table with an origin date, and the template should calculate each item's age from that date to the balance date. Aging makes problems visible. A row that is 12 days old is routine, and a row that is 140 days old is a question someone must answer.
Calculating age
Add a column for age in days. In Excel, subtract the origin date from the balance date: if the balance date is in cell B2 and the item's date is in E10, the formula in the age column is =$B$2-E10. Then add a second column that assigns a band, such as "under 30", "30 to 90" or "over 90". A simple IF formula does this. Conditional formatting that shades the over 90 band makes the stale items stand out when the sheet is printed.
What each band means
- Under 30 days: normal for timing items. No note needed beyond the description.
- 30 to 90 days: needs a short note on what is holding the item open.
- Over 90 days: needs a decision this month. Correct the books, write the item off with approval, or obtain the missing document.
Carry the list forward, but never carry an item forward silently. When last month's item is still open, keep its original date so the age keeps growing. A common mistake is to re-enter an old item with a new date, which resets the clock and hides the problem. The reviewer's checklist for exactly this issue is in what a balance sheet reconciliation reviewer should check.
A filled example for accrued liabilities
The example below shows an accrued liabilities account at March 31. All figures are made up. The general ledger balance is 31,200.00. The support schedule, built from contracts and later invoices, totals 29,300.00: a contractor accrual of 12,500.00, an accrued bonus of 15,000.00 and unbilled legal fees of 1,800.00.
| Line | Amount |
|---|---|
| Balance per general ledger, March 31 | 31,200.00 |
| Less: reconciling items (below) | (1,900.00) |
| Adjusted ledger balance | 29,300.00 |
| Balance per support schedule | 29,300.00 |
| Unexplained difference | 0.00 |
| # | Description | Originated | Amount | Type | Age (days) | Action |
|---|---|---|---|---|---|---|
| 1 | January utility accrual; bill received and paid in February, accrual never reversed | Jan 31 | 640.00 | Stale | 59 | Reverse in April |
| 2 | Payroll accrual posted twice | Mar 19 | 1,100.00 | Error | 12 | Correcting entry |
| 3 | Invoice booked to payables March 30; accrual not yet reversed | Mar 30 | 160.00 | Timing | 1 | Clears at next close |
| Total | 1,900.00 |
Three items, three different kinds. The stale accrual is the one the aging column catches: at 59 days it needs a note now, and a decision once it passes 90 days if it is still not reversed. The duplicate payroll accrual is an error that needs a correcting journal entry. The third item is a normal timing difference that will clear on its own.
How do you reconcile the general ledger when the subledger is wrong?
When the subledger is wrong, fix the side that holds the error, and prove which side that is before posting anything. Do not adjust the general ledger to match a subledger you have not checked. Start by testing both against an outside source, then correct the incorrect record with a documented entry.
This situation is common. A subledger of vendor bills can carry a duplicate invoice. A fixed asset register can list an asset that was sold. The general ledger may be right, and forcing it to agree with the subledger would spread the error.
A sequence for deciding which side is wrong
- Find the difference. Compare the two totals and list the transactions that explain it.
- Trace each item to an outside document. An invoice, a statement or a contract tells you which record is correct.
- Correct the wrong side. If the subledger is wrong, fix it at the transaction level, for example by voiding the duplicate bill. If the general ledger is wrong, post a journal entry with a clear description.
- Rerun the tie-out. The two should now agree without a plug.
- Document the fix. Note the cause on the workpaper, so a reviewer sees both the error and its correction.
When a manual journal entry caused the gap
A frequent cause is a manual journal entry posted directly to a control account. The ledger moves, and the subledger does not. The usual repair is to reverse the manual entry and post the underlying transaction through the subledger so both sides update. If the entry was right and the subledger should hold the detail, add the missing transaction there. Either way, the reconciling item should name the journal entry number.
Whose job is the correction?
The preparer finds the cause and proposes the fix. Someone with authority approves it, especially when it touches closed periods. If the books for a prior month are locked, record the correction in the current period and document why, rather than reopening the old one without approval.
GL reconciliation template mistakes: unsupported plugs and copied months
Reviewers reject two mistakes on sight: unsupported plugs and copied months. A plug is an amount entered only to make the difference zero. A copied month is a workpaper rolled forward with the dates changed and the checking skipped. Both make the sheet look finished without proving anything.
Unsupported plugs
A plug often appears under a label such as "other", "adjustment" or "difference". It has no origin date, no document and no owner. The test is simple: could a reviewer trace this amount to a transaction? If the answer is no, it is a plug. Replace it with the real items, or leave the difference unexplained and escalate it. An honest unexplained 40.00 is better than a hidden one.
Copied months
Rolling a template forward is fine. Rolling the numbers forward without testing them is not. Signs of a copied month include a source balance that matches last month's, reconciling items with unchanged ages, and a statement date that has not moved. Build the template so that the dates and source balances must be re-entered each period, and clear the sign-off cells when you copy the sheet.
Other mistakes worth checking
- Hard-coded totals instead of formulas, so the sheet shows agreement when the items no longer add up.
- Items on the wrong side, which makes the adjusted balances look equal when the books still need an entry.
- Mixed dates, such as a balance date of March 31 with a source document dated March 28.
- Missing sign-off, or a reviewer who is also the preparer.
Other templates in the series
Some accounts need a lighter form. A petty cash float is counted rather than matched, and the petty cash reconciliation form is built for that. A credit card account has a long transaction list, and the guide to reconciling credit card statements in Excel lays out a worksheet for it.
For statement-backed accounts, PreciseBankFlow can check that each statement foots and match its lines to your ledger, so the source balance you enter on this template has been tested. It is in early access, and the template, the judgment and the sign-off remain yours.