This week I had a question from Dominique that took me back to my accounting days.
She asked “how can I use Excel to reconcile a bank account by matching off the debits and credits”.
Imagine a list of amounts like this (I’ve highlighted the matches in red):
Now, you can’t (easily) sort the numbers because you want to match the negative numbers with the positive numbers so they cancel each other out.
The other challenge is you can’t simply match every instance of a number e.g. 245 is in the list 3 times, twice as a debit and once as a credit.
Excel Bank Reconciliation Formula
Here are a couple of Excel formulas we can use to get our reconciliation done before lunch.
Step 1: In column B create a unique record for every pair (i.e. a pair being a debit and a credit that add up to zero).
See how the first pair of 245’s are given the value of 245-1, and the 245 in row 10 is given 245-2.
In cell B2 we use this formula:
And then copy it down the column.
In English it reads:
If A2 is less than zero i.e. a credit, convert it to a positive number, join a dash to it, then count the instances of the value in A2 in the list A2:A2 and add this number on the end, otherwise take the value in A2, join a dash to it, then count the instances of the value in A2 in the list A2:A2 and add this number on the end.
When you copy the formula down the column the range being counted grows (thanks to absolute references) to include the next row.
Step 2: Identify the pairs in the list.
Now we have a unique list in column B we can identify which values are the same and put a mark against the matches:
To do this we used this formula:
In English says:
If the count of the value in B2 in the list B2:B10 = 2, put an x, otherwise put nothing.
Now you can sort the data based on column C and anything without an ‘x’ is not reconciled.
Off to an early lunch. Your work is done 🙂
Download the Excel workbook. Note: this is a .xlsx file, please ensure your browser doesn't change the file extension on download.
Thanks to Dominique for asking this question. I enjoyed reminiscing about my accounting days.
If you found this useful please share it with your friends and colleagues using the Google+1, LinkedIn, Facebook and Twitter buttons.
If they do any kind of reconciliations they’ll love you for this tip!