Why your CSV totals look wrong in Excel

You download a sales CSV, open it in Excel, and get a different total from the one you expected. Before changing any values, keep an untouched copy. Excel interprets CSV columns using its import settings, so what appears in the worksheet may differ from the original text. That does not mean opening the file has rewritten the source. Microsoft explains how CSV imports work.

Start with the amount column. A cell that looks like $1,249.50 might contain text rather than a number. If that cell is text and the next cell contains the number 89.95, summing the range gives 89.95, not 1339.45. Excel's SUM function ignores text in referenced cells. Changing the appearance to Currency is not a reliable way to convert those values. Microsoft's SUM documentation describes this behavior.

Next, protect identifiers. An order ID such as 000101 is a label, even though it contains digits. If Excel converts it to a number, the leading zeros disappear. Long numeric IDs can also lose digits beyond Excel's 15-digit precision. Formatting an already damaged ID as text cannot recover its original value. Reimport from the untouched export. Microsoft's guidance on preserving IDs covers both problems.

In desktop Excel, use Data > From Text/CSV, then Transform Data. Check the delimiter in the preview. In Power Query, inspect Applied Steps: remove an automatic Changed Type step if it has already converted IDs, then set the ID columns to Text while their original strings are intact. Set the other column types deliberately before loading the result. Power Query can infer types from only the first 200 rows, so a plausible preview does not validate every record. Microsoft documents that automatic detection.

Dates and amounts need the export's regional conventions. 09/03/2026 could mean September 3 or March 9. A wrong interpretation can put an order outside your reporting window. Similarly, 1,249.50 and 1.249,50 use different decimal separators. Confirm the source convention, then right-click the relevant column in Power Query and select Change Type > Using Locale. Choose the appropriate type and region. If the date convention is unknown, keep the original text and ask for clarification. Microsoft explains locale-based conversion.

Finally, compare the same records and currencies. USD and CAD need separate totals unless you explicitly apply exchange rates. Removing an approved duplicate or excluding an unresolved record also changes the accepted total. Two rows sharing an order ID might be separate line items, so agree on the record's unique identifier before deduplicating. Keep uncertain amounts in a review list rather than replacing them with zero.

Before using the result, run this short check:

• Compare one leading-zero ID with the original CSV.
• Verify one date whose day and month could be confused.
• Check that amount cells are numeric and negative amounts remain negative.
• Reconcile input records with accepted, review and removed-duplicate records.
• Compare totals by currency using the same date and status filters.

Disclosure: This guide is from Export Rescue, a CSV cleanup service offering agreed normalization, duplicate removal, flagged exceptions and a change log for $49 per file, up to 5,000 rows and 15 columns.