GUIDE · UPDATED 2026
Excel changes CSV files. Here is exactly how.
Opening an instrument's CSV export in Excel is the most common way a raw result gets altered without anyone deciding to alter it. Nothing looks wrong on screen. The damage is in what Excel guesses when it opens the file, and in what it writes back if someone presses Save.
Written by Taha Bayar, a biologist working on laboratory data handover.
What Excel guesses when it opens a CSV
| In the file | Excel shows | Why |
|---|---|---|
00123 (sample ID) | 123 | It looks like a number, and a number has no leading zeros. |
1234567890123456789 (19-digit barcode) | 1.23457E+18 | Excel keeps 15 significant digits. The rest become zeros: 1234567890123450000. |
12E3 (an ID) | 1.20E+04 | Digits around an E are read as scientific notation, 12 × 10³. |
SEPT2, MARCH1 (genes) | 2-Sep, 1-Mar | Text that looks like a date becomes a date. |
1-2, 3/4 (wells, ratios) | a date | The same guess; which date depends on the PC's regional settings. |
0.80 | 0.8 | Trailing zeros are formatting, not value. The precision the instrument reported is no longer visible. |
The gene row is not hypothetical. A 2016 study of supplementary files found gene-name errors of this kind in roughly one in five papers that shared gene lists in Excel, and in 2020 the HUGO Gene Nomenclature Committee renamed 27 human genes, SEPT1 to SEPTIN1 and MARCH1 to MARCHF1 among them, so that spreadsheets would stop converting them.
On a PC set to a decimal comma (Turkish, German, French and many other regional settings), a double-clicked CSV is also split on semicolons instead of commas. A comma-separated instrument export then opens as a single column.
What Save writes back
- The guesses become the data. Lost zeros and digits, and dates in the PC's date format, are written into the file.
- The encoding can change. "CSV (Comma delimited)" saves in the PC's legacy code page, not UTF-8. Characters outside it, such as α or Δ on a Western European PC, become question marks. "CSV UTF-8" is a separate entry in the Save As list.
- Separators, quotes and line endings can change. The values may survive, but the file is no longer the same file, and its SHA-256 changes.
How to switch the guessing off
Excel for Microsoft 365 and Excel 2024 have a setting for it. On Windows: File → Options → Data → Automatic Data Conversion. On a Mac: Excel → Preferences → Edit. There are four switches: leading zeros, 15-digit truncation, the letter E, and letters-and-numbers that look like dates.
Two catches. First, it is per user and per PC: the colleague who opens the file next may still have it on. Second, Microsoft notes that values with a space or a hyphen, such as JAN-1, may still become dates with the switch off. Well positions like 1-2 are exactly that shape.
In older Excel, and whenever the file matters, import instead of opening: Data → From Text/CSV, then Transform Data, set identifier columns to Text, and load. The original file on disk is not touched.
Keep the raw file raw
- Take its SHA-256 before anyone opens it. That value is how you later show the file was not changed.
- Open a copy, never the export itself. Or set the export folder to read-only.
- Import with identifiers as text, as above.
- Move the original bytes, not a re-saved file. If the result leaves the instrument PC, the file that arrives should be the one the instrument wrote, with the hash taken at the source.
Kionel does the last step for an offline instrument PC: it moves the file as bytes, signs its SHA-256 with the work order on the sending PC, and checks it again on arrival. To compare sample ID lists without Excel touching the zeros, use the sample reconciliation tool; it keeps leading zeros and letter case as they are.