When one system books amounts as negatives and the other as positives
Two tables can agree on every transaction and still fail every comparison in Google Sheets™, because one of them books money the opposite way round. A credit note that reduces a balance in one system can appear as a negative amount in one export and a positive one in the other; a payment can be negative in a cash book and positive in a bank statement, or the reverse. The transactions match. The signs do not.
Why this happens
There is no universal convention for which sign means money in. A ledger might book a receipt as positive and a payment as negative, so a customer's refund (a payment) is negative there, while a bank statement shows that same refund as a negative because money left the account, or positive because it is being read as a credit to the customer, depending on which side of the statement it is read from. Credit notes are the sharpest example: a credit note is a reduction, and different systems represent "a reduction" with different signs depending on whether they are thinking about it from the seller's side or the buyer's.
Comparing the two numbers directly does not just fail to match — it reports a difference
of twice the transaction's value, because one system's +50 and the
other's −50 are 100 apart, not 0.
Fixing it column by column
The fix is not a single "reverse this whole table" setting, because usually only one column needs it — the amount — while the key and any date columns compare normally. In Two-File Reconciliation, each column you choose to compare has its own rule, set from a dropdown under that column in the mapping step: number, date, exact text or similar text. Choosing number for an amount column reveals two more controls next to it:
- A tolerance (labelled "Tolerance, either way"), the amount either side of exact the two values are still allowed to differ by. It defaults to a penny, enough to absorb rounding, and can be widened or set to none.
- A sign flip checkbox: with it ticked, the column agrees when one side is the exact negative of the other, rather than requiring the two numbers to be identical. It is described in the sidebar as: "Agree when one side is the negative of the other: credits booked negative in one system only."
Tolerance and sign flip apply together, so an amount that is the negative of the other side within a penny still agrees. Only the amount column needs the sign flip; a key or reference column compared as exact or similar text is unaffected.
Working out which columns need it
Reconcile a handful of rows you already know should match, with sign flip off, and look at the differences the add-on reports. If every difference is close to exactly double the true amount, that is the signature of a sign disagreement, and ticking sign flip on that column should collapse those rows back to agreeing. If only some rows show that pattern — credit notes, say, but not ordinary payments — the disagreement is specific to a transaction type rather than the whole column, and is worth resolving in the source data or by splitting the reconciliation rather than flipping the sign for everything.
The shortcut
Two-File Reconciliation is a Google Sheets™ add-on that matches two tables on a key and lets you set exactly this kind of rule per column: a tolerance and a sign flip for amounts, a matching window for dates, and exact or similar-text comparison for references. It reports what agrees, what differs, and what is on one side only as new tabs, and never touches your source tabs. Free up to 1,000 rows a side.
Install: in Google Sheets™, Extensions › Add-ons › Get add-ons, search for “Two-File Reconciliation”. The guide walks through a first run. Using Excel? The Excel add-in does the same.
Google, Google Sheets™ and Google Workspace™ are trademarks of Google LLC. Microsoft and Excel are trademarks of the Microsoft group of companies. Two-File Reconciliation is not affiliated with or endorsed by Google or Microsoft.