Stop Excel Silently Changing Your CSV Data (Zeros, Dates, Big Numbers)

A CSV file is just text. Nothing in it tells a program that 00123 is a numeric value or that SEPT1 is a calendar date. Excel decides that for you the moment you double-click the file, and it decides silently. The cell looks tidy, the header row lines up, and the corruption is already baked in before you notice. This is not a rare bug. It has quietly damaged tax records, product catalogs, and enough published science that an entire field renamed its genes to get away from it. Here is exactly what Excel changes, why, and how to keep your data intact.

The four ways Excel rewrites your data

When Excel opens a CSV it does not just display the text. It runs each cell through a type guesser and, wherever the text looks like a number or a date, it converts and reformats it. There are four classic failure modes, and each one throws away information that no formula can bring back.

1. Leading zeros vanish

Zip codes, phone numbers, bank sort codes, and internal IDs often carry meaningful leading zeros. Excel reads the field as a number, and numbers do not start with zero, so the zeros are dropped.

id,zip,phone
00123,07030,0044123456
// Excel shows:
123,7030,44123456

2. Codes turn into dates

Anything that resembles a day and month gets converted to a serial date and reformatted to your locale. A part number, a ratio, a fraction, or a gene symbol can all disappear this way. The original text is gone: what remains is a date serial that no longer means what you typed.

gene,ratio,part
SEPT1,1/2,MAR-3
// Excel shows:
1-Sep,02-Jan,3-Mar

3. Long numbers become scientific notation

A 16-digit credit-card-length number or a long barcode is read as a number too large to display, so Excel collapses it to scientific notation like 1.23457E+15. The cell now shows an approximation, not your value.

card,barcode
1234567890123456,4006381333931
// Excel shows:
1.23457E+15,4.00638E+12

4. The 15-digit precision wall

This is the quietest and worst one. Excel stores numbers with at most 15 significant digits, following the IEEE 754 floating-point specification. Microsoft's own documentation is blunt about it: for any number containing 16 or more digits, such as a credit card number, any digits past the fifteenth are rounded down to zero. So a 16-digit card or a long account number is not just reformatted, it is silently truncated. Change it back to a plain number format and the trailing digits are permanently gone.

account
1234567890123456789
// Excel keeps only 15 significant digits:
1234567890123450000

The gene that got renamed to escape Excel

The most famous casualty of the date conversion is human genetics. In 2016, Ziemann, Eren, and El-Osta scanned the supplementary spreadsheets attached to thousands of published genomics papers and found gene-name-to-date errors in roughly one fifth of them. Their scan confirmed errors in 987 supplementary files from 704 articles: about 19.6 percent of papers with supplementary Excel gene lists were affected. Gene symbols like SEPT1 (a septin gene) were being converted to 1-Sep, and MARCH1 to 1-Mar, then saved back out as corrupted data that no reader could distinguish from the real thing.

The problem was so entrenched that in 2020 the HUGO Gene Nomenclature Committee did something remarkable: it renamed the genes instead of waiting for the software to change. Bruford and colleagues published updated guidelines in Nature Genetics, and 27 human genes were formally given new symbols specifically to survive spreadsheet autoconversion. SEPT1 became SEPTIN1, MARCH1 became MARCHF1, and so on. When the fix for a software default is to rewrite the names of parts of the human genome, the default is the bug.

What survives and what does not

The pattern is clear: plain text and short, unambiguous numbers survive. Anything that looks like a date, a very long number, or a number with a meaningful leading zero is at risk. And the moment Excel converts, then you save the file, the CSV on disk now holds the corrupted version.

How to stop it

There are three reliable approaches, from most manual to most foolproof.

Import with the wizard, columns set to Text

Never double-click the CSV. Instead, open a blank workbook and use Data, then Get Data or From Text/CSV to bring the file in through the import flow. In the preview, set every column that holds codes, IDs, or symbols to the Text data type before you load. Text columns are handed to the grid verbatim, with no guessing. This is more clicks, but it is the only way to control typing on a file you did not create.

Turn off automatic data conversion

Since 2023, current versions of Excel (Microsoft 365 and Excel 2024) added settings to disable the conversions at the source. On Windows go to File, then Options, then Data, and find Automatic Data Conversion. On Mac it lives under Excel, then Preferences, then Edit. You can independently switch off four behaviors: removing leading zeros, truncating long numbers to 15 digits and showing scientific notation, converting text around the letter E to scientific notation, and converting a continuous string of letters and numbers to a date. Turning these off makes Excel keep such entries as text instead of silently rewriting them.

Edit the CSV in a tool that never coerces

The most reliable fix is to stop using a spreadsheet for the job. When you only need to view, clean, or edit tabular data, use an editor that treats every cell as the text it is and never guesses types. The CSV editor opens your file exactly as written, with no date conversion, no dropped zeros, and no 15-digit truncation, so what you see is what is on disk. It runs entirely in your browser, so the file is never uploaded. When you genuinely do need a spreadsheet, convert deliberately with the CSV to Excel converter so the typing is a choice you make, not a default that happens to you.

It also helps to understand the layer below the conversion. If your fields are getting split or merged as well as retyped, the cause is usually quoting or the delimiter, not Excel's type guesser. Get the structure right first, then protect the values.

A short checklist

1. Never double-click a CSV you care about. Import it, or open it in a non-coercing editor.
2. Set ID, code, zip, and gene columns to Text before Excel loads them.
3. Treat any 16-digit-or-longer number as text, always. The 15-digit wall is unrecoverable.
4. On Microsoft 365 or Excel 2024, turn off Automatic Data Conversion under File, Options, Data.
5. Remember the settings are per-machine: a colleague on defaults can still corrupt a shared file.
6. When you only need to edit or clean the data, skip the spreadsheet entirely.

Sources

• Ziemann, Eren, El-Osta: Gene name errors are widespread in the scientific literature. Genome Biology 2016
• Bruford et al.: Guidelines for human gene nomenclature. Nature Genetics 2020
• Microsoft Support: Keeping leading zeros and large numbers
• Microsoft Support: Set automatic data conversions