My Online Training Hub

Microsoft Office Online Training

Get our newsletter and join over 80,000
people who learn Excel with us every week
You are here: Home / Excel Formulas / Excel Bank Reconciliation Formula

Excel Bank Reconciliation Formula

by 56 Comments

Excel Bank Reconciliation Formula

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):

bank reconciliation Excel

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).

bank reconciliation Excel

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:

bank reconciliation Excel

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!

Free 100 Excel Tips & Tricks eBook – Click here to Download

Leave a Reply

Your email address will not be published. Required fields are marked *

Current ye@r *

Comments

    • 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.

      Reply

  3. 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

    Reply

  4. 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

    Reply

  5. 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

    Reply

  6. 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..

    Reply

    • 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

      Reply

  7. 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

    Reply

    • 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

      Reply

  9. 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.

    Reply

  11. 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

    Reply

  13. 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?

    Reply

    • 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.:

      =ABS(A2)&"-"&C2

      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.

      Reply

      • 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.

        Reply

        • 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

          Reply

          • =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.

          • 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

  14. 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…

    Reply

      • 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.

        Reply

        • 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.

          Reply

          • 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?

          • 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.

          • 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.

          • 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

  15. 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

    Reply

    • Hi Kuldeep,

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

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

      Kind regards,

      Mynda.

      Reply

      • 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

        Reply

        • 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.

          Reply

          • 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

          • 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.

  16. You could make transaction matching easier using Excel add-in – SumMatch.
    You may finish your reconciliation before your morning coffee break!

    Reply

  17. Hi Mynda, just a quick question:
    Is there an option to subscribe (via email) to the comments of a particular post on this blog ?

    Reply

  18. 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’.

    Reply

    • 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.

      Reply

      • 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 ?

        Reply

  19. 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.

    Reply

  20. 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

    Reply
YouTube Channel Follow us on Facebook Twitter Follow Google+ RSS Feed or Email

Blog Categories

Resources

Excel Resources

Affiliate Program

Become an Affiliate
Secured by Sucuri Rect
✕
Hang On, Don't Go Just Yet.
Excel Tips & Tricks Book

As a thank you for visiting how would you like a
10% Discount Code to use with any of my courses?

I'll also give you a copy of my eBook packed with
100 Excel Tips & Tricks.

And I'll send you my weekly Excel blog posts.

Enter your email address below to receive all of these.

Regards, Mynda (Excel MVP)

mynda treacy mvp
100% Privacy. We don't spam.
✕
SUBSCRIBE AND LEARN EXCEL
Excel Tips & Tricks Book


Learn great Excel features, tips and tricks each week from my newsletter.

Just enter your email below, and as an extra bonus, I'll send you a copy of my '100 Excel Tips & Tricks eBook' - for nothing!

Thanks,

Mynda

 



100% Privacy. We don't spam.
✕
100 FREE EXCEL TIPS & TRICKS
Excel Tips & Tricks Book


Want to learn more great Excel tips and features?

Enter your email below and I'll send you my weekly blog post.

Plus, by signing up you'll get a copy of my '100 Excel Tips & Tricks eBook' - for nothing!




 

100% Privacy. We don't spam.