Key takeaways
- Different totals often come from different definitions, dates, filters or stages of a process.
- Reconcile at summary level first, then drill into records and transformations.
- A trusted reconciliation needs owners, evidence and a documented resolution.
- Do not force systems to agree until you understand why their numbers differ.
Why business systems show different numbers
A CRM may count opportunities, finance may count invoiced revenue and Excel may contain a manually adjusted management view. All three numbers can be internally correct while answering different questions.
Differences also appear when systems refresh at different times, use different time zones, exclude cancelled records or apply separate rules for refunds, taxes and duplicates. The first step is to describe what each number actually represents.
Related guide: Data Provenance: How to Prove Where Every Business Number Came From →
Define the number before comparing it
Write down the metric name, population, time period, status rules, currency, timezone and exclusions for each report. A phrase such as monthly sales is not precise enough if one report uses invoice date and another uses payment date.
Agree on a canonical definition for important KPIs, but keep source-specific measures when they serve different operational purposes. A reconciliation should explain the relationship rather than hide useful differences.
- Which records are included?
- Which date controls the reporting period?
- Are taxes, refunds or discounts included?
- Which status values count as complete?
- Are duplicate or test records excluded?
- Which currency and timezone are used?
Reconcile totals before individual records
Begin with row counts, distinct IDs, subtotals by date or category and key totals. A large difference in one day or status often reveals the cause faster than opening records one by one.
Separate expected differences from unexplained differences. For example, a finance total may be lower than CRM pipeline value because only invoiced records are included. Document that relationship before searching for an error.
Related guide: How to Check a Large Dataset Without Reviewing Every Row →
Drill into records and transformation rules
After the summary comparison, match records using stable IDs, invoice numbers, customer IDs or another documented key. Identify records present in one system but missing in another, and compare fields that affect the calculation.
Review the transformations between source and report: deduplication, status mapping, currency conversion, date parsing and manual adjustments. A small mapping difference repeated across thousands of rows can explain a large total gap.
- Create matched, source-only and destination-only groups.
- Compare status, date, amount and identifier fields.
- Inspect duplicates and records with missing keys.
- Review filters, joins and calculated columns.
- Keep a sample of evidence for each difference type.
Choose a resolution and assign ownership
The resolution may be a corrected source record, an updated metric definition, a mapping change or a documented timing difference. Do not change a report simply to make it match another system if the underlying purpose is different.
Assign one owner for the source, one for the transformation and one for the business decision when those responsibilities differ. Record the decision, effective date and affected reports so the same debate does not return next month.
Prevent recurring reconciliation problems
Turn the most important comparisons into repeatable checks. Schedule a reconciliation after each refresh, show the variance and threshold, and route unexplained differences to an owner before publishing the report.
Maintain shared metric definitions and a source-to-report map. Over time, the reconciliation process becomes an early warning system for broken integrations, delayed files and unexpected business changes.
- Set acceptable variance thresholds.
- Compare row counts and key totals automatically.
- Show report freshness and source dates.
- Alert an owner when a threshold is exceeded.
- Review recurring causes and improve the upstream process.
Frequently asked questions
Why does my CRM revenue not match finance revenue?
The systems may use different dates, statuses, currencies, refund rules or record populations. Compare definitions and timing before assuming one system is wrong.
Should CRM or finance be the source of truth?
It depends on the metric. Finance may own booked revenue, while CRM may own pipeline activity. Define the source of truth for each business question.
How do I reconcile two Excel reports?
Standardize column names and types, match stable identifiers, compare totals and exceptions, then document the filters and transformations used by each report.
How often should reports be reconciled?
Reconcile whenever the data is refreshed or before an important decision. High-impact reports should have automated variance checks and an owner.
Can reconciliation be automated?
Yes. Rules can compare counts, totals, identifiers, dates and statuses, then produce an exception list for the cases that need human review.