Blog · Data quality and reconciliation · Finance and FP&A teams
Financial data reconciliation matches records across financial sources and explains every difference. Follow a subledger-to-ledger example, spreadsheet formulas and a closing control that proves the totals.
Financial data reconciliation matches financial records between sources and explains why their totals differ. Use the same entity, account, period and sign convention, then prove that the individual differences add to the original gap. Equal grand totals alone do not prove that the underlying records agree.
Start with a bounded question: does one US company's September 2026 invoice register agree with revenue account 4100 in its general ledger? Keep other entities, accounts and periods outside that comparison, and record those exclusions beside the workbook.
Use posting period for the ledger and the corresponding accounting period for the register. Keep document date separately. An invoice dated September 30 but posted in October needs an explanation; changing its date to make the totals match hides the issue.
Export entity, account, period, document ID, transaction currency, signed amount and source row reference. Keep debit and credit columns if that is how the ledger exports. State the conversion rule: this example shows revenue credits as positive and credit memos as negative in both files.
Keep the original exports unchanged. The IRS recordkeeping guidance explains that purchases, sales and other business transactions generate supporting documents used to record the books. Those documents are also the evidence needed to resolve a financial mismatch.
Create sheets named Source, Ledger and Recon. On the first two sheets, use columns A through E for entity, account, period, document ID and signed amount. Put the match key in F2:
=A2&"|"&B2&"|"&C2&"|"&D2
Fill it down. Require the same text conventions in both exports: preserve leading zeros, remove accidental spaces and use the same period labels. Check that the separator does not occur inside an identifier.
An invoice may have several source lines and several ledger postings. Aggregate those legitimate lines by key rather than matching each source row to every ledger row. A repeated key is a review signal; it is not proof of a duplicate. Use transaction or posting IDs to investigate repeated records before excluding anything.
For the underlying concept, see the roll-up identity and reconciliation. A detailed total must still equal its control total after grouping.
Copy the keys from both scoped exports into column A of Recon and remove duplicate keys from that combined list. Including both sources matters: a list built only from invoices will miss ledger-only journals.
In Recon, use B for source amount, C for ledger amount, D for difference, E for source row count and F for ledger row count. Enter these formulas in row 2 and fill down:
B2 =SUMIFS(Source!$E$2:$E$1000,Source!$F$2:$F$1000,$A2)
C2 =SUMIFS(Ledger!$E$2:$E$1000,Ledger!$F$2:$F$1000,$A2)
D2 =B2-C2
E2 =COUNTIF(Source!$F$2:$F$1000,$A2)
F2 =COUNTIF(Ledger!$F$2:$F$1000,$A2)
The labels before each equals sign identify the destination cell; paste the expression beginning with =. Extend the ranges to cover every exported row. Microsoft documents SUMIFS as summing values that meet criteria, and COUNTIF counts cells meeting a criterion. Covirage is not affiliated with Microsoft.
A zero amount is different from an absent record. If E is positive and F is zero, the key is source-only even if its signed amount happens to net to zero. Investigate that status separately from the amount difference.
This illustrative register and ledger contain the following grouped records. All amounts are USD, and every amount is exact to whole dollars. A zero in an absent-source position represents the sum returned by the formula, not evidence that a record exists.
| Document | Register (USD) | Ledger (USD) | Register minus ledger (USD) | Finding |
|---|---|---|---|---|
| INV-4101 | 12,000 | 12,000 | 0 | Matched |
| INV-4102 | 18,000 | 18,000 | 0 | Matched |
| CM-4103 | -2,000 | -2,000 | 0 | Matched credit memo |
| INV-4104 | 7,500 | 0 | 7,500 | Posted in October |
| INV-4105 | 9,600 | 9,000 | 600 | Different booked amount |
| INV-4106 | 4,200 | 0 | 4,200 | Posted to account 4200 |
| JRN-ADJ | 0 | -350 | 350 | Ledger-only adjustment |
| Total | 49,300 | 36,650 | 12,650 | All groups included |
First, tie the register's $49,300 and the ledger's $36,650 independently to their export control totals. Then prove the bridge:
Register total = ledger total + timing difference + valuation difference + account mapping difference + ledger-only adjustment reversal.
49,300 = 36,650 + 7,500 + 600 + 4,200 + 350.
The adjustment reversal is positive because the ledger-only journal reduced revenue by $350. Its sign is determined by the comparison, not by the exception label.
With the seven document groups in rows 2 through 8, this check must return zero:
=SUM(B2:B8)-SUM(C2:C8)-SUM(D2:D8)
This proves the arithmetic is complete. It does not approve the accounting. Inspect the October posting, the amount discrepancy, the account mapping and the journal's authorization. Keep an owner and evidence reference against every open item, and distinguish explained items from corrected items.
For sales-system specifics, use the CRM-to-ledger reconciliation method. Run broader financial data integrity checks before matching, and use financial outlier analysis after reconciliation to investigate unusual but correctly posted values.
Bring the scoped subledger and ledger exports, including identifiers, periods and signed amounts, in a file or Google Sheet. Covirage maps the columns; its tools compute and check the comparison, and the external AI model explains the result with every figure traced to its rows. Create a free Covirage account, which takes about a minute, then set up your business and bring a sample of your own data.
See how this supports finance teams.
Validation checks whether a file follows expected rules, such as valid dates and populated account codes. Reconciliation compares that file with another financial source. A file can pass every validation rule while still missing a transaction that appears in the ledger.
Yes. Offset errors can leave the totals equal while individual records are wrong. Reconcile both the total and the record groups, and retain unmatched records even when their net difference is zero. Review the gross exceptions before approving the result.
No. A timing label describes the cause, not the accounting decision. Check the posting date, transaction evidence and closing policy. The controller decides whether a difference requires a journal, belongs in a later period or remains an open reconciling item.
Accounting usually owns the final reconciliation and approval. Billing, procurement, payroll or another source owner supplies the missing evidence and corrects source errors. Give each open item a named owner, expected resolution date and reference to the supporting document.