Excel for Accounting: Reconciliations, Aging Reports and Checks

Excel for accounting is where the month-end close actually happens for many teams. The accounting software records the transactions, but the reconciliations, the aging reviews, the adjustment schedules and the checks that catch errors before the auditors do are usually built in a workbook.

This guide walks through the accounting tasks you do in Excel every week and the features that make each one faster and safer, with a full bank reconciliation example and an accounts receivable aging you can copy.

In this guide

• The accounting tasks Excel handles best
• Step by step: a bank reconciliation with XLOOKUP and COUNTIFS
• Build an accounts receivable aging with dates and SUMIFS
• Prepare journal entries with built-in checks
• Stop bad data with validation and conditional formatting
• Analyze the general ledger with PivotTables
• Clean software exports with Power Query
• Protect your workbooks
• Where to go from here
• FAQ

The accounting tasks Excel handles best

Accounting software is the system of record. Excel is the workbench around it. Here is where it typically earns its place, and the features that do the heavy lifting.

Task	Key Excel features	What you get
Bank reconciliation	XLOOKUP, COUNTIFS, conditional formatting	Matched and unmatched items in minutes
AR and AP aging	Date arithmetic, IFS, SUMIFS, PivotTables	Balances by age bucket and by customer or vendor
Journal entry preparation	Tables, data validation, ROUND, check cells	Upload-ready entries that always balance
Accruals and prepayments	EOMONTH, EDATE, simple schedules	Monthly amortization without manual recalculation
General ledger analysis	PivotTables, slicers, SUMIFS	Account and period views, variance review
Data preparation	Power Query	Clean, refreshable imports from the accounting system
Review and control	Sheet and workbook protection, comments, auditing tools	Files others can use without breaking

Step by step: a bank reconciliation with XLOOKUP and COUNTIFS

A bank reconciliation answers one question: which items appear on one side but not the other? Excel can do the matching for you if both lists are structured the same way.

1. Turn both lists into tables. Paste the bank statement on one sheet and the cash account ledger detail on another. Select each range and press Ctrl+T. Name them Bank and Ledger in the Table Design tab. Make sure both have Date, Reference and Amount columns, with the same sign convention (deposits positive, payments negative).
2. Match on reference first. In a new column of the Bank table, type =XLOOKUP([@Reference],Ledger[Reference],Ledger[Amount],"Not in ledger"). Each bank line now shows the ledger amount for the same check number or transfer ID, or a clear label if there is none.
3. Flag amount differences. Add a Difference column: =IF(ISNUMBER([@LedgerAmount]),[@Amount]-[@LedgerAmount],""). Anything other than zero is a keying error or a partial payment to investigate.
4. Match on amount and date when there is no reference. Card fees, interest and many direct debits have no shared ID. Use =COUNTIFS(Ledger[Amount],[@Amount],Ledger[Date],[@Date]). A result of 0 means unmatched, 1 means a clean match, and 2 or more means duplicates that need a manual look.
5. Repeat in the other direction. Run the same formulas in the Ledger table against the Bank table. Unmatched ledger items are usually outstanding checks and deposits in transit.
6. Build the summary. Start from the bank statement balance, add deposits in transit, subtract outstanding checks, and compare with the adjusted book balance after recording bank fees and interest. A check cell with =ROUND(AdjustedBank-AdjustedBook,2)=0 confirms the reconciliation.

Filter each table on “Not in ledger” or a count of 0 and you have your reconciling items list. Next month, paste the new data into the same tables and the formulas follow automatically.

Build an accounts receivable aging with dates and SUMIFS

Start from an open invoices export with Customer, Invoice, Due Date and Balance, formatted as a table named AR. Put the aging date in a single input cell, say H1, rather than using TODAY(), so the report stays reproducible at month end.

• Days past due: =$H$1-[@[Due Date]]. Excel stores dates as numbers, so subtracting them gives days.
• Bucket: =IFS([@Days]<=0,"Current",[@Days]<=30,"1-30",[@Days]<=60,"31-60",[@Days]<=90,"61-90",TRUE,"Over 90")
• Totals by bucket: =SUMIFS(AR[Balance],AR[Bucket],"31-60"), and add a customer criterion for a per-customer view: =SUMIFS(AR[Balance],AR[Customer],$A5,AR[Bucket],C$4).

Add a control: the sum of all buckets must equal the AR control account balance in the general ledger. If it doesn’t, something is missing from the export. The same structure works for accounts payable with vendor and bill due dates.

Prepare journal entries with built-in checks

Many teams prepare recurring entries, such as accruals, allocations and reclassifications, in a template before uploading them to the accounting system. A few habits make those templates safe:

• Separate debit and credit columns, and a check cell: =IF(ROUND(SUM(Debit)-SUM(Credit),2)=0,"Balanced","Out of balance").
• ROUND allocations to two decimals and push any rounding difference to one line, so the entry balances to the cent.
• Look up account names from the chart of accounts with XLOOKUP, so a mistyped account number shows an error instead of posting to the wrong place.
• Keep support next to the entry: the calculation, the source and the preparer, on the same sheet or in a linked one.

Stop bad data with validation and conditional formatting

Most spreadsheet errors in accounting are input errors. Data validation (Data > Data Validation) blocks them at the source: a drop-down list of valid account codes, dates restricted to the current period, amounts that must be positive, or a cost center chosen from a list instead of typed by hand.

Conditional formatting then makes the remaining problems jump out:

• Highlight duplicate invoice numbers with the built-in Duplicate Values rule.
• Color any reconciliation difference that isn’t zero with a formula rule such as =ROUND($F2,2)<>0.
• Shade invoices over 90 days in red in the aging report.
• Flag budget lines with a variance above a threshold you keep in an input cell.

Analyze the general ledger with PivotTables

A general ledger export can run to tens of thousands of lines. A PivotTable summarizes it in seconds: accounts in rows, periods in columns, the net amount as values. From there you can:

• Double-click any total to see the transactions behind it, which is the fastest way to answer an auditor’s “what makes up this balance?”
• Add slicers for entity, department or journal source.
• Use Show Values As to compare each month with the previous one.
• Group dates by month and quarter for a quick flux analysis.

If you want to get faster at building these views, our Excel pivot table training guide walks through date grouping, Show Values As, slicers and calculated fields with progressive exercises. Running a smaller operation? The guide to Excel for small business shows a simple cash flow tracker and invoice log.

When the same analysis needs several tables, such as the ledger, the chart of accounts and a department list, Power Pivot lets you relate them in the Data Model instead of stitching them together with lookups.

Clean accounting software exports with Power Query

Exports from accounting systems are rarely analysis-ready: report titles in the first rows, subtotals mixed with detail, amounts as text, debits and credits in separate columns. Power Query (Data > Get Data) cleans them with recorded steps you build once:

1. Remove the top rows and promote the real headers.
2. Filter out subtotal and blank lines.
3. Set data types for dates and amounts, using the correct locale if the file comes from another country.
4. Add a custom column for the net amount: Debit minus Credit.
5. Load the result as a table, then click Refresh next month.

Power Query can also combine every file in a folder, which turns twelve monthly bank exports into one reconciliation-ready table. The finance side of these tools, with NPV, scenarios and model structure, is covered in our guide to Excel for finance.

Protect your workbooks

Excel offers several layers of protection, and they do different things:

• Sheet protection (Review > Protect Sheet) locks formulas while leaving input cells editable. Unlock the input cells first in Format Cells > Protection.
• Workbook structure protection stops people from adding, deleting or renaming sheets.
• Encrypt with password (File > Info > Protect Workbook) is the option that actually prevents someone from opening the file.

Microsoft is explicit that sheet-level protection is not a security feature: it prevents accidental changes, not determined access. Use file encryption and your organization’s access controls for confidential payroll or client data.

Where to go from here

Everything above uses standard Excel features, yet many accountants only discover half of them after years on the job. If you want a reference for what the next level looks like, our advanced Excel skills checklist lists them with a quick self-test, and our comparison of Excel certificates explains how to prove those skills to an employer.

To learn them in order rather than one emergency at a time, Gnosarium’s online Excel program takes you from the fundamentals to PivotTables, what-if analysis, Power Query and Power Pivot, and prepares you for the MOS MO-210 and MO-211 exams.

Excel for accounting FAQ

What Excel skills do accountants need?

The core set is tables, SUMIFS and COUNTIFS, XLOOKUP, date functions, PivotTables, data validation and conditional formatting. Power Query is quickly becoming expected too, because it removes most of the manual cleanup of system exports.

Can Excel replace accounting software?

Not for most businesses. Accounting software enforces double entry, audit trails and access controls that a spreadsheet doesn’t. Excel works best alongside it, for reconciliations, schedules, analysis and reporting.

How do I do a bank reconciliation in Excel?

Put the bank statement and the ledger detail in two tables, match them with XLOOKUP on the reference and COUNTIFS on amount and date, then list the unmatched items as reconciling items and check that the adjusted bank and book balances agree.

Is XLOOKUP better than VLOOKUP for accounting?

Yes, in most cases. XLOOKUP defaults to an exact match, can look to the left, doesn’t break when columns are inserted and has a built-in “not found” value. It is available in Excel for Microsoft 365 and recent versions of Excel.

How can I stop people from changing formulas in my workbook?

Unlock the input cells, then protect the sheet so only those cells can be edited. Protect the workbook structure to prevent sheet changes, and use password encryption if the file contains confidential data.