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:

=IF(A2<0,-A2&"-"&COUNTIF(A$2:A2,A2),A2&"-"&COUNTIF(A$2:A2,A2))

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:

=IF(COUNTIF($B$2:$B$10,B2)=2,"x","")

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

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!

D Buck says

Thank you very much, I’ve been using this tip for some time now, and it has been very helpful. One additional question, if I want to perform this on a monthly batch of transactions but I want to evaluate each day/date independently, how could I modify the formula? I do have a separate date field, but am not sure how to look at the each date separately.

Thanks again for any help you can provide!

Dave

Catalin Bombea says

Hi Dave,

Can you upload a sample file? It will be easier for us to help. You can use our forum, just open a new topic: http://www.myonlinetraininghub.com/forum/excel

Catalin

muhammad muzammal says

i just want to reconcile the bank reconciliation statement with organizational cash book…i feel it is very difficult task to reconcile easily and quickly,so plz help me for the completion of my assignment.kindly email me your fruitful material and excel sheet for this..

Mynda Treacy says

Hi Muhammad,

I’m sorry but I don’t have any template for this. The best I can offer is the formula above. Hopefully you can adapt it to your needs.

Mynda

Matthew Fulton says

I just wanted to let you know that your post is still helping people! I have been trying to build my own Auto Reconciliation Excel Add in and this formula finally helped me map out the most challenging part. I did modify your formula a bit so I can import two different sheets and have it match by the category type as well (Checks,Deposits,Expenses)

Instead of having just the -1 added to the end I also add a letter to coincide with the category. In my example K3:K5 hold the values that [@Type] is matched against.

=IF(G4<0,-G4&"-"&COUNTIF(G$4:G4,G4),G4&"-"&COUNTIF(G$4:G4,G4))&IFS([@Type]=$K$3,"C",[@Type]=$K$4,("D"),[@Type]=$K$5,("E"))

After applying the formula to both tables independently, I used a lookup to match the rows between the two tables as well as provide me an bright red warning on unmatched transactions.

Would you have any suggestions for simplifying the formula so it will be more VBA friendly?

Thanks again

Matthew Fulton

Parkway Business Solutions

https://parkway.business

Catalin Bombea says

Instead of the last part ( IFS([@Type]=$K$3,”C”,[@Type]=$K$4,(“D”),[@Type]=$K$5,(“E”)) ), you can use a lookup table:

INDEX(Table4[Column2],MATCH([@Type],Table4[Column1],0)), where column1 has those K3, K4 and K5 values, and column 2 will hold the corresponding values: C, D and E.

It’s easier to use table names in vba than to recreate those cells references.

Cheers,

Catalin

Rahul Gupta says

Wonderful. best formula for a reconciliation. Thanks

Mynda Treacy says

Cheers, Rahul. Glad we could help.

Mynda

ramasamy says

excellent formula. Thanks for sharing this and explaining in plain english for the best understanding.

I have copied your formula and pasted it in the excel sheet and did small changes. It works fine. Fantastic.

Philip Treacy says

Thanks 🙂 Glad that we were able to help you out.

Regards,

Phil

Vinod says

Need an help in reconciliation

Mynda Treacy says

Hi Vinod,

Do you have a specific question we can help with?

Mynda

Amanda says

Thank you so much! I have been looking for a solution to this for hours. This is the best one by far! Thank you, this has saved me a ton of time! Amanda

Mynda Treacy says

Glad it was helpful, Amanda 🙂

curston says

Hi, Thank you for this!

But what if you have debits and credits in two seperate columns?

Mynda Treacy says

Hi Curston,

Move them into one column 🙂

That’s the easiest solution.

Kind regards,

Mynda

Danny says

Hi Mynda,

Thanks for sharing. It really helps.

I have a similar situation but rather than only values “amount” in column A, I have one more column , eg column C which includes another query ,eg.”date” or ” payment number”. Is there any way still applying this formula with restraint to column c eg “restriced to particular date or payment number?

Mynda Treacy says

Hi Danny,

The key to this technique is to create a unique value from the amount column. We do this by counting the instances of the same number and then apending a 1 or 2 etc. to the end.

But in your case you don’t need to append the 1 or 2 etc. as you can create a unique value by concatenating the amount in column A with the Payment Number in column C (I presume there won’t be more than one instance of the payment number i.e. it is unique to one Debit and one Credit entry only). e.g.:

Note: You don’t have to put hyphen in between each value in column A and C, but it might just make it easier to read if you do.

Once you have your unique value using the formula above, you can apply the matching formula in the same way as the example above.

I hope that helps.

Kind regards,

Mynda.

DIDO says

Hello Mynda,

