How to find why Excel and CSV totals do not match
When spreadsheet totals disagree, compare population, grain, filters, types, signs, units, joins, and rounding in that order. Reconcile from source to accepted rows to transformation output, keeping each exclusion and adjustment visible. Re-entering formulas before locating the first divergence usually hides the cause.
The essential checks
- Confirm date range, population, metric, sign, currency, and rounding basis.
- Compare row counts and totals by source, period, and segment before the grand total.
- Measure exclusions, duplicates, join multiplication, conversion failures, and formula ranges.
- Show source total plus documented adjustments equals reported total or an explicit remainder.
Where errors enter the process
Two reports can both use SUM and still disagree because one excludes hidden rows, treats text numbers as zero, includes duplicate joins, or uses a different date boundary and currency basis.
Evidence worth tracing
- The row count changed after a merge or filter.
- Visible values are omitted from a sum because they are text.
- Subtotals agree in some months or branches but not others.
Choose the approach that matches the evidence and repetition
| Approach | When it works | What to verify |
|---|---|---|
| Recalculate SUM | Obvious range mistakes | Filters, types, and definitions |
| Pivot by segment | Localizing period or category gaps | Source grain and hidden exclusions |
| Control-total bridge | Material report reconciliation | Every adjustment has evidence |
From the field · Accountant
Reconcile an invoice CSV to a monthly Excel report
The CSV invoice total is $42,180 higher than the report. Investigation finds cancelled invoices filtered only in one file, credit notes stored with inconsistent signs, and 14 amounts imported as text.
Columns used in the example
invoice_id · invoice_date · status · amount_raw · currency · customer_id · source_file
Follow it with your own file
- Match the reporting period, currency, and status definition.
- Compare counts and totals by status and source file.
- Parse text amounts into candidates and standardize credit signs.
- Bridge cancellations, credits, exceptions, and accepted invoices to the report total.
Check: Every source invoice must appear once in accepted, excluded-with-reason, or unresolved records. The signed bridge must equal the original difference within the agreed rounding threshold.
A method you can follow
- Align definitions: Confirm date range, population, metric, sign, currency, and rounding basis.
- Find the first divergence: Compare row counts and totals by source, period, and segment before the grand total.
- Inspect transformations: Measure exclusions, duplicates, join multiplication, conversion failures, and formula ranges.
- Build a bridge: Show source total plus documented adjustments equals reported total or an explicit remainder.
Reconciliation is a sequence of smaller equalities
A grand-total mismatch becomes tractable when it is localized by source, period, segment, and transformation. Control totals should follow the data through the workflow instead of being calculated only at the end.
Where this usually goes wrong
A small difference is not automatically rounding. Quantify the maximum plausible rounding effect and keep any excess as an unresolved exception until source evidence explains it.
Sign-off questions
- Metric definitions aligned
- Row counts compared
- Types and signs checked
- Join number of unique values checked
- Difference fully bridged or disclosed
Doing these steps in Prepyr
Prepyr accepts CSV files only. Export the worksheet or table you need as a UTF-8 CSV first; workbook formulas, formatting, macros, charts, and other sheets will not be included. Describe the result you need: Find and explain why Excel and CSV report totals disagree. Check the columns selected, preview any proposed changes, and compare the output with the source before applying or exporting it.
Prepyr steps used here: Data profiling · Reviewable cleaning · Reusable workflows · Version history
A documented reconciliation that identifies the exact records and rules responsible for the difference instead of forcing the two totals to agree.
Questions readers usually ask next
Why does Excel ignore numbers that look valid?
They may be stored as text or include symbols and separators that prevent numeric conversion. Profile the type and parse them explicitly.
Can rounding explain any small difference?
Only if the difference is within a calculated rounding bound. Otherwise investigate missing records, signs, units, filters, and joins.