Once you’ve completed your first round of bank reconciliations in your accounting software and the balances still don’t match, it can be frustrating to trace missing or unmatched transactions: https://it-ebs.co.uk/news/oh-solve-problem-like-bank-rec/
Most systems only let you tick off items that match, without showing clearly what remains outstanding. Exporting your data to Excel gives you more flexibility and powerful tools to pinpoint discrepancies quickly.
If you make supplier payments via BACS or another electronic method, ensure the payment reference matches the description used in your accounting system. This makes it easier to match transactions—especially when multiple entries share the same value. Likewise, encourage your customers to use consistent references when paying invoices so you can allocate receipts accurately.
Start by downloading your bank statement in CSV or Excel format from your online banking portal. From your accounting system, export the corresponding bank transactions in the same way. Combine both sets of data side by side in one worksheet and sort them by date so similar transactions align closely.
Next, recreate the running balance using a simple formula such as (Previous Balance + Paid In – Paid Out). This helps you compare your reconstructed totals against the official bank balances, highlighting where differences begin to emerge. Use a variance or plus/minus formula to measure gaps between the two sources.
To narrow down issues, sum the debit and credit columns separately to identify whether discrepancies fall on the income or expense side. If you notice a specific amount difference, use Excel’s Find function (Ctrl + F) to locate matching values instantly.
Instead of the traditional tick method, use color-coding to mark transactions that match. Conditional formatting (Insert hyperlink Conditional Formatting in Excel – Step by Step Tutorial) can make this process even easier—apply different colours or gradients for income and expenses, or use a rule to highlight cells within a specific value range (for example, between 25 and 40). You can also highlight groups of cells to check subtotals quickly.
Once reconciled, clean up any leftover transactions that are not legitimate outstanding items. Lingering unreconciled entries can create major issues down the line, even if the system reports the account as balanced.
These Excel techniques act as a visual roadmap to identify and resolve variances efficiently. The real adjustments still need to be processed within your accounting software, but this approach makes it far easier to isolate what needs attention and get your accounts fully aligned.
For more practical examples and step-by-step Excel formulas, visit the resources or downloads section: https://it-ebs.co.uk/downloads/)