• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Excel Bank Reconciliation Formula

You are here: Home / Excel Formulas / Excel Bank Reconciliation Formula
Excel Bank Reconciliation Formula
June 25, 2013 by Mynda Treacy

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 🙂

Enter your email address below to download the sample workbook.

By submitting your email address you agree that we can email you our Excel newsletter.
Please enter a valid email address.

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!

Excel Bank Reconciliation Formula

More Excel Formulas Posts

top excel functions for data analysts

Top Excel Functions for Data Analysts

Must know Excel Functions for Data Analysts and what functions you don’t have to waste time learning and why.
excel advanced formula environment

Excel Advanced Formula Environment

Excel Advanced Formula Environment is a long awaited, new improved way to write, name and store Excel formulas.
Pro Excel Formula Writing Tips

Pro Excel Formula Writing Tips

Must know Excel formula writing tips, tricks and tools to make you an Excel formula ninja, including a new formula editor.
excel shaping arrays

New Array Shaping Excel Functions

The Excel Shaping Array Functions makes it easier than ever to reshape arrays and ranges using these purpose built functions
excel nested if functions what not to do

Excel IF Formulas and What Not To Do

Excel IF formulas can get out of hand when you nest too many IFs. Not only do they become unwieldy they’re difficult for anyone to understand
excel image function

Excel IMAGE Function

The Excel IMAGE Function enables you to embed images in a cell using a formula. It supports BMP, JPG/JPEG, GIF, TIFF, PNG, ICO, and WEBP files

Excel VSTACK and HSTACK Functions

New Excel VSTACK and HSTACK functions makes combining arrays of cells easy and with some clever tricks we can extend their capabilities.
identify overlapping dates and times in excel

Identify overlapping dates and times in Excel

How to identify overlapping dates and times in Excel with a formula that checks a range of cells. Works with Dates and Times.
New Excel Text Functions

TEXTSPLIT, TEXTBEFORE and TEXTAFTER Functions

TEXTAFTER, TEXTBEFORE and TEXTSPLIT are exciting new Excel Text functions. They’re fairly self-explanatory, however TEXTSPLIT has some cool features.

Top 10 Intermediate Excel Functions

Take your Excel skills to the next level with this top 10 intermediate Excel functions. These are must know functions for all Excel users.
Category: Excel Formulas
Previous Post:Excel Formulas to Summarise Monthly Data into QuartersExcel Formulas to Summarise Monthly Data into Quarters
Next Post:Excel ACCRINT FunctionExcel ACCRINT Function

Reader Interactions

Comments

  1. Damian

    March 11, 2021 at 3:20 pm

    Awesome, thanks for this!

    Reply
    • Mynda Treacy

      March 11, 2021 at 3:46 pm

      Our pleasure!

      Reply
  2. Srinivas

    May 31, 2020 at 2:45 am

    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

    Reply
    • Mynda Treacy

      May 31, 2020 at 9:55 am

      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

      Reply
  3. Mike Tracy

    May 2, 2020 at 6:04 pm

    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.

    Reply
  4. Luis G Polanco

    March 22, 2020 at 4:21 pm

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

    Reply
    • Mynda Treacy

      March 23, 2020 at 8:57 am

      Glad I could help, Luis 🙂

      Reply
  5. Mohamed Zohri

    September 28, 2019 at 6:06 pm

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

    Reply
    • Catalin Bombea

      September 29, 2019 at 1:42 pm

      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.

      Reply
  6. sumit

    September 27, 2019 at 11:35 pm

    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

    Reply
    • Catalin Bombea

      September 29, 2019 at 1:51 pm

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

      Reply
  7. Bill K

    August 27, 2019 at 1:17 am

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

    Reply
    • Mynda Treacy

      August 27, 2019 at 8:33 am

      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

      Reply
  8. Bharat

    July 30, 2019 at 1:44 am

    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.

    Reply
    • Catalin Bombea

      July 30, 2019 at 5:09 am

      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)

      Reply
  9. Shiva

    July 17, 2019 at 1:43 am

    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

    Reply
    • Mynda Treacy

      July 17, 2019 at 8:23 am

      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

      Reply
  10. Nethra

    February 26, 2019 at 10:32 pm

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

    Reply
    • Mynda Treacy

      February 27, 2019 at 6:57 am

      Hi Nethra,

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

      Mynda

      Reply
  11. Frederik

    February 5, 2018 at 7:46 pm

    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

    Reply
    • Catalin Bombea

      February 6, 2018 at 2:32 pm

      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

      Reply
  12. Amber

    February 2, 2018 at 2:37 am

    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!

    Reply
    • Catalin Bombea

      February 2, 2018 at 2:11 pm

      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

      Reply
      • Amber Batchelder

        February 13, 2018 at 9:43 am

        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!

        Reply
        • Catalin Bombea

          February 13, 2018 at 4:39 pm

          Hi Amber,
          Looks like you already have a solution on forum 🙂
          Regards,
          Catalin

          Reply
  13. Hello

    January 16, 2018 at 11:42 am

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

    Reply
    • Catalin Bombea

      January 16, 2018 at 2:02 pm

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

      Reply
  14. Mohammad Chowdhary

    December 30, 2017 at 7:13 pm

    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.

    Reply
    • Mynda Treacy

      December 31, 2017 at 3:54 pm

      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

      Reply
  15. ashok verma

    April 28, 2017 at 10:50 pm

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

    Reply
    • Catalin Bombea

      April 29, 2017 at 2:15 am

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

    February 19, 2017 at 11:26 pm

    Nice work

    Reply
  17. Hassab Siddiq

    January 17, 2017 at 5:27 pm

    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
    • Mynda Treacy

      January 18, 2017 at 1:56 pm

      Glad I could help, Hassab 🙂

      Reply
  18. jay

    January 11, 2017 at 9:48 pm

    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
    • Catalin Bombea

      January 12, 2017 at 3:27 pm

      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

      Reply
  19. D Buck

    June 24, 2016 at 5:05 am

    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
    • Catalin Bombea

      June 24, 2016 at 2:37 pm

      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

      Reply
  20. muhammad muzammal

    June 16, 2016 at 8:30 pm

    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
    • Mynda Treacy

      June 17, 2016 at 12:24 pm

      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
  21. Matthew Fulton

    April 13, 2016 at 12:31 am

    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
    • Catalin Bombea

      April 13, 2016 at 5:17 am

      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
  22. Rahul Gupta

    December 2, 2015 at 9:51 pm

    Wonderful. best formula for a reconciliation. Thanks

    Reply
    • Mynda Treacy

      December 2, 2015 at 9:54 pm

      Cheers, Rahul. Glad we could help.

      Mynda

      Reply
  23. ramasamy

    September 29, 2015 at 10:00 pm

    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
    • Philip Treacy

      September 29, 2015 at 10:06 pm

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

      Regards,

      Phil

      Reply
  24. Vinod

    July 20, 2015 at 11:16 pm

    Need an help in reconciliation

    Reply
    • Mynda Treacy

      July 22, 2015 at 1:08 pm

      Hi Vinod,

      Do you have a specific question we can help with?

      Mynda

      Reply
  25. Amanda

    July 7, 2015 at 5:06 am

    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
    • Mynda Treacy

      July 7, 2015 at 9:01 am

      Glad it was helpful, Amanda 🙂

      Reply
  26. curston

    November 18, 2014 at 4:24 pm

    Hi, Thank you for this!

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

    Reply
    • Mynda Treacy

      November 19, 2014 at 11:04 am

      Hi Curston,

      Move them into one column 🙂

      That’s the easiest solution.

      Kind regards,

      Mynda

      Reply
  27. Danny

    September 11, 2013 at 4:20 pm

    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
    • Mynda Treacy

      September 12, 2013 at 9:22 am

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

        August 27, 2015 at 2:35 pm

        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
        • Catalin Bombea

          August 29, 2015 at 2:24 pm

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

            August 31, 2015 at 7:03 am

            =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

            August 31, 2015 at 2:16 pm

            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

  28. emmanuel

    September 11, 2013 at 8:32 am

    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
    • Mynda Treacy

      September 11, 2013 at 9:28 am

      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.

      Reply
      • emmanuel

        September 12, 2013 at 9:07 am

        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
        • Mynda Treacy

          September 12, 2013 at 9:24 am

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

            September 12, 2013 at 9:42 pm

            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

            September 12, 2013 at 10:01 pm

            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

            September 13, 2013 at 2:26 am

            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

            September 13, 2013 at 8:33 am

            Will do, Emmanuel 🙂

          • Richard Cabrera

            May 17, 2014 at 5:02 am

            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

            May 18, 2014 at 9:17 am

            Hi Richard,

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

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

            Kind regards,

            Mynda.

  29. Kuldeep Kumar

    August 19, 2013 at 3:09 am

    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
    • Mynda Treacy

      August 19, 2013 at 7:50 pm

      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
      • Kuldeep Kumar

        August 20, 2013 at 2:14 am

        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
        • Mynda Treacy

          August 20, 2013 at 8:37 am

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

            August 23, 2013 at 1:52 am

            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

            August 23, 2013 at 10:49 am

            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

          March 22, 2020 at 4:41 pm

          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,

          Reply
  30. Catpol

    August 4, 2013 at 5:18 pm

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

    Reply
    • Mynda Treacy

      August 4, 2013 at 7:18 pm

      Thanks for sharing, Catpol.

      Reply
  31. KV

    June 29, 2013 at 11:12 pm

    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
    • Philip Treacy

      July 2, 2013 at 12:45 pm

      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

      Reply
  32. KV

    June 29, 2013 at 7:50 pm

    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
    • Mynda Treacy

      June 30, 2013 at 10:00 am

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

        July 2, 2013 at 3:16 am

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

    June 26, 2013 at 9:57 am

    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
    • Mynda Treacy

      June 26, 2013 at 11:23 am

      Cheers, Neale. Great tip 🙂

      Reply
  34. sonu

    June 26, 2013 at 3:50 am

    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
    • Mynda Treacy

      June 26, 2013 at 12:54 pm

      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.

      Reply

Leave a Reply Cancel reply

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

Current ye@r *

Leave this field empty

Sidebar

More results...

Shopping Cart

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

Guides and Resources

  • Excel Keyboard Shortcuts
  • Excel Functions
  • Excel Formulas
  • Excel Custom Number Formatting
  • ALT Codes
  • Pivot Tables
  • VLOOKUP
  • VBA
  • Excel Userforms
  • Free Downloads

239 Excel Keyboard Shortcuts

Download Free PDF

Free Webinars

Excel Dashboards Webinar

Watch our free webinars and learn to create Interactive Dashboard Reports in Excel or Power BI

Click Here to Watch Now
  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
 
  • About My Online Training Hub
  • Contact
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Copyright © 2023 · My Online Training Hub · All Rights Reserved

Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.

Download A Free Copy of 100 Excel Tips & Tricks

excel tips and tricks ebook

We respect your privacy. We won’t spam you.

x