How to Do Bank Reconciliation in Excel with ChatGPT
Reconcile bank transactions step by step
Provide the bank statement and cash-ledger rows to ChatGPT and use it to generate candidate matches, exceptions, and an Excel-ready reconciliation schedule. The workflow below requires one-to-one matching evidence and keeps ambiguous items unmatched for human review.
I need to reconcile a bank statement against a general-ledger cash account in Excel. Add a unique row ID to every pasted row before matching: - Bank rows: B001, B002, B003... - Ledger rows: L001, L002, L003... BANK STATEMENT Columns: Bank Row ID | Date | Reference | Description | Amount [Paste rows here] GENERAL LEDGER — CASH ACCOUNT Columns: Ledger Row ID | Date | GL Ref | Description | Amount [Paste rows here] Matching policy: - Dates are DD/MM/YYYY and must be real dates or unambiguous date text. - Amounts use negative for outflows and positive for inflows. - Exact match: same signed amount AND sufficient identifying evidence (prefer matching reference; otherwise use date + amount + description evidence). - Do NOT treat "same date but different amount" as a match by itself. - Do NOT reuse a Bank Row ID or Ledger Row ID in more than one one-to-one match. - If duplicate date+amount candidates exist, mark them "Ambiguous" unless reference/ description evidence identifies one unique counterpart. - If I want a timing window or amount tolerance, use ONLY the values I explicitly give: Date tolerance: [0 days unless I specify otherwise] Amount tolerance: [0.00 unless I specify otherwise] - Do not create one-to-many or many-to-one matches unless I explicitly ask for split/ combined transaction matching and provide the business rule. - Never invent a reference, date, description, amount, FX rate, fee, or missing row. - Keep uncertain items unmatched/ambiguous and explain why. For every proposed match, show the evidence used. Output 1 — one-to-one reconciliation table: Bank Row ID | Ledger Row ID | Bank Date | Ledger Date | Bank Ref | GL Ref | Bank Amount | Ledger Amount | Difference | Status | Match evidence Allowed Status values: Matched | Ambiguous | Unmatched (Bank only) | Unmatched (Ledger only) Output 2 — exception lists: - all Ambiguous candidates - all Bank-only rows - all Ledger-only rows Output 3 — control totals calculated from the ORIGINAL datasets, not from the matched table: - Bank source total - Ledger source total - Bank source total minus Ledger source total - number of Bank source rows - number of Ledger source rows - number of one-to-one Matched pairs Before giving the final result, check that no Bank Row ID or Ledger Row ID has been used twice. Format the tables as tab-separated text I can paste into Excel. Do not recommend posting journal entries from this output; reconciliation exceptions require review first.
Replace the bracketed sections with your real data, then paste the prompt into ChatGPT.
I ran a bank reconciliation and I'm getting unexpected results. Here's what I see: Issue: [e.g. "All items show Unmatched even though amounts are the same"] Here are 3 sample rows from my bank statement: [paste rows] Here are the corresponding rows from my general ledger: [paste rows] Please: 1. Compare the raw values and data types before assuming the cause. 2. List the plausible causes supported by these samples. 3. Give the exact Excel test/formula I can use to confirm the cause. 4. Only then give the cleaning fix. 5. Tell me how to verify that the cleaned values truly match. Do not assume a formatting issue merely because two displayed values look similar.
Include the raw cell values, signs and date types. A displayed date/amount can look identical while the underlying Excel value differs.
I need to reconcile a multi-currency bank statement against my general ledger. Reporting currency: [e.g. GBP] Approved FX basis for this reconciliation: [e.g. transaction-specific rate in column E / booked GBP amount / policy rate supplied below] Rounding tolerance permitted by policy: [e.g. £0.01, or 0 if none] BANK STATEMENT Columns: Bank Row ID | Date | Reference | Currency | Original Amount | FX Rate (if supplied) [Paste rows] GENERAL LEDGER — CASH ACCOUNT Columns: Ledger Row ID | Date | GL Ref | Currency | Original Amount | Booked Reporting-Currency Amount (if available) [Paste rows] Rules: - Prefer reconciling to the actual booked reporting-currency amount when it is supplied. - Use only the FX rate/basis I provide; do not look up, estimate, average, or invent a rate. - State the conversion direction explicitly before calculating. - Round only at the precision I specify. - Keep original-currency amount, FX rate, converted amount and ledger booked amount in separate columns. - Use one-to-one row IDs; never reuse a bank or ledger row in multiple matches. - A conversion difference is an exception to investigate, not automatically an FX gain/loss journal. - Do not post or recommend a journal entry unless I separately provide the accounting policy and ask for one. Output: Bank Row ID | Ledger Row ID | Currency | Original Bank Amount | FX Rate | Converted Bank Amount | Ledger Booked Amount | Difference | Status | Evidence Then list all ambiguous and unmatched rows separately and reconcile the source control totals.
Add a Currency column to both datasets if you have multiple currencies in one statement.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Date | Bank Ref | Bank Amount | Status |
| 2 | 01/06/2025 | TXN-4401 | −£12,500 | ? |
| 3 | 05/06/2025 | TXN-4410 | +£8,750 | ? |
| 4 | 07/06/2025 | TXN-4415 | −£1,980 | ? |
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Date | Bank Ref | Bank Amt | Ledger Amt | Difference | Status |
| 2 | 01/06/2025 | TXN-4401 | −£12,500 | −£12,500 | £0 | Matched |
| 3 | 05/06/2025 | TXN-4410 | +£8,750 | +£8,700 | +£50 | Amount exception |
| 4 | 07/06/2025 | TXN-4415 | −£1,980 | — | — | Bank only |
TXN-4401 → exact match, difference £0 TXN-4410 → same reference identifies the related entries, but amounts differ: bank £8,750 vs ledger £8,700, exception +£50 TXN-4415 → unmatched bank item, −£1,980 Do not combine the £50 amount exception and £1,980 bank-only item into one generic "net difference". They are different exception types and should be investigated separately. ✓
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Source | Date value | Amount | Type / result |
| 2 | Bank | 01/06/2025 | £500 | Excel date serial |
| 3 | Ledger | '2025-06-01 | £500 | Text |
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Source | Original | Clean Date | Amount | Status |
| 2 | Bank | 01/06/2025 | 01/06/2025 | £500 | Matched |
| 3 | Ledger | '2025-06-01 | 01/06/2025 | £500 | Matched |
If B3 contains ISO text "2025-06-01": =DATE(VALUE(LEFT(B3,4)),VALUE(MID(B3,6,2)),VALUE(RIGHT(B3,2))) This parses fixed-position ISO text (YYYY-MM-DD) into numeric year/month/day arguments and returns a real Excel date serial. Then apply DD/MM/YYYY as the display format. Formatting alone changes appearance, not the underlying data type. ✓
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Source | Currency | Amount | Status |
| 2 | Bank | USD | $1,000.00 | Unmatched |
| 3 | Ledger | GBP | £790.00 | Unmatched |
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Source | Currency | Original | GBP Amount | Status |
| 2 | Bank | USD | $1,000.00 | £790.00 | Matched |
| 3 | Ledger | GBP | £790.00 | £790.00 | Matched |
Assumed rate in G1: 1 USD = 0.79 GBP Bank conversion: $1,000 × 0.79 = £790.00 ✓ Only after applying the explicitly supplied conversion basis should £790 be compared with the GBP ledger booked amount. For real reconciliations, retain the original amount, rate and converted amount for auditability; use the transaction-specific or policy-approved basis supplied in your data and apply only an explicitly defined rounding tolerance.
Four reasons a reconciliation can produce false matches or exceptions
Run a Bank Reconciliation with ChatGPT — 5 Steps
Export both datasets to Excel or CSV
Export your bank statement and general-ledger cash account. Preserve the original exports, then create a working copy containing individual transaction rows only. Exclude running balances, subtotals and report headers from the matching dataset rather than deleting the only source copy.
Align date formats and sign conventions
Confirm both datasets use compatible underlying date values—not merely the same display format—and align sign conventions before matching. Document whether outflows are negative and inflows positive, then verify a few known transactions in Excel.
Paste both datasets into the prompt
Use the Full Reconciliation prompt above. Include headers and unique Bank/Ledger Row IDs so every proposed one-to-one match can be traced back to exactly one source row.
Review every Partial and Unmatched item
ChatGPT's output is a starting point, not a final answer. Every amount exception or unmatched item needs a root-cause review. It may reflect timing, fees, an entry error, a split/combined transaction, or another valid cause. Do not assume an unmatched bank item automatically requires a journal entry.
Paste the reconciliation table into Excel
ChatGPT formats the output as a tab-separated table ready to paste into Excel. Apply Ctrl+Shift+L to add filters, then sort by Status to group all Partial and Unmatched items at the top.
Debug mismatching rows — 4 steps
Identify the failing pattern
Notice if the mismatch affects all rows, only specific dates, or specific transaction types. This narrows down formatting or export issues.
Extract 3-5 example rows
Don't paste the entire ledger again. Grab 3 rows from the bank export and their exact corresponding rows from the ledger that should be matching.
Query ChatGPT for hidden formatting
Paste the samples into ChatGPT using the prompt above. ChatGPT can help identify likely issues such as invisible characters, trailing spaces, differing sign conventions, or text-stored dates from the samples you provide.
Apply the fix in Excel before re-running
Apply any cleaning formula in a helper column first and verify known rows before replacing source values. For fixed ISO text such as YYYY-MM-DD, a DATE/LEFT/MID/RIGHT construction avoids locale ambiguity; DATEVALUE can depend on system date settings.
Reconcile multi-currency statements — 4 steps
Ensure currency identification
Check that your bank statement specifies the original currency for each transaction, especially if multiple foreign currencies are mixed in one export.
Define your exchange rates
Use the transaction-specific or policy-approved FX basis required by your accounting process. Provide that rate/booked reporting-currency amount explicitly; do not ask ChatGPT to estimate or source a rate implicitly.
Run the multi-currency prompt
Paste your data using the prompt above. Require ChatGPT to state whether the quoted rate means reporting currency per 1 unit of foreign currency or the inverse before multiplying/dividing, and retain the rate used beside every converted amount.
Account for FX variance (Partial Matches)
Compare the converted bank amount with the ledger's booked reporting-currency amount using only the tolerance permitted by your policy. A difference may have several causes; investigate it before deciding whether any FX gain/loss or other journal entry is appropriate.