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 🙂

Enter your email address below to download the sample workbook.

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!

Waqas

Thank you so much. You are a life saver.

Mynda Treacy

Glad we could help!

Damian

Awesome, thanks for this!

Mynda Treacy

Our pleasure!

Srinivas

Hi, the formula was helpful. than you

One concern is if the debit and credit amount is same example 1000 debit and 1000 credit, the formula concerts to same sings and matches.

So is there any alternative solution

Mynda Treacy

Hi Srinivas, I’m not sure what the issue is as that’s the idea of the formula. Perhaps you can post your question on our Excel forum where you can share a sample Excel file with your data and desired result. From there we can help you further. Mynda

Mike Tracy

Hi,

This post was so helpful.

My approach is to search internet and find best solution for bank reconciliation. I will use this formula in my reconciliation sheet.

I also find another website which I got some ideas for bank reconciliation.

https://armarecon.com/user-manual/

hope to help others for their bank reconciliation.

I relay appreciate you for your valuable post.

Luis G Polanco

Hi I am Luis, your approach help in the sense that it speeds up to find the amounts missing on the Bank Statement side and on the CheckBook side also.. Thank you so much..

Mynda Treacy

Glad I could help, Luis 🙂

Mohamed Zohri

how can I use Excel to reconcile my ledger bank account by matching it with the bank statement for the same period?

Catalin Bombea

Hi Mohamed,

Without seeing your files, the only answer I can give is already in this article you just read above.

You can try to upload on our forum sample files with your source data, so we can see how it looks, we will try to help.

sumit

ACCOUNT NUMBER Add Amount Deduct Amount Total

24 8 0

24 40 0

24 0 3.25

24 0 2

24 0 1.5

24 0 2

24 0 1.5

34 0 5

77 8 0

106 10 0

120 30 0

120 0 1.5

144 1 0

144 20 0

144 1 0

144 1 0

144 1 0

144 1 0

144 0 1.5

144 0 1.5

165 10 0

165 0 4

165 0 1.25

165 0 5

Sir how we will calculate the total front of every entry

if unique id comes again and again with different credit and debit

Please help me for this.

With Regards

Sumit

Catalin Bombea

Is it just an aggregation you need, like this?

ACCOUNT Add Deduct

24 48 10.25

34 0 5

77 8 0

106 10 0

120 30 1.5

144 25 3

165 10 10.25

You can use simple formulas:

=SUMIF([ACCOUNT],[@ACCOUNT],[Add Amount])

Bill K

Does anyone know if something similar can be done if you are using Power Query?

Mynda Treacy

Hi Bill,

You’d need to number the records first, then split them into two tables of records; one for those numbered 1 and one for those numbered 2. Then compare the two lists with Power Query.

If you get stuck, please post your question on our Excel Forum.

Mynda

Bharat

I have applied the above formula in my sheet but this formula fails to knockoff the transactions because of multiple currency used. Example One Cr 40k-1 PLD and one Cr 40k-2 EUR, however I have one Dr value 40k in EUR but it didnt show as matched due to currency issue. Please help.

Catalin Bombea

Hi,

You will have to use our forum to upload a sample file, hard to imagine your data. (create a new topic after sign-in)

Shiva

How can I reconcile using excel to match of the debits and credits , but here I am talking about 7000 lines , and I need to identify the debit and credit for same customer , this is first situation.

Secondly I need to identify the debit and credit with using the B/L number.

Can you please assist me .

Please see below sample.

Customer No Customer Name B/L No Invoice No Amount

1 Rock Enterprize 12345678 123 45

2 Sand Enterprize 32145 321 -45

3 Beast 12345678 258 -45

4 Rock Enterprize 12345678 741 -45

Mynda Treacy

Hi Shiva,

You need to use a formula similar to this VLOOKUP and Match Multiple Criteria. If you get stuck, please post your question on our Excel Forum where you can upload a sample Excel file and we can help you further.

Mynda

Nethra

My requirement is same as given by Frederik. I want to know if there is formula to match one debit with multiple credits.

Mynda Treacy

Hi Nethra,

The answer is the same as the one Catalin gave to Frederick.

Mynda

Frederik

Thank you very much.

However, what to do if a credit transaction is a sum sum of more than one debit transactions?

Like this for example:

500

200

300

-1000

Catalin Bombea

Hi Frederik,

