How to Reconcile in Excel
Reconcile records in Excel by standardizing keys, matching amounts, checking dates, flagging differences, and reviewing exceptions.
Reconciling in Excel means comparing two sets of records and finding what matches, what is missing, and what differs. The records might be bank transactions vs an expense tracker, invoices vs payments, inventory counts vs purchases, or schedule hours vs payroll.
The process is simple: clean both lists, create matching keys, compare the records, and review the exceptions.
Prepare both tables
Step 1. Put each source in a table
Use one row per transaction or record. Keep headers clear and remove subtotals or blank separator rows.
Step 2. Standardize formats
Dates should be real dates, amounts should be numbers, and names should be cleaned enough to compare.
Step 3. Create a matching key
A key can combine date, amount, vendor, invoice number, employee, or other fields. For example:
=TEXT(A2,"yyyymmdd")&"|"&TEXT(B2,"0.00")&"|"&C2This creates a repeatable comparison value for each row.
Match records with COUNTIF or XLOOKUP
To check whether a key from Table A exists in Table B:
=IF(COUNTIF(TableBKeys, A2)>0, "Matched", "Missing")To pull details from the matching record:
=XLOOKUP(A2, TableBKeys, TableBStatus, "Not found")After that, filter for "Missing", "Not found", duplicate keys, or amount differences.
Example: reconcile clinic expenses
A clinic might compare card transactions against its clinic expense tracker. The goal is to catch missing receipts, uncategorized supply purchases, duplicate entries, and transactions that never made it into the tracker.
Create a key from date, amount, and vendor. Then flag unmatched card transactions and review them before month end.
TIP
Common reconciliation problems
| Problem | Cause | Fix |
|---|---|---|
| Same transaction appears twice | Duplicate import or manual entry | Flag duplicate keys before matching |
| Amount differs slightly | Fees, refunds, tax, or partial payments | Add a tolerance review column |
| Dates are off by one or two days | Bank posting delay | Use a date-window check for close matches |
| Vendor names do not match | Different naming conventions | Clean vendor names before comparison |
The Griddy way
Reconciliation is repetitive because every source formats records a little differently.
"Reconcile this bank export against my clinic expense tracker, match by date and amount, flag missing rows, duplicates, and transactions without receipts."
Griddy can standardize the records, create matching checks, and produce a clean exceptions view for review.
Skip the manual work
Describe it. Griddy does it.
Instead of writing this formula yourself, just tell Griddy what you need in plain English. Works in Excel and Google Sheets.
Use this on real templates
Reconcile tracker rows before month-end review
Reconciliation is easier when expense and invoice templates preserve dates, amounts, vendors, receipt status, and payment status consistently.
Expense Tracker for Clinics
Track clinic supplies, billing costs, software, payroll support, repairs, vendors, and receipts in one free expense spreadsheet.
Open templateFinanceInvoice Template for Clinics
Create clinic invoices for private-pay services, packages, consultations, products, balances, and client billing in one spreadsheet.
Open templateFinanceExpense Tracker
Log every expense, track receipts, and generate category summaries. Free template for personal or business use.
Open templateFinanceInvoice Template
Professional invoice template with automatic subtotal, tax, and total calculations. Customise with your logo and send in minutes.
Open template