Matching transactions using formulas [Accounting]

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.