The Month-End Close Checklist in Excel
Every accountant who has closed the books in Excel knows the failure mode: it’s day 4, the balance sheet is off by $1,217.43, and nobody remembers which of the six open workbooks has the right cash number. The fix isn’t working harder — it’s working in the same order every month, with the same checks, in workbooks built to be checked. This is the close checklist I’ve refined over years of monthly closes, organized in the sequence the work actually has to happen.
Print it, adapt it, and run it the same way every month. Consistency is the entire point.
Before You Touch a Number: Workbook Setup
A close that starts in a messy workbook ends in a messy reconciliation. Spend the first 20 minutes of day 1 on hygiene.
1. Create this month’s close workbook from last month’s — by copy, never by overwrite. Save a new file named with a sortable date convention: Close_2026-05_v01.xlsx. Year-month-first names sort correctly in any folder; MayClose_final_FINAL2.xlsx does not. Increment the version number (v02, v03) at every meaningful milestone — after the bank rec ties, after JEs post, after review. When something breaks on day 4, you can roll back to the last known-good version instead of untangling it live.
2. Kill stale external links immediately. Workbooks copied month over month accumulate links to prior-period files, and those links silently pull old numbers. Run through finding and breaking external links before any new data comes in, not after the tie-out fails.
3. Refresh your data dumps into dedicated input tabs. Trial balance, bank activity, AR/AP aging, payroll register — each export gets its own tab, prefixed IN_ (e.g., IN_TB, IN_Bank). Paste exports as values, never formulas, and never type into an input tab. The rule: input tabs are read-only after import. Every calculation lives on separate work tabs that reference the inputs.
4. Convert each dump to a Table (Ctrl+T) and name it. Tables auto-expand when next month’s export has more rows, which means your SUMIFS and lookups don’t silently truncate. Named ranges for key single cells — CloseDate, PriorTB_Total — make formulas auditable at a glance.
5. Freeze your header rows on any tab longer than a screen. It sounds trivial until you’re reconciling row 1,400 of bank activity and can’t see which column is which. Freezing panes takes five seconds.
The Reconciliation Sequence
Order matters. Cash first, because everything downstream assumes cash is right. Then the balance sheet accounts that feed the P&L.
6. Bank reconciliation
Match the bank export against the GL cash activity. The workhorse here is matching on amount and date with XLOOKUP:
=XLOOKUP(B2, GL_Cash[Amount], GL_Cash[Ref], "UNMATCHED")Anything returning UNMATCHED goes on the outstanding items list. For one-to-many situations (one deposit, several GL entries), aggregate with SUMIFS by date and counterparty before matching. Before you start, check for duplicate transactions in the bank export — duplicated rows in a CSV download are a classic source of phantom differences.
The reconciliation isn’t done when the difference is small. It’s done when:
=Bank_Ending - GL_Ending - SUM(Outstanding[Amount]) = 0A rec that’s “only off by $40” is a rec that’s wrong.
7. Balance sheet account recs
For each material balance sheet account — AR, AP, prepaids, accruals, fixed assets, payroll liabilities — tie the subledger to the GL control account. The fastest structural check is comparing the two datasets with a lookup: every subledger line should find its GL counterpart and vice versa. Run the comparison both directions; one-directional checks miss items that exist only on the other side.
Build each rec to the same template:
| Line | Source |
|---|---|
| GL balance per trial balance | IN_TB |
| Subledger / supporting detail total | IN_ tab |
| Reconciling items (listed individually, with explanation) | Manual |
| Unexplained difference | Formula — must be 0 |
That last row is a formula, never a typed zero. Wrap lookups in IFERROR only where a miss is genuinely expected — wrapping everything in IFERROR is how errors hide until the auditors find them.
8. Roll-forwards
Prepaids, fixed assets, and accruals get a roll-forward: beginning balance + additions − amortization/payments = ending balance, and the ending balance must equal the GL. Beginning balance should be a cell reference to last month’s close file’s ending balance — pasted as a value with the source noted in a cell comment, so this month’s file stands alone.
Summarize and Tie Out
9. Pivot the trial balance
Once recs are clean and adjusting JEs are posted, re-export the TB and build your summaries. A pivot table off the TB with account category in rows and current/prior month in columns gets you a comparative balance sheet and P&L in about two minutes — and unlike a hand-built summary, it can’t drop an account.
Two non-negotiable pivot checks:
- Grand total of the TB pivot = 0. Debits equal credits or you’ve imported a partial export.
- Pivot P&L net income = balance sheet current-year earnings movement. If these disagree, an entry hit the wrong side of the ledger.
10. Variance analysis
Compare actuals against both prior month and budget. Build a variance tab with columns: Account, Actual, Budget, $ Var, % Var. Flag anything exceeding your threshold — a common rule is the greater of 5% and a fixed dollar floor:
=IF(AND(ABS(D2)>5000, ABS(E2)>0.05), "REVIEW", "")Apply conditional formatting to the flag column so review items are visible from across the room. Then — and this is the part that separates a close from a data dump — write one sentence of explanation next to every flagged line. “Rent up $8K — annual escalation per lease, effective May 1.” If you can’t write the sentence, the variance isn’t explained, and unexplained variances are how misstatements survive to year-end.
11. The three-way tie-out
Before anything goes to management, verify that three things agree to the penny: the GL system, your close workbook, and the reporting package. Build a TIEOUT tab with one row per statement total and a difference column that must show all zeros. Hardcode nothing on this tab — every figure is a cell reference. This tab is the last thing you check and the first thing a reviewer should open.
Lock It Down and Document It
12. Version control the final file
Save the reviewed file as Close_2026-05_FINAL.xlsx and stop editing it — any post-close adjustment becomes next month’s problem or a properly labeled FINAL_revised with a change note. If the workbook circulates, protect the formula cells while leaving input cells open, so a reviewer can’t fat-finger a SUMIFS while scrolling. On files saved to OneDrive or SharePoint, the Show Changes feature (Review tab, Microsoft 365) gives you a record of who touched what during review.
13. Documentation that survives you
Every close workbook needs a README tab, written for the version of you that gets hit by a bus (or just goes on vacation):
- Source of each
IN_tab (system, report name, run date, who ran it) - Order of operations — which tabs feed which
- Open items carried to next month, with owner and expected resolution date
- Sign-offs: preparer, reviewer, dates
This takes ten minutes and turns a personal spreadsheet into an institutional process.
The One-Page Checklist
| # | Step | Done when |
|---|---|---|
| 1 | New versioned workbook from prior month | File saved as Close_YYYY-MM_v01 |
| 2 | External links broken | Edit Links shows none |
| 3 | Data dumps into IN_ tabs as values | All sources dated |
| 4 | Tables and named ranges set | Formulas reference Tables |
| 5 | Bank rec | Proof formula = 0 |
| 6 | Balance sheet recs | All “unexplained difference” rows = 0 |
| 7 | Roll-forwards tie to GL | Ending = TB |
| 8 | TB pivot summaries | Pivot grand total = 0; NI ties to BS |
| 9 | Variances flagged and explained | One sentence per flag |
| 10 | Three-way tie-out | All differences = 0 |
| 11 | FINAL saved, formulas protected | File locked |
| 12 | README updated, sign-offs recorded | Reviewer initials in file |
The first month you run this takes longer than your current close. The third month is faster, and by the sixth, the close stops producing surprises — which is the only metric that matters.
FAQ
How long should a month-end close in Excel take?
For a small-to-mid-size entity with clean inputs, 3 to 5 business days is a reasonable target. If you’re consistently past day 7, the bottleneck is almost always upstream — late data dumps or recs that start from scratch each month — not Excel itself. The template-and-roll approach in steps 1–4 typically cuts a day off by itself.
Should I reconcile in Excel or in my accounting system?
Use the accounting system’s built-in bank rec module if it works for your bank feed — it handles matching state better than a spreadsheet. Excel earns its place for accounts the system doesn’t reconcile natively (accruals, prepaids, intercompany) and for the analytical layer: pivots, variances, and tie-outs across sources.
What’s the single most common cause of a close workbook breaking month over month?
Ranges that don’t expand. A SUMIFS pointed at A2:A500 works until the month the export has 540 rows, and the miss is silent. Converting every input to an Excel Table (Ctrl+T) and referencing Table columns eliminates the entire failure class.
Hardcoded values keep sneaking into my formulas. How do I find them?
Select the work tab, press Ctrl+G, choose Special, then Constants with only Numbers checked. Excel highlights every typed number. On a calculation tab, each one should either become a formula or move to a clearly labeled input cell.
Do I really need versioned saves if the file is on OneDrive or SharePoint with version history?
Platform version history is a safety net, not a process. Explicit v01/v02 saves at defined milestones tell you which version was the good one — “the file as of the moment the bank rec tied” — without spelunking through 40 autosave timestamps. Use both.
