Imagine you are the head of Accounts Receivable department at a large company. Drab, I know, But humor me and imagine.
Now, every month you get a transaction report like this:
And you want to know which numbers are matching up.
i.e, if your company gave Vendor-0002 $872.34 on 1st of April, 2014 and your received below payments from them subsequently,
- $427.77 on 1st April
- $152.88 on 2nd April
- $291.69 on 2nd April
Then you consider the account matched since the total received is same as total payable.(427.77 + 152.88 + 291.69 = 872.34).
Manually identifying all such matching transactions can be tedious, boring & error-prone.
This is where formulas come handy.
Using simple Excel formulas, we can identify all matching transactions, highlight them using conditional formatting so that you can chase the vendors with an outstanding.
Note: thanks to Kirstin whose email question prompted me to write this article.
Using formulas to match up (reconcile) accounting transactions
Step 1: Lets take a look at the data
This is how our AR (Accounts Receivable) data looks above (very first image in this post).
For the sake of simplicity I have set up this data as an Excel table.
Step 2: Write the formula
Here is the criteria for matching.
- If the total amounts (paid & received) corresponding to a vendor is zero, we consider it matched.
- Else not.
Add an extra column to the table and write this formula.
=ROUND(SUMIFS([Amount],[Vendor Reference],[@[Vendor Reference]]),2)=0
What this formula does:
It gives TRUE if a particular vendor’s amounts to total to ZERO
else FALSE
How it works?
The SUMIFS formula sums up all the numbers for the vendor name in current row [@[Vendor Reference]]
The ROUND formula rounds it to 2 digit precision. We need to use this because of a floating point error in Excel (that returns extremely small values when the result should be zero).
Related: How to use structural references in Excel
Step 3: Fill down the formula
Fortunately, you don’t have to do this step. Excel automatically fills the formula down as we are using tables. Yay!
Step 4: Highlight matched rows using conditional formatting.
Make a note of the column where TRUE / FALSE values are calculated. In my set-up, it is column E.
Select the entire table. Go to conditional formatting > new rule from home ribbon.
Set up a rule like this:
Note the mixed reference style $E4. This ensures that for highlighting all columns, Excel checks only column E.
That is all. You have now matched all the paid-up transactions. Time to flex your muscles, put-up your legs on the desk and call those other people who did not pay yet.
Download example workbook
Click here to download example workbook & practice. Examine formulas & conditional formatting to learn more.
How do you reconcile / match-up transactions?
I will be honest. I have never been the head of accounts receivable department. Even in my own household, I do not handle receivables. My wife takes care of that. I handle payables (ie credit card bills, shopping expenses and other such things).
But I often use formulas to reconcile my bank statement.
What about you? Do you use formulas to match transactions. What techniques you rely on? Please share your tips & ideas using comments.
Bonus homework
Assuming we have data like above, what formula answers the question:
How many vendors have their transactions matched?
Post your formulas in comments.
23 Responses to “Learn Top 10 Excel Features”
What it looks like if excel without formula?? 🙂
It would be not excel it would just be fancy tables in which you could just use power point. (Chandoo) would Access be an alternative?
Awesome piece of work!!!
Great article.
Chandoo - my biggest interest in the article was the awesome word-graphic at the top - where did you go to get it done into a shape?
@Rich.. thank you. I used http://www.tagxedo.com/ to generate this word cloud. I took all the comments in the original post, pasted them in tagxedo website and set up the shape etc.
Awesome Chandoo.. You need always needs coffee to start up with. BTW , how did u created the Heart Shaped picture filled with High Repetitive text in it .. Please put it on your Next blog ...
Chandoo, good article. I’ve added a link to it from Connexion – our collection of the most useful and interesting spreadsheet-related articles from the web. See http://www.i-nth.com/resources/connexion
Hi,
Just one small question. Where the hell have been I in the past for not discovering this website sooner?
I've lost a job interview recently where even though I had the subject knowledge, I was not upto their mark in Excel.
Thank you for all the free tips, guidance and for creating this forum environment.
[PS: I've just been through the site for the 1st time, and have signed up for the newsletter. You can expect pretty stupid questions from me soon]
Hy Chandoo, you always inspire me with to explore something new in excel. This data structure table is only for excel 2007 or compatible to 2010. I recently installed latest excel version 2013 in my System and experience problems regarding operating according to previous one. I'm waiting your article relates to that excel version.
Thanks
Awesome article Mr. Chandoo and that is a awesome heart shaped pic you created. Great tips as well.
[...] Learn Top 10 Excel Features | Chandoo.org – Learn Microsoft Excel Online. [...]
Chandoo is awesome..
Thanks, i got better, And i always get 90.50 in my grade card but now i get 96.50 i improved because of the tutorials you gave, Thank You Very Much Chandoo Guy.
Hi chandoo, i am intersted in seeing the video or step by step done procedure of analysing the comments and presenting in the data percentage steps. I think this one would be first step in finding out how generally happens data calculation. Thank you.
As well i would like to know how to get that black shape art of your face which i see in chandoo. I am interested in making it for me.
Nice to see the features considered by Excel users to be most useful. It might be a good idea to also analyze StackOverflow Excel questions to see what keywords appear most often.
Here are my top 10 Excel Features (for advanced users):
http://www.analystcave.com/excel-10-top-excel-features/
Thanks a ton for this it totally helped with my homework ????
Very good effort
Thank you for this. Lots of learning in the links you've provided for this septuagenarian.
Pls send me new post
Dude, your humor ? ?
Loved your work.
Hello Sir,
I am Sanjeev Khakre and i from Indore City, India , I am your big follower and i have watch your videos and learnt a lots of excel trick or function and many more . thanks so much for all of your excellent support.
Your excel knowledge is real awesome.
Thanks
Sanjeev
Your work is excellent but pls willing to know more details about the features of microsoft excel
Chandoo Would Access be a better alternative than VB?