All finance teams know the pain that comes with matching transaction data – your bank statement and general ledger transactions for example. Two data sources are seldom easy to compare – especially in different formats, date layouts, transactions and key account numbers, it is rarely a case of lining each up side-by-side to check transactions off.

This is where most finance staff will get out the accounting ‘Swiss Army knife’: Excel.

Transaction matching with Excel

Excel makes it easier to format, re-order and rearrange data from two different sources so that they are straightforward to compare and match. excel reconciliation, reconciling in excel, excel, spreadsheet errorFinance staff also tend to like it because it has formulas to complete calculations, convert currencies and find specific entries. However, Excel is not without its drawbacks…

Shortcomings of Excel

Just like Swiss Army knives, Excel can be a little cumbersome when it comes to certain tasks. Overlooking these drawbacks can severely affect the accuracy and reliability of your accounts. Drawbacks using Excel:

1. Different sources export in different file formats

Data from different sources that have to be compared and matched, come in many formats, or in Excel spreadsheets which sometimes also come with some included formatting. It could have negative effects for the rest of your transaction matching.

Our tip is to always export your data as a comma separated value (CSV) file. This is the most straightforward formats, which use minimal formatting, so you should run into fewer errors as you import it into Excel.

2. Excel can format data wrongly

Most figures should be formatted as numeric, but sometimes they are accidently formatted as a text string, or a specific formatting to always be rounded up or down,. Likewise, copying data from one cell to another could also lead to numbers being converted to text, or account numbers being rounded to the nearest Euro, for example. Just moving data round to make it useable could leave you with a host of formatting errors that may not be obvious at first glance. Especially if you used Excel preformatted exports for the data you would like to match.

3. Human error

Sometimes entries are missed by such an easy mistake , perhaps they were hidden below the bottom of the screen, leaving the user thinking they were all copied, or marked off. Or perhaps a slip of a finger might lead to a single digit being accidently added or removed, throwing figures into chaos. Your staff may be highly-trained experts, but these kinds of human errors always creep in, leaving you with a risk of spreadsheet errors between 0.8-1.8%, on average. That might sound small, but when your transactions total over a million euros/dollars/pounds, your monetary risk is actually quite high.

4. Roll back

Sometimes you realise a previously performed transaction match was wrong for one of the transactions you now spotted – because you have found a better match. But it’s not that easy to roll back that previous match. Which one was the other one that needs to be reverted?

Overcoming Excel shortcomings with specialist accounting tools

Finance staff are often so busy trying to get Excel to play ball that they neglect to look for a better tool for the job. After all, if you have a Swiss Army knife that can do a little bit of everything, and you’re used to it, why bother getting a specialist tool?

Firstly, because it makes transaction matching far easier. If you had to cut a piece of paper on a rare occasion then your Swiss Army knife would do the job well enough. However, if your job was to sit cutting pieces of paper on a daily basis, it would be far easier, more efficient and more accurate using a pair of sharp scissors. The same goes for transaction matching. Excel makes a good stopgap, but if you are regularly matching hundreds if not thousands of transactions then why not use a tool specifically designed for the job?

Secondly, a specialist tool allows for automation. If you know that certain accounts, invoices or statements will come in the same format each and every month, then completing the same conversion time after time becomes a waste of time. To continue the analogy above, if you were cutting the same shape each day then it would be far quicker and more effective to machine cut the shape.

Adra Match ACCOUNTS is a specialist accounting tool that allows finance teams to quickly, efficiently and accurately match thousands of accounts within minutes. The automation uses source-specific rules to convert the data, then automates the matching of account entries – this means no human error, no formatting issues and a single output file format.

See how ACCOUNTS could work for your accounts department, instead of having Excel work against it.

Connect with Adra Match on Google+