Content authored by practitioners with experience at Amazon India, Intuit QuickBooks, and the Tata Group. Meet the team →
Comparing a bank extract with the books by formula every month is slow and breaks when files change size.
Load both files as Power Query queries, clean and type the key column, then merge with inner, left anti and right anti joins to produce matched, bank-only and books-only lists that refresh with new files.
Two source files with a shared key such as the UTR, three merge queries loaded to separate sheets, and a fixed file location for next month's exports.
Three refreshable lists: matched rows, bank lines not in the books, and book lines not in the bank, with counts that add back to each file's total.
Load both files into Power Query. Merge them on a shared key such as the UTR or invoice number. Expand the result. Rows found in only one file are your unmatched items. Next month, drop in the new files and click Refresh; the whole match runs again with no formulas to copy down.
Power Query is built into Excel 2016 and later, including Microsoft 365, under Data, Get and Transform Data.
Which merge type answers which question?
| Join kind in Power Query | What it keeps | Question it answers |
|---|---|---|
| Inner | Rows with the key in both files | Which lines matched? |
| Left anti | Rows in the first file with no match in the second | What is in the bank but not in the books? |
| Right anti | Rows in the second file with no match in the first | What is in the books but not in the bank? |
| Full outer | Every row from both, matched where possible | Show me everything in one table |
| Left outer | Every row from the first, with matches from the second | Add book details next to every bank line |
For a reconciliation you usually build three queries: inner, left anti and right anti.
How do I set it up, step by step?
- Load the bank extract. Data, Get Data, From File, From Workbook (or From Text/CSV). Pick the sheet and click Transform Data, not Load.
- Clean the key. Select the UTR column. Set the type to Text (Transform, Data Type, Text). Then Transform, Format, Trim, and Format, Uppercase.
- Close and load as connection only. Home, Close and Load To, choose Only Create Connection. Name the query “Bank”.
- Repeat for the books (a Tally day book or bank ledger export). Clean its key the same way. Name it “Books”.
- Merge. Data, Get Data, Combine Queries, Merge. Choose Bank on top, Books below, click the UTR column in each, and pick Join Kind: Inner. Click OK.
- Expand. In the new query, click the expand icon on the Books column and tick the amount and date fields you want.
- Repeat the merge with Left Anti and Right Anti for the two unmatched lists.
- Load each result to its own sheet: Matched, Bank only, Books only.
Each step is recorded in the Applied Steps pane on the right. That record is what makes it repeatable.
Worked example: 1,250 bank lines against 1,198 book lines
A finance executive merges September’s current account extract (1,250 lines) with the bank ledger from Tally (1,198 lines) on UTR.
| Query | Rows |
|---|---|
| Inner (matched) | 1,190 |
| Left anti (bank only) | 60 |
| Right anti (books only) | 8 |
Check: 1,190 matched plus 60 bank-only is 1,250, the full bank file. 1,190 plus 8 books-only is 1,198, the full books file. If those totals do not add back, some keys appear more than once.
The 60 bank-only lines include bank charges of ₹5,900 across 22 lines, three customer receipts worth ₹2,84,000 not yet entered in Tally, and NACH debits for an EMI of ₹46,250. The 8 books-only lines are cheques of ₹3,12,400 issued but not yet presented. Each list goes to the person who can explain it.
Why do keys fail to match, and how do I clean them inside Power Query?
| Cause | Symptom | Fix in Power Query |
|---|---|---|
| Text versus number | Merge shows no matches at all, or the preview says few rows match | Set both key columns to Text before merging |
| Leading or trailing spaces | Some obvious pairs miss | Transform, Format, Trim |
| Capital letters | ”hdfc” does not match “HDFC” | Transform, Format, Uppercase; the standard merge is case-sensitive |
| UTR buried in the narration | No UTR column in the bank file | Add Column, Extract, Text Between Delimiters, or Split Column by delimiter |
| Hidden characters | Pairs still miss after Trim | Transform, Format, Clean |
Do the cleaning in the Bank and Books queries, before the merge, so every merge uses the clean key.
When is Power Query enough, and when is it not?
Power Query is enough when:
- One clean key, such as the UTR, exists on both sides.
- Each key appears once in each file.
- Amounts are expected to match exactly.
It starts to struggle when:
- Amounts differ slightly. A merge needs an exact key. Matching ₹48,500.00 against ₹48,499.60 within a ₹1 tolerance needs a formula step afterwards.
- One payment settles many invoices. A ₹2,36,000 receipt against two invoices of ₹1,18,000 will not merge on invoice number.
- References are typed differently. Microsoft 365 offers fuzzy matching in the merge dialog, which compares text similarity. It helps with names, but it can pair the wrong references, so check every fuzzy match.
What to read next
Next, read Excel versus Python versus reconciliation software, which covers when a refreshable query stops being enough, and when manual reconciliation tops out. If you prefer formulas, see VLOOKUP, XLOOKUP or INDEX-MATCH.
TransactIG matches bank statement lines to your books and keeps what is left in an exception queue; see bank reconciliation software for India.