How to reconcile transactions to a target value in Excel

You have a list of transactions. You know the total they should add up to. But finding which specific ones reconcile to that target is a harder problem than it looks.

This guide walks through why, and how to solve it.

The problem

Say you’ve received an invoice for £3,240 and you have fifteen bank receipts that you believe relate to it. Collectively those receipts total £5,800 — too high. Your job is to find the subset that adds up to exactly £3,240.

With two or three transactions, you’d just try combinations manually. With fifteen, the number of possible combinations is 32,767. That’s not a job for trial and error.

Why Excel doesn’t help natively

Excel has no built-in function for this. SOLVER can be coerced into approaching it, but it’s slow, requires the Analysis ToolPak, and doesn’t find all solutions — only one. For regular reconciliation work, it’s not a practical tool.

Most accountants fall back to one of two approaches:

• Manual trial and error — sort the transactions, try the likely candidates, repeat until it works or you give up
• Helper column logic — build increasingly elaborate IF/SUMIF combinations that still require human iteration

Both work. Both take time that compounds across every month-end.

How the Reconciler works

The tech+bash Reconciler is an app inside the Excel Add-in task pane. It takes two inputs:

1. A cell range — the transactions you want to search through
2. A target cell — the value you’re trying to match

Hit submit and it searches every possible subset of your range for combinations that sum to the target. Matching entries are highlighted in yellow on the sheet. If more than one combination works, it tells you how many options exist and lets you cycle through them one at a time.

It finds up to ten reconciling subsets per run and works with ranges of up to 20 numbers.

Step-by-step walkthrough

1. Open the task pane

Click the tech+bash button in the Excel ribbon to open the task pane. Select Reconciler from the Apps section.

2. Set your transaction range

You can type the range directly (e.g. E4:E19) or select the cells on the sheet and click the range picker button to populate the field automatically.

3. Set your target value

Select the cell containing your target — the invoice total, the expected bank balance, or whatever figure you’re reconciling to.

4. Submit

Click Submit. The inputs lock while the Reconciler runs. When it finishes, the result panel shows green if one or more solutions were found, with a count of how many options exist.

5. Cycle through options

If multiple subsets match, use the forward and back arrows to step through them. Each click re-highlights the relevant cells. Once you’ve identified the right combination, you’re done.

6. Reset or undo

Click Reset to clear the inputs and run again on a different range. If you want to remove the yellow highlights without resetting, Ctrl+Z (undo) clears them cleanly.

Tips for best results

Narrow the range first. If you have 40 transactions but can reasonably exclude half of them based on dates or amounts, do that before running the Reconciler. The 20-number limit is generous for most reconciliation tasks, but a tighter input set gives cleaner results.

Run it on the candidates, not the whole ledger. The Reconciler is designed for situations where you’ve already narrowed things down to a probable set. It confirms which specific combination is right — it’s not a general-ledger search tool.

Use it alongside Aggregate by Colour. Once the Reconciler highlights the matching transactions, you can use the Aggregate by Colour app to sum just the unmatched (unhighlighted) items — useful if you need to account for everything in the range.

When the Reconciler won’t solve it

If no combination in your range hits the target, the Reconciler will tell you. That’s useful information — it means either a transaction is missing from your range, the target value is wrong, or there’s an error somewhere upstream. Either way, you’ve ruled out the straightforward cases and can focus your investigation accordingly.

For datasets larger than 20 numbers, or for automated reconciliation at scale, take a look at our data services — we build and run reconciliation pipelines for accounting firms where the volume makes manual work impractical.