What is Bank Reconciliation?
Bank reconciliation is the process of matching transactions recorded in your internal company accounting ledger (or checkbook register) against the official transactions listed on your monthly bank statement. Performing regular monthly reconciliations helps catch unauthorized fees, identify uncashed customer checks, prevent accounting fraud, and ensure tax filings are accurate.
Preparing Your Financial Data in Excel
To reconcile bank statements efficiently in Microsoft Excel, you need two clean data sources side-by-side in your workbook:
- Internal Ledger Sheet: Exported cash ledger entries from your accounting records.
- Converted Bank Statement Sheet: Your official bank transactions converted from PDF to Excel using StatementPro.
Step-by-Step Bank Reconciliation Workflow
1. Standardize Dates and Transaction Amounts
Ensure both worksheets format transaction dates consistently (e.g., `YYYY-MM-DD`) and present amounts as numbers rather than text strings. In the converted bank statement sheet, maintain separate columns for Debit (Outflows) and Credit (Inflows).
2. Calculate Net Ending Balances
Verify the starting and ending balance formulas on your bank statement worksheet:
Ending Balance = Beginning Balance + SUM(Credits) - SUM(Debits)3. Use XLOOKUP / VLOOKUP to Match Transactions
Instead of manually highlighting matching rows with a yellow marker, use Excel formulas to compare transaction reference numbers or amounts between your ledger and bank sheet.
Example Excel formula to search for a bank reference ID in your internal ledger:
=XLOOKUP(A2, Ledger!A:A, Ledger!C:C, "UNMATCHED")4. Identify Common Reconciliation Variance Items
When your internal ending cash balance does not equal your bank statement ending balance, investigate the following typical variance causes:
- Outstanding Checks: Checks written to vendors that have not yet been cashed by the bank.
- Deposits in Transit: Customer payments received and recorded in your books that have not cleared bank processing by month-end.
- Bank Service Charges: Monthly maintenance fees or wire fees charged directly by the bank that have not yet been entered into your internal ledger.
- Interest Earned: Interest credited to your account by the bank.
Automate Your Monthly Reconciliation Workflow
By using StatementPro to turn PDF bank statements into structured Excel workbooks, you eliminate hours of manual data keying, allowing you to complete month-end reconciliations in minutes.