You export a clean CSV, open it in Excel, and the data is wrong. Postcodes have lost their leading zeros. Account numbers have become 1.23457E+15. A product code reads 2-Mar. Nobody edited anything. This page explains exactly what Excel is doing, which of these changes are irreversible, and how to import data without damage.
The one rule
Never double-click a CSV. Open Excel first, then Data → From Text/CSV, and set every identifier column to Text in the preview. Double-clicking applies General formatting to every column, and the conversion happens before the file is on screen — by which point the original values no longer exist.
The General format is the cause
Every cell in Excel has a format, and the default is General — which means "look at this value and guess what it is". That guess is applied at the moment of import, converting the text from your file into Excel's internal representation. It is not a display setting. The original string is not retained anywhere.
General's guessing order is roughly: does it look like a number? Then a date? Then a time? A fraction? Scientific notation? A boolean? If none of those, keep it as text. The rules are broad, and they were designed for someone typing figures into a spreadsheet — not for machine-generated identifiers.
🚨 These conversions are one-way
Formatting a column as Text after import does not restore anything. Excel already discarded the leading zeros, already rounded the sixteenth digit, already replaced MAR-1 with a date serial number. Changing the format only changes how the surviving value is displayed. If you have saved over the original file, the data is gone.
Leading zeros
| In your file | Excel shows | What was lost |
|---|---|---|
01234 | 1234 | A leading zero |
00042 | 42 | Three zeros |
007 | 7 | Two zeros |
0800 123456 | 800123456 | Zero and the space |
Excel reads 01234, decides it is a number, and stores the number 1234. Numerically that is correct — 01234 and 1234 are the same quantity. But these values are not quantities. A postcode, an employee number and a bank sort code are labels that happen to be made of digits, and you would never add two of them together.
This is the conceptual error at the heart of every problem on this page. Excel assumes digits mean arithmetic. Identifiers are strings.
The 15-digit limit
Excel stores every number as an IEEE 754 double-precision float — the same type most programming languages call a double. It has 53 bits of mantissa, which gives about 15 significant decimal digits. Beyond that, digits cannot be represented, and Excel writes zeros in their place.
| You enter | Excel stores | Displayed as |
|---|---|---|
1234567890123456 | 1234567890123450 | 1.23457E+15 |
4532015112830366 | 4532015112830360 | 4.53202E+15 |
9876543210987654321 | 9876543210987650000 | 9.87654E+18 |
Two separate things are happening here and they are worth distinguishing. Scientific notation is only display — widening the column or changing the format shows the full number again. The rounding is permanent — the final digits are genuinely gone, and no amount of reformatting brings them back.
This matters because the affected values are precisely the ones where every digit counts: card numbers are 16 digits, IMEI numbers 15, many national identity numbers 16 or more, and database bigint keys routinely exceed 15. A spreadsheet is silently corrupting the identifier while displaying something that looks plausible.
⚠️ How to spot it after the fact
Set the column to Number format with zero decimal places and widen it. If the last few digits are zeros where they should not be, the value has been rounded. Comparing against the source file is the only reliable check — Excel gives no warning that precision was lost.
Automatic date conversion
This is the most destructive behaviour, because the result does not merely look different — it is a completely different kind of value.
| Your data | Excel makes it | Stored internally as |
|---|---|---|
1-2 | 2-Jan | A date serial number |
3/4 | 4-Mar (or 3-Apr) | A date serial number |
MAR1 | 1-Mar | A date serial number |
SEPT2 | 2-Sep | A date serial number |
2E5 | 200000 | A number in scientific notation |
1/2 | 2-Jan | A date, not the fraction 0.5 |
Underneath, Excel stores a date as the number of days since 1 January 1900. So MAR1 becomes 45717 or similar. Reformatting the cell as Text shows you 45717 — not MAR1. The original string was never stored.
The gene naming case
The clearest illustration that this is a real and serious problem comes from genomics. Human gene symbols include SEPT1 through SEPT12 (the septin family), MARCH1 onwards, and DEC1. Every one of these matches Excel's date patterns.
Researchers routinely publish supplementary data as spreadsheets. Studies examining published papers found that a substantial fraction — around one in five with supplementary Excel files — contained gene names silently converted to dates. The errors were in peer-reviewed, published research.
In 2020 the HUGO Gene Nomenclature Committee, which is the formal authority on human gene naming, renamed the affected genes. SEPT1 became SEPTIN1. MARCH1 became MARCHF1. An international scientific body concluded it was more practical to rename decades of established nomenclature than to rely on people configuring Excel correctly.
💡 What that tells you
If a global standards committee decided the realistic fix was to change the data rather than the tool, treating this as user error is not reasonable. Assume any spreadsheet containing codes, identifiers or abbreviations has been altered unless you know it was imported as Text.
Locale and the decimal separator
Excel interprets numbers according to Windows regional settings, and much of the world uses a comma as the decimal separator and a full stop as the thousands separator — the opposite of the English convention.
| File contains | In an English locale | In a German locale |
|---|---|---|
1,234 | 1234 (thousands) | 1.234 (one and a bit) |
1.234 | 1.234 (a decimal) | 1234 (thousands) |
3.14 | 3.14 | 314 |
The same file opened on two machines produces different numbers — off by a factor of a thousand, with no error and nothing to indicate anything happened. This is the mechanism behind a whole class of cross-border financial reporting errors.
A related issue: in comma-decimal locales, Excel expects CSV files to be semicolon-separated, because the comma is already in use. A genuinely comma-separated file opened there lands entirely in column A.
Importing safely
The correct procedure
- Open Excel first. Do not double-click the CSV.
- Data → From Text/CSV, and select the file.
- In the preview, check the delimiter and the file origin (choose 65001: UTF-8 for anything with accented characters).
- Click Transform Data to open Power Query.
- Select each identifier column and set its type to Text.
- Close & Load.
Power Query is the important step. Unlike the old import wizard, it records your column types as a repeatable query — so refreshing the data reapplies them automatically rather than requiring the whole ritual again.
Marking values as text in the file itself
Note that simply quoting a CSV field does not protect it. "01234" is still parsed as the number 1234 — the quotes are CSV syntax for delimiting a field, and Excel strips them before applying its type detection. This is a widespread misconception and the source of a lot of confusion when a "properly quoted" export still gets mangled.
When you control the export
✅ Send .xlsx, not .csv
A CSV is untyped text — every value must be guessed at import, and that guessing is the entire problem. An .xlsx stores the type alongside each cell, so a value written as text opens as text with no interpretation. If your recipients use Excel and your data contains identifiers, exporting .xlsx eliminates this whole category of problem at the source.
Other silent alterations
| Behaviour | Effect | Fix |
|---|---|---|
| Trailing spaces trimmed | "ABC " → "ABC" | Import as Text |
| Very long text truncated | Cut at 32,767 characters | Do not use a spreadsheet for long text |
| Row limit | 1,048,576 rows — extra rows dropped silently | Use a database or Power Query |
| Column limit | 16,384 columns | As above |
| Line breaks in cells | May split a record on export | Quote fields; verify the row count |
| Encoding on save | Saves CSV as ANSI by default, mangling accents | Save as "CSV UTF-8" |
| Formulas recalculated | Values change on open | Paste as values before sharing |
The row limit deserves emphasis because it fails so quietly. Open a two-million-row CSV in Excel and you get the first 1,048,576 rows with a message that is easy to dismiss. Save it, and the other million rows are gone from the file. There is no marker in the saved data indicating truncation occurred.
A pre-flight checklist
- Never double-click a CSV. Import through Data → From Text/CSV.
- Set identifier columns to Text in the preview, before loading.
- Keep an untouched copy of the original file. This is the only real safety net.
- Check the row count against the source after import.
- Spot-check leading zeros and long numbers in the first and last rows.
- Choose UTF-8 as the file origin if there are any non-English characters.
- Export as .xlsx rather than CSV when the recipient uses Excel.
- Do not use Excel as a data pipeline. For transformation, use a tool that does not guess.
Converting CSV to JSON instead?
Convert between CSV and JSON in your browser with no type guessing and no upload — every value comes out exactly as it went in.
Open the CSV to JSON Converter →Summary
- The General format guesses at import time, and the guess is destructive.
- Leading zeros, 15+ digit precision and date conversions are irreversible. Reformatting afterwards does not recover them.
- Identifiers are text, not numbers. If you would never add two of them together, it is not a number.
- Quoting a CSV field does not protect it. Only importing as Text does.
- Locale changes what a comma means, so the same file gives different numbers on different machines.
- Excel silently truncates beyond 1,048,576 rows.
- The gene renaming is not a joke — it is the strongest evidence that this is a tool problem, not a user problem.
Frequently Asked Questions
How do I stop Excel removing leading zeros?
Import the column as Text rather than General. Use Data → From Text/CSV, then in the preview set that column's type to Text before loading. Formatting the cells as text after the fact does not help — the zeros were discarded at parse time and the information no longer exists in the file.
Why does my 16-digit number end in zeros?
Excel stores numbers as IEEE 754 double-precision floats, which hold only about 15 significant decimal digits. Anything longer is rounded, and Excel replaces the excess digits with zeros. A 16-digit card number loses its last digit permanently. Long identifiers must be stored as text — they are labels, not quantities.
Why did my product code turn into a date?
Excel's General format aggressively pattern-matches anything that could be a date. Values like 1-2, 3/4, MAR-1 and 2-10 all match date patterns and are converted, silently and irreversibly, into date serial numbers. The original text is gone once the file is saved.
Is it true that Excel caused scientists to rename genes?
Yes. Human gene symbols such as SEPT1 and MARCH1 were being auto-converted to dates in published research data, and studies found the problem affected roughly a fifth of papers with supplementary spreadsheets. In 2020 the HUGO Gene Nomenclature Committee formally renamed the affected genes, because changing the names proved more practical than changing Excel.
What is the safest way to open a CSV in Excel?
Never double-click it. Open Excel first, then use Data → From Text/CSV, and in the import preview set every identifier column to Text. Double-clicking applies General formatting to everything with no chance to intervene, and the damage happens before you see a single cell.