Bank reconciliation in Excel vs software: when to make the switch
8 min read

Excel is the tool almost every company starts reconciling with, and for many it is still enough. The problem starts when sources multiply: several card terminals, gateways, stores and grouped deposits. This guide explains how to do a bank reconciliation in Excel properly, when it stops being enough and what options you have when that happens.
Summarize with AI
The short answer
Excel is enough when you have one or two bank accounts, few payment methods and a monthly reconciliation that one person finishes in a few hours.
You need software when you reconcile every day, receive money through several acquirers and gateways, run several stores, or the close depends on a single person who knows how the file works.
How to do a bank reconciliation in Excel
A bank reconciliation compares what your books say with what the bank says and explains every difference. In Excel, the process that works best has five steps:
- Export both sources for the same period. The bank statement and the cash ledger from your accounting system, each on its own sheet, with date, reference, description and amount.
- Normalize the data. Use the same date format, keep amounts as numbers and strip spaces and leading zeros from references. Most matching errors start here.
- Match on reference and amount. Use XLOOKUP or VLOOKUP with a key that combines reference and amount. Mark each transaction as reconciled or pending.
- Classify what did not match. Each pending item is a reconciling item: outstanding checks, deposits in transit, unrecorded bank fees and charges, or typing errors.
- Tie out the balances. The bank balance plus and minus the reconciling items must equal the book balance. If it does not, there is still an unexplained difference.
A good template keeps data sheets separate from the result sheet, does not mix formulas with values pasted by hand and has a column for the cause of each item. That way someone else can review it.
Seven signs Excel is no longer enough
- You reconcile every day, not once a month. Rebuilding the same file every morning eats hours nobody sees.
- The bank deposits several days of sales in one transfer. One-to-one matching no longer works and you have to group by hand.
- You receive money through several acquirers and gateways. Each one settles with its own format, timing and fees.
- You need to check fees and withholdings. Comparing what was agreed with what was deducted, sale by sale, is not feasible in a spreadsheet.
- You have several stores or tills. The cash in each till goes through the cash count, the carrier and the bank before it ties out.
- Differences are found weeks later. By then it is hard to claim them from the bank or the acquirer.
- The close depends on one person. If that person is sick or leaves, nobody knows how the file works.
If three or more apply to you, staying in Excel probably already costs more than automating.
Excel vs software, criterion by criterion
| Criterion | Excel | Reconciliation software |
|---|---|---|
| Upfront cost | None, you already have it | License or service, plus initial setup |
| Time per reconciliation | Grows with every source and store | Stays stable as volume grows |
| Realistic frequency | Monthly or weekly | Daily |
| Fees and withholdings | Spot checks | Every settlement checked against the agreed rate |
| Traceability | Depends on who built the file | History of every difference, its cause and who reviewed it |
| Error risk | Broken formulas, extra pasted rows, different versions | Fixed rules applied the same way every day |
| Dependence on people | High | Low: the rules are documented |
| Flexibility | Total: change anything instantly | Changing a rule means adjusting it in the system |
Excel wins on upfront cost and flexibility, which is why it makes sense while the process is small and changes often. Software wins on everything that repeats.
What reconciling in Excel really costs
The cost of Excel does not show up on any invoice. To estimate it, multiply three numbers: the hours your team spends reconciling each day, the working days in a month and the hourly cost of those people. Then add the differences that were never found, such as overcharged fees or settlements the acquirer never paid.
A real example: a retail chain in Colombia with more than 260 stores took 6 hours for each reconciliation. Once automated, it took 10 minutes and saved about 144 hours of work a month. 99.5% of transactions are now reconciled without human intervention.
You can run the numbers for your own operation in the reconciliation impact calculator.
Which type of software to choose
Not all reconciliation software solves the same problem. There are three broad options:
- The reconciliation module in your accounting software or ERP. It matches the bank statement with your books. It usually falls short when you need to reconcile card terminals, gateways and cash before they reach the bank.
- A reconciliation platform. Tools such as Simetrik or BlackLine give your team a platform to configure and run its own reconciliations at scale. They fit banks, processors and companies with a dedicated team.
- A daily reconciliation service. Quantum defines the rules with your team, receives the files from each source and delivers every day what matched and the differences to review. It fits retail chains that want the result without operating the tool.
If you are comparing the last two, read Quantum vs Simetrik.
How to switch without risk
- Start with a single process. The one that takes the most hours or produces the most differences, for example one acquirer's deposits.
- Run in parallel for a month. Keep your Excel file and compare the results. That validates the rules with real data.
- Define tolerances. Decide which amount or date difference counts as reconciled and which one a person must review.
- Keep Excel as the output. Your team can keep receiving the result in Excel, with supporting files, without having to build it.