Please I have a very similar situation to Victor’s but the difference is that the reference number and amount a times appears more than once. After concatenating both the reference numbers and amounts and applying the matching formula above, only one debit and credit were matched. How do I adjust the formula to match both debits and credits as many times as they appear.

I must commend your style of teaching.

Thank you.

Catalin Bombea says

Hi Dido,

Can you please upload a sample file with detailed description of what you are trying to achieve? It will be easier to work on your specific structure.

You can use our Help Desk.

Cheers,

Catalin

dido says

=IF(COUNTIF($B$2:$B$10,B2)=2,”x”,””)

I will like to amend the matching formula above such that if the count of value in B2 in the list B2:B10 is an even number put an X, if an odd number (say 2 debits and one credit) put an X for every pair of debit and credit and put nothing for the outstanding debit.

Thank you.

Catalin Bombea says

Hi Dido,

try this version:

=IF(MOD(COUNTIF(B:B,B2),2)=0,”x”,””)

MOD function will return true id the result of COUNTIF function divided by 2 has no remainder ( the result is an even number).

Cheers,

Catalin

emmanuel says

Hi Mynda, i like your way of teaching so much, it shows how much exp u have in this field with real stuff! am using excel more than 5+ yrs but still i can’t learned anything effectively…after saw your real stuff … i like n love to learn more about..it! pls suggest me how can i ask my queries if any? give me your mail id so that it will very useful for me…

Mynda Treacy says

Hi Emmanuel,

Thanks for your kind words 🙂

You can learn more from me via my courses:

Advanced Excel Course – Excel Expert

Excel Dashboard Course.

You can send me questions via the help desk.

Kind regards,

Mynda.

emmanuel says

hi, thanks a lot for your quick response! kindly update me about your new courses, materials etc., if you have any free material about report generation through macros kindly share with me please! if possible pls send me to my mail id.

Mynda Treacy says

Will do, Emmanuel. I’ve added you to our Excel newsletter email list so you will receive our free email tutorials and notices of any new courses or promotions we’re offering.

Kind regards,

Mynda.

emmanuel says

Mynda, I have a doubt in preparing the “Reports in excel by using macros” …kindly help me to learn detailed or else send me some sample files or videos?

Mynda Treacy says

Hi Emmanuel,

Unfortunately I don’t have any examples I can point you towards. We do have a VBA course in production but it’s not available yet.

I’m sorry I’m not more help.

Kind regards,

Mynda.

emmanuel says

Its ok….somehow hard for me…because i have waited for so long years to learn…i hope u will surely help me to learn once its ready or will update me as soon as possible.

Mynda Treacy says

Will do, Emmanuel 🙂

Richard Cabrera says

I would like to reconcile but with one credit matching multiple debits. Can anyone help with a formula? See below at my example. Thanks Richard

100 x

-25 x

66 xx

-2 xx

-50 x

-4 xx

-60 xx

-20 x

-5 x

Mynda Treacy says

Hi Richard,

You could try Solver for this, or there is an Excel add-in here:

http://www.evolucion.com.au/

Kind regards,

Mynda.

Kuldeep Kumar says

Hi Mynda,

You are Superb..

Thanks for sharing your knowledge.

I use Bank Reconciliation formula regularly in my office & it is very helpful.

But I face one problem when I apply this formula after filter. Would you help me out how can I apply this formula after filter where it reads only visible figures..

Waiting for your reply.

Thanks & Regards

Kuldeep Kumar

Mynda Treacy says

Hi Kuldeep,

Instead of the =IF(COUNTIF($B$2:$B$10,B2)=2,”x”,””) formula you can use this:

Kind regards,

Mynda.

Kuldeep Kumar says

Hi Mynda,

Thanks for reply to me…(-_-)

Please consider my below problem which is related to bank reconciliation:

There are three funds & every fund has five hundred (500) transactions & all transactions are mixed according to date (some are related to 1st fund & some are related to 2nd fund & so on), which I have to reconcile seperatly by using filter, By FILTER all transactions show into relevant fund because all transactions has a Fund’s head. When I use this formula: =IF(A2<0,-A2&"-"&COUNTIF(A$2:A2,A2),A2&"-"&COUNTIF(A$2:A2,A2))..this formula also reads that figures amount that is same in other funds & also hidden by filter and puts the result like 245-1, 245-2, 245-3 & 245-4….(whether it is to be in negative or positive) in increasing order by reading hidden amount also.

I want that formula, which read only that amount which fund has filtered & visible & not related to other fund. Please explain this new formula in English by using colors.

Waiting for your reply.

Thanks & Regards

Kuldeep Kumar

Mynda Treacy says

Hi Kuldeep,

If you use formulas that only pick up the unfiltered data then as soon as you remove the filters the formulas will pick up all data. This means that you can never look at all of the data in an unfiltered state at the same time, and if you inadvertently do, the formulas will give erroneous results.

Would it not be better to sort the data by fund and then separate it into a worksheet for each fund, or at least separate the data for each fund with a few blank rows?

This way the formulas already available above will work and you can get an overview of all funds at the same time. Plus you won’t run the risk of looking at unfiltered data and getting the wrong matched information.

Kind regards,

Mynda.

Kuldeep says

Hi Mynda,

Thanks for your help.

Would you please explain this formula In English by using colors with a short example of Bank reconciliation in Excel worksheet ( as you have explained Bank Reconciliation formula):

=IF(SUMPRODUCT(SUBTOTAL(3,OFFSET(B2,ROW($B$2:$B$10)-ROW(B2),0)),–($B$2:$B$10=B2))>1,”x”,””)

Thanks & Regards

Kuldeep

Mynda Treacy says

Hi Kuldeep,

It’s not easy for me to put colours into the comment fields. I recommend you watch this video on deciphering formulas. You can also read up on the functions used:

IF

SUMPRODUCT

OFFSET

And the ROW function returns the row number for the cell referenced. When used in an array formula like SUMPRODUCT it retuns an array of row numbers. So ROW(B2:B10) with return this array of numbers {2;3;4;5;6;7;8;9;10}.

I hope that’s enough to get you started.

Kind regards,

Mynda.

Catpol says

You could make transaction matching easier using Excel add-in – SumMatch.

You may finish your reconciliation before your morning coffee break!

Mynda Treacy says

Thanks for sharing, Catpol.

KV says

Hi Mynda, just a quick question:

Is there an option to subscribe (via email) to the comments of a particular post on this blog ?

Philip Treacy says

Hi KV,

The comments for any post can be received using RSS. The address for the RSS feed for any post is the post’s URL /feed

So for this post it’s http://www.myonlinetraininghub.com/excel-bank-reconciliation-formula/feed

You can receive the RSS feed using Outlook or an RSS reader like Feedly

Some browsers will recognise the RSS feeds and allow you to subscribe by clicking on the RSS icon in or around the URL bar too.

Regards

Phil

KV says

The formula in column C works only on the first and second instance of a value in column A to cancel each other out.

This means if there is a third and a fourth instance (or more), those values will not be flagged with an ‘x’.

Also, I may be wrong about this, but if we check for ABS values of the value in column A, this formula could possibly also cancel out two positive entries or two negative entries.

We may need to add one more column with a formula to ensure that only a positive and a negative number are cancelling each other out.

One other thing: if the data is sorted by column C, wouldn’t the formulas in that column be affected due to the relative references in columns B and C ?

It may be a good idea to convert the formulas in column B and C to values before sorting the table.

Or better still, just filter column C on records with an ‘x’.

Mynda Treacy says

Hi KV,

Thanks for your comments.

The formula in column B does two things:

1. It creates a unique value by appending the -1 or -2 etc. to each value. This is for the purpose of matching in column C.

2. It decides what the appended value is by by counting how many of the same values there are in the list. So if there are 3 positive 245 amounts each one will be numbered 245-1, 245-2, 245-3 and if there are only two negative 245 amounts they will be given the value of 245-1, 245-2. So although you have two 245-1 values in column B, one represents the positive and one represents the negative.

This way you won’t have a case where two positive values contra each other out, plus it handles multiple instances of the same values cancelling each other out by giving each pair (postive and negative) a new unique value in column B e.g. 245-1, 245-2.

Perhaps if you download the workbook you can test some scenarios and see how the formulas work.

In regards to pasting as values before sorting, yes you are correct, you need to do this. Or like you say, simply use filters.

Kind regards,

Mynda.

P.S. You should receive an email when new comments are posted to this blog entry.

KV says

Thanks for the clarifications Mynda 🙂

I *was* wrong after all, about the way the formula in column B works

And I haven’t received any email so far (checked my SPAM folder too).

Unless, it works only when a “new comment” is posted, and not replies to older ones ?

Neale says

Nice Excel blog

You could simplify your first formula by using the ABS function

=ABS(A2)&”-“&COUNTIF(A$2:A2,A2)

The ABS function (short for Absolute) converts all negatives to positives.

Mynda Treacy says

Cheers, Neale. Great tip 🙂

sonu says

Hi Mynda,

Thanks for sharing this trick.

It would be great if you can share one spreadsheet with one complete illustration on this topics.

Waiting for your early reply.

Thanks and Regards

CA Sonu

Mynda Treacy says

Hi Sonu,

You’re welcome. You can download the Excel workbook here.

Note: this is a .xlsx file, please ensure your browser doesn’t change the file extension on download.Kind regards,

Mynda.