Without a unique identifier that ties the credit transactions to that specific debit transactions, it’s almost impossible, you have to dig through all transactions, combine the credit values until one debit transaction matches. And that is most likely a source of errors. Of course, depends on your data and how the information is structured.

Catalin

Amber

Hello!

This formula has been extremely helpful. However, I am wondering if I can add a condition to the formula. I need to match debits and credits which this formula helps to do, but the amounts that match off need to be associated with the same contract number. So the condition in my situation would be a contract number.

Any help would be appreciated!

Catalin Bombea

Hi Amber,

Can you upload a sample data sheet so we can see your data structure? Use our forum to upload the file (create a new topic)

Without seeing your data, it’s hard to imagine a solution, thank you for understanding.

Catalin

Amber Batchelder

Hello,

I uploaded a very simplified version of my data in the forum under General Excel Questions and Answers titled “Excel Bank Reconciliation Formula- with condition.”

Thanks!

Catalin Bombea

Hi Amber,

Looks like you already have a solution on forum 🙂

Regards,

Catalin

Hello

I have to look the matches in two different columns. How can I do the same thing? Thank you in advance.

Catalin Bombea

Hi,

Can you provide a sample file? You can upload it to our forum: excel-forum (Create a new topic after signing-up)

Catalin

Mohammad Chowdhary

Hi there, would you help me to learn or where to learn about transactions reconciliation between two big transactions. I have an interview coming up on this and am struggling to find enough information. Its nothing specifically in youtube either. Please help. Will really appreciate it.

Mynda Treacy

Hi Mohammad,

Learning how to reconcile accounts is an accounting topic, as opposed to it being strictly Excel related. I’d do some Google searches on ‘how to reconcile accounts’ or similar.

Mynda

ashok verma

I want check bank reconciliation statement please provide formula in excel sheet

Catalin Bombea

Hi Ashok,

There is a downloadable file in this article (here is the link again), so I don’t understand what you mean, please be more specific.

Mahebubkhan

Nice work

Hassab Siddiq

Dear

I follow your tutorial articles which are very helpful for me

I applied this formula to my bank reconciliation and it wirked perfectly

A wonderful performance saving a lot of time

Regards

Mynda Treacy

Glad I could help, Hassab 🙂

jay

Thank you so much for sharing. I do run into a problem while using the formular. Sometimes, it could not match a pair of numbers. I can see both the same number like two 100-1 with no match mark made. Do you have any suggestions about where to fix? thank you so much

Catalin Bombea

Hi Jay,

You can open a new ticket on our support system. Make sure you upload a sample file, so we can see a sample of your data, and the error.

Regards,

Catalin

D Buck

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

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: https://www.myonlinetraininghub.com/excel-forum

Catalin

muhammad muzammal

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

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

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

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

Wonderful. best formula for a reconciliation. Thanks

Mynda Treacy

Cheers, Rahul. Glad we could help.

Mynda

ramasamy

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

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

Regards,

Phil

Vinod

Need an help in reconciliation

Mynda Treacy

Hi Vinod,

Do you have a specific question we can help with?

Mynda

Amanda

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

Glad it was helpful, Amanda 🙂

curston

Hi, Thank you for this!

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

Mynda Treacy

Hi Curston,

Move them into one column 🙂

That’s the easiest solution.

Kind regards,

Mynda

Danny

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

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

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

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

=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

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

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

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

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

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

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

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

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

Will do, Emmanuel 🙂

Richard Cabrera

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

Hi Richard,

You could try Solver for this

Kind regards,

Mynda.

Kuldeep Kumar

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

Hi Kuldeep,

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

Kind regards,

Mynda.

Kuldeep Kumar

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

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

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

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.

Luis G Polanco

Hello, I am Luis, regarding Kuldepp Consult of transactions of different funds, I solved this situation by creating a Shortcut (numeric) nickname for each fund.. then I linked this numeric shortcut in a table which I used for the Bank Recon.. This way I could filter each fund transaction and determine which was Paid or Deposited in the month when Reconciling..And as Mynda, suggested at the End of the Recon, you must make an Individual report for each Fund to show the details for each one, Best Regards,

Catpol

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

You may finish your reconciliation before your morning coffee break!

Mynda Treacy

Thanks for sharing, Catpol.

KV

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

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

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

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

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

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

Cheers, Neale. Great tip 🙂

sonu

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

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.