Accounting Spreadsheet Templates in Excel: Ledger, Reconciliation and Close

9/3/2026

Most accounting spreadsheets fail the same way. They work for the person who built them, in the month they were built, and then quietly stop being trustworthy: a range that does not extend to new rows, a hard-coded number pasted over a formula, a reconciliation that "balances" because someone typed the difference into a plug cell.

A good accounting spreadsheet is less about clever formulas than about structure. The data lives in one place, every total can be traced back to the rows that make it up, and anything that should equal something else is tested by a check cell rather than by eye.

This guide walks through three Excel templates that cover most of what small practices and in-house teams build by hand: a general ledger, a bank reconciliation and a month-end close tracker. For each one you get the layout, the columns, the formulas that matter and the checks that make the sheet reviewable. The examples use functions available in current Microsoft 365 versions of Excel; where an older version lacks a function, an alternative is noted.

Principles That Apply to Every Template

Before any layout, five rules separate a spreadsheet you can rely on from one you have to re-check every month.

One row per transaction, one column per attribute. The ledger is a list, not a report. Dates, accounts, descriptions and amounts go in columns, and every transaction is one row. Reports (trial balance, account summaries) are built from that list, never typed alongside it.
Use Excel Tables, not loose ranges. Convert each data block with Ctrl+T and give it a name such as tblGL. Formulas that refer to tblGL[Debit] extend automatically as rows are added. Most "the total is wrong" problems in inherited workbooks come from a SUM over A2:A500 when the data now runs to row 640.
Separate inputs, calculations and outputs. Put raw data on its own sheet, lookups and mappings on another, and reports on a third. Color input cells one way and leave formula cells alone. A reviewer should be able to tell at a glance what was typed and what was calculated.
Build check cells. Debits equal credits. The trial balance totals zero. The reconciled balance equals the book balance. Each test is a cell that returns TRUE or a difference of zero, with conditional formatting that turns it red when it fails. A spreadsheet without checks relies on the preparer noticing.
Never overwrite a formula with a value. If a number has to be adjusted, the adjustment is its own row with a description, not a paste over a total. This single habit does more for review quality than any function.

Template 1: The General Ledger

A spreadsheet general ledger is appropriate for very small entities, for side records such as a fixed-asset sub-ledger or an intercompany schedule, or for analysis of an export from accounting software. It is not a substitute for a real accounting system once transaction volume, multiple users or audit requirements appear, but it is a good way to understand what the system is doing.

The chart of accounts sheet

Start with a separate sheet, tblCOA, listing each account once:

Account No.	Account Name	Type	Normal Balance	Statement
1000	Operating Cash	Asset	Debit	Balance Sheet
1200	Accounts Receivable	Asset	Debit	Balance Sheet
2000	Accounts Payable	Liability	Credit	Balance Sheet
3000	Owner's Equity	Equity	Credit	Balance Sheet
4000	Service Revenue	Revenue	Credit	Income Statement
6100	Rent Expense	Expense	Debit	Income Statement

Number accounts in ranges by type (1000s assets, 2000s liabilities and so on) so that sorting and grouping work naturally. The "Type" and "Statement" columns are what let you build a trial balance and financial statements later without retyping anything.

The journal sheet

The ledger itself, tblGL, holds every entry:

Entry No.	Date	Account No.	Account Name	Description	Debit	Credit	Reference
1001	3-Jan	1000	Operating Cash	Owner contribution	25,000.00		DEP-0103
1001	3-Jan	3000	Owner's Equity	Owner contribution		25,000.00	DEP-0103
1002	5-Jan	6100	Rent Expense	January rent	2,400.00		CHK-101
1002	5-Jan	1000	Operating Cash	January rent		2,400.00	CHK-101

Key design choices:

Entry No. groups the lines of one journal entry. Every entry must balance on its own, not just the ledger as a whole.
Account Name is a formula, not typed. Use =XLOOKUP([@[Account No.]],tblCOA[Account No.],tblCOA[Account Name],"NOT FOUND"). In versions without XLOOKUP, INDEX and MATCH does the same job. A "NOT FOUND" result is an immediate flag that someone posted to an account that does not exist.
Use Data Validation on Account No. pointing at the chart of accounts, so a mistyped account cannot be entered at all.
Keep Debit and Credit as separate columns. A single signed Amount column is fine for analysis, but separate columns mirror how accountants read entries and make the balancing test obvious.
Reference ties every line to source documentation. A ledger without references cannot be vouched.

Balancing checks

Add a check block above or beside the table:

Ledger balances: =SUM(tblGL[Debit])-SUM(tblGL[Credit]) should be zero.
Every entry balances: add a helper column in the table, =SUMIFS([Debit],[Entry No.],[@[Entry No.]])-SUMIFS([Credit],[Entry No.],[@[Entry No.]]), and a check cell that counts non-zero results with COUNTIF. One unbalanced entry can hide inside a ledger that balances in total if another error offsets it.
No unknown accounts: =COUNTIF(tblGL[Account Name],"NOT FOUND") should be zero.

From ledger to trial balance

The trial balance sheet lists each account from the chart of accounts with formulas, not typed figures:

Debits: =SUMIFS(tblGL[Debit],tblGL[Account No.],[@[Account No.]])
Credits: =SUMIFS(tblGL[Credit],tblGL[Account No.],[@[Account No.]])
Balance: debits minus credits, with credit-normal accounts shown as negatives or flipped using the Normal Balance column

Add a date filter by including tblGL[Date],"<="&EndDate in each SUMIFS, where EndDate is a named input cell. Now the same sheet produces a trial balance at any month end. A PivotTable over tblGL is a valid alternative for exploration, but SUMIFS layouts are easier to format as a fixed report and to compare period over period.

The total of the balance column is your final check: it must be zero.

Template 2: The Bank Reconciliation

The bank reconciliation is the control most likely to be done badly in a spreadsheet, because it is easy to make a reconciliation balance and hard to make it prove anything. The design goal is that every reconciling item is listed, dated and explained, and that the two sides arrive at the same adjusted balance independently.

The layout

Use the standard two-sided structure:

Bank side

Line	Amount
Balance per bank statement, end of period	48,215.37
Add: deposits in transit	3,120.00
Less: outstanding checks	(5,904.12)
Add or less: bank errors	0.00
Adjusted bank balance	45,431.25

Book side

Line	Amount
Balance per general ledger, end of period	45,511.25
Add: interest earned not yet recorded	15.00
Less: bank service charges not yet recorded	(95.00)
Add or less: book errors	0.00
Adjusted book balance	45,431.25

Check: adjusted bank balance minus adjusted book balance equals zero.

Supporting detail

The totals on the summary must come from detail lists, never be typed:

Outstanding checks table: check number, date issued, payee, amount, and a "cleared in" column completed when the check later appears on a statement. The summary uses SUMIFS on the amount where "cleared in" is blank.
Deposits in transit table: date recorded, description, amount, and the date it cleared.
Book adjustments table: every item on the book side becomes a proposed journal entry. If the reconciliation shows a service charge not recorded, the entry belongs in the ledger, and next month it should not reappear.

Matching statement lines to book entries

For accounts with volume, put the bank statement export on one sheet and the ledger cash detail on another. A match key (often amount plus date, or check number) lets you flag each line:

On the bank sheet: =IF(COUNTIFS(Book[Amount],[@Amount],Book[Check No.],[@[Check No.]])>0,"Matched","Unmatched")
On the book sheet, the mirror formula against the bank data

Unmatched book items are your outstanding checks and deposits in transit. Unmatched bank items are book adjustments or errors. Filtering on "Unmatched" turns an hour of ticking into a few minutes of review, but the preparer still has to look at each unmatched item: identical amounts on different transactions can produce false matches.

What makes a reconciliation reviewable

Age every reconciling item. Add a days-outstanding column. A deposit "in transit" for 40 days is not in transit; it is a problem.
No plug lines. If there is an unexplained difference, it appears as "unreconciled difference" in red, not as a miscellaneous adjustment.
Tie both starting figures to source. The bank balance to the statement, the book balance to the trial balance. Record where each came from.
Sign-off cells for preparer and reviewer with dates.

Year-end reconciliations of payroll accounts follow the same logic, and our year-end payroll reconciliation checklist shows how the tie-outs extend to wage and tax filings.

Template 3: The Month-End Close Tracker

The close tracker is not an accounting record. It is a project plan that shows, on one sheet, whether the books are ready to report. Firms that close reliably in a few days almost always have one; firms that close in three weeks usually rely on memory.

The task list

Build tblClose with one row per recurring task:

Task	Area	Owner	Reviewer	Due (workday)	Status	Completed	Reviewed	Evidence
Reconcile operating cash	Cash	Preparer A	Manager	WD+2	Complete	3-Feb	4-Feb	Rec file link
Post payroll accrual	Payroll	Preparer B	Manager	WD+2	In progress
Review AP aging and accrue unbilled	Payables	Preparer B	Controller	WD+3	Not started
Depreciation entry	Fixed assets	Preparer A	Manager	WD+3	Not started
Revenue cut-off review	Revenue	Senior	Controller	WD+4	Not started
Flux analysis vs prior month	Reporting	Senior	Controller	WD+5	Not started

Design points:

Due dates as workday offsets (WD+2 meaning the second business day after month end) let the same list roll forward each month. Convert to calendar dates with =WORKDAY(MonthEnd,2,Holidays) where Holidays is a named range.
Status from a validated list (Not started, In progress, Complete, Reviewed) so counts and formatting work.
Evidence column links to the reconciliation or schedule. A task with no evidence is not complete.
Separate preparer and reviewer. The tracker should make segregation of duties visible; if the same name appears in both columns, that is a control gap worth fixing, as discussed in our guide to internal controls over financial reporting.

The dashboard row

At the top of the sheet, a few formulas tell the controller everything:

Tasks complete: =COUNTIFS(tblClose[Status],"Complete")+COUNTIFS(tblClose[Status],"Reviewed")
Tasks overdue: count of rows where the converted due date is before TODAY() and status is not Complete or Reviewed
Percent reviewed by area, using COUNTIFS on Area and Status

Conditional formatting on overdue rows turns the list into a live status board. For anything more visual, a small summary chart of tasks by status and area is enough; the goal is a one-glance answer to "can we report yet?"

The flux analysis tab

A close is not finished until someone has asked whether the numbers make sense. Add a tab that pulls this month and last month from the trial balance, calculates dollar and percent change, and flags any account where the change exceeds a threshold you set (for example, a dollar amount and a percentage together). Each flagged line gets a written explanation. This is the cheapest analytical review you can do, and it catches misposted entries that every reconciliation missed. Our article on catching financial statement errors at year-end lists the error patterns these reviews are designed to find.

Common Mistakes in Accounting Spreadsheets

Totals over fixed ranges that stop short of newly added rows
Hard-coded plugs in reconciliations or trial balances
Merged cells in data areas, which break sorting, filtering and PivotTables
Dates stored as text, usually from imports, so date filters and SUMIFS by period silently miss rows; fix them with DATEVALUE or Text to Columns
Numbers stored as text, which SUM ignores without warning
Multiple versions emailed around, so nobody knows which file is final
No documentation tab explaining sources, assumptions and the order in which sheets are updated
Rounding differences from displaying two decimals while calculating more; use ROUND in the calculation where the amounts must agree to the cent

When to Move Off the Spreadsheet

Spreadsheets are the right tool for analysis, schedules and reconciliations that support a ledger. They become the wrong tool for the ledger itself when:

More than one person needs to post at the same time
Transaction volume makes row-by-row entry the bottleneck
An audit or lender expects system controls, access logs and a locked prior period
Bank feeds, invoicing or payroll need to integrate with the books

At that point the general ledger belongs in accounting software, and Excel goes back to what it does best: the reconciliation, the close tracker, the flux analysis and the schedules that support them. Some teams also hand the repetitive matching and checklist work to automation; our post on how to automate the month-end close with AI covers where that works and where review still has to stay human.

Building the Skills Behind the Templates

Templates are only as good as the person maintaining them. The skills that make these three work are not exotic: Tables and structured references, XLOOKUP or INDEX and MATCH, SUMIFS and COUNTIFS, data validation, conditional formatting, and the discipline to build check cells. If those are not yet second nature, Essential Excel Skills is the place to start. For summarizing large ledgers, Tackle Excel Pivot Tables goes deeper on PivotTables, and High Impact Excel: Dashboard Edition covers turning a close tracker into a reporting view. The full range is on the Excel training for accountants page.

Next Step

Pick the template that matches your biggest monthly headache, build it once with Tables and check cells, and roll it forward next month instead of starting over. If the formulas above felt unfamiliar, work through Essential Excel Skills first; the ledger, reconciliation and close tracker will take an afternoon each once the fundamentals are in place.

Frequently Asked Questions

What is an accounting spreadsheet?

An accounting spreadsheet is a workbook, usually in Excel, used to record, organize or analyze financial transactions. Small entities sometimes keep their entire general ledger in one, but more often accountants use spreadsheets for supporting work: bank reconciliations, account analyses, fixed-asset and prepaid schedules, accrual calculations, close checklists and variance reviews. A good accounting spreadsheet keeps raw data in one table, builds every report from that data with formulas, and includes check cells that prove totals agree.

Can I use Excel as a general ledger?

Yes, for a very small entity or as a teaching or analysis tool. Set up a chart of accounts table, a journal table with one row per line of each entry, and a trial balance built with SUMIFS, plus checks that debits equal credits for every entry. Excel lacks the access controls, audit trail and period locking of accounting software, so once several people post entries, transaction volume grows, or a lender or auditor will rely on the books, move the ledger into a proper system.

How do I set up a bank reconciliation in Excel?

Use a two-sided layout. Start the bank side with the statement balance, add deposits in transit, subtract outstanding checks and adjust for bank errors. Start the book side with the general ledger balance and adjust for items the bank recorded that the books have not, such as fees and interest. Both sides should reach the same adjusted balance. Pull every reconciling item from a detail table with dates and references, add a check cell that must equal zero, and never use an unexplained plug.

What Excel functions do accountants use most?

The workhorses are SUMIFS and COUNTIFS for totals by account and period, XLOOKUP (or INDEX and MATCH in older versions) for pulling account names and mapping data, IF and IFERROR for checks, ROUND for amounts that must agree to the cent, WORKDAY and EOMONTH for close calendars, and TEXT and DATEVALUE for cleaning imported data. Excel Tables with structured references matter as much as any function, because they keep formulas correct as rows are added.

What should a month-end close checklist include?

At minimum: bank and credit card reconciliations, accounts receivable and payable reviews, accruals for payroll and unbilled expenses, prepaid and depreciation entries, revenue cut-off, balance sheet account reconciliations, intercompany balances where relevant, and a flux analysis comparing results to the prior period. Each task should have an owner, a reviewer, a due date expressed as a business day after month end, a status and a link to evidence. The tracker is complete only when every task is reviewed.

Are free accounting spreadsheet templates safe to use?

They can be a useful starting point, but check them before relying on them. Look for totals over fixed ranges, hidden plug cells, missing checks that debits equal credits, and formulas that were overwritten with values. Rebuild the data areas as Excel Tables, add your own check cells, and document where each input comes from. A template you understand fully is safer than a polished one you cannot audit, and downloaded files with macros should be treated with the usual caution.