You export a clean file from a system that knows exactly what its data means, double-click it, and Excel rewrites part of it before you have read a single row. Postcodes lose a digit. A barcode column turns into 1.23457E+15. A product code called SEPT1 becomes 1 September. Nothing warns you, and the moment you press Save, the damage is written back to the file.
This is not a bug and there is no repair tool for it. It is a chain of decisions Excel makes while reading — and once you know what each one does, all of them are avoidable.
A CSV carries no type information. Nothing in it says “this column is a postcode, keep it as text” — every field is just a run of characters, so any program that wants to do arithmetic has to guess what they mean. Excel guesses with rules built for a person typing into a grid, where 007 almost always means seven and 1/2 almost always means a date. Applied to a machine-generated export, those rules are destructive. The guess happens at parse time, before anything is displayed, which is why the values are already wrong when the file appears.
What happens: a postcode 01310 becomes 1310, a product code 007 becomes 7, and a tax or national ID starting with a zero comes out one character short — which for a fixed-length identifier means invalid, not untidy.
Why: Excel classifies the field as a number, and as a number the zeros carry no information. They are not hidden by formatting; they are not in the value any more.
How to protect it: the column has to be marked as Text before the file is parsed. A custom format like 00000 pads the display back out afterwards, but that is a guess about width, not a recovery.
What happens: a 16-digit card number, IBAN fragment or barcode is shown as something like 1.23457E+15.
Why: two things are going on. The scientific display is only formatting — widen the column and a full-width number comes back. But underneath, Excel keeps only the first fifteen significant digits and replaces everything after them with zeros. The number that returns is not your number, and no formatting change recovers the rest, because it was never stored.
This is the point of no return. Leading zeros, dates and encoding problems are all recoverable from the original file, as long as you have not saved over it. Digits past the fifteenth are recoverable from nothing but a fresh export — so if a file with long identifiers has already been opened and saved in Excel, do not try to repair it. Export it again.
How to protect it: import the column as Text. A text column is never parsed as a number, so the ceiling never applies.
What happens: 3-5 becomes 3 May, 1/2 becomes a date, and codes mixing letters with digits get caught too. The most famous casualty is not a spreadsheet at all — it is human genetics.
Gene symbols such as SEPT1 and MARCH1 were being turned into dates in supplementary files attached to published papers. A 2016 study in Genome Biology found that roughly one in five papers with supplementary Excel gene lists contained these erroneous conversions. In 2020 the HUGO Gene Nomenclature Committee gave up on fixing the spreadsheets and renamed 27 human genes instead: SEPT1 is now SEPTIN1, MARCH1 is now MARCHF1. A scientific field changed its naming convention to accommodate a spreadsheet's autodetection.
How to protect it: Text columns again — or, in current Excel, switch the conversion off entirely (below).
What happens: the whole file lands in column A. Or numbers split down the middle: 1.234,56 and 1,234.56 are the same amount in different conventions, and read with the wrong one, half of it becomes a separate column.
Why: Excel does not split on commas. It splits on the list separator defined in your Windows regional settings. On a Turkish or continental European Windows the comma is reserved for decimals and the separator is a semicolon, so a genuinely comma-separated file gets no split at all. The same setting is used when saving — which is why a file exported on one colleague's machine opens as a single column on another's.
How to protect it: import through the Data tab, where you pick the delimiter for that file instead of inheriting a machine-wide setting.
What happens: İstanbul arrives as Ä°stanbul, ç becomes ç, and so on down the column.
Why: a CSV records no encoding either. Without a byte order mark at the start of the file, Excel commonly falls back to the legacy system code page instead of UTF-8. The file is fine; the reading of it is wrong. It runs in reverse too — Excel's plain CSV (Comma delimited) save has historically written the system code page, so a file that looked correct on your machine arrives broken elsewhere.
How to protect it: import via the Data tab with File Origin set to UTF-8, and when saving choose CSV UTF-8, which writes the mark that stops the next program having to guess.
Never double-click a CSV you care about. Go to Data → From Text/CSV. In the preview you set File Origin and the delimiter, then either choose Do not detect data types or open the query editor and set individual columns to Text under Home → Transform → Data Type. Anything marked Text arrives byte for byte.
The trade-off is real: a text column is text. You cannot sum it until you convert it deliberately — exactly what you want for an ID, and exactly what you do not want for a revenue column.
Excel for Microsoft 365 and Excel 2024 added File → Options → Data → Automatic Data Conversion, with separate switches for removing leading zeros, truncating long numbers to fifteen digits, reading E as scientific notation, and converting date-like letter-and-number combinations. It covers opening files, typing, pasting and Text to Columns. Two caveats: it does not exist in Excel 2019 or 2016, and it does not govern Power Query, so a Data-tab import still needs its column types set.
Most of the time you open a CSV to look at it — check what a column contains, find a row, confirm an export ran. That is reading, not editing, and it does not need a program that rewrites values on the way in. A viewer that renders the file exactly as stored answers the question without putting the file at risk, and there is nothing to save afterwards, so nothing to save wrongly.
Excel is not a badly built program, and none of these behaviours are mistakes on their own terms. Someone typing 1/2 into a spreadsheet means a date far more often than text; someone typing 007 almost always means seven; parsing to a fixed precision is what makes arithmetic fast and consistent. Every conversion above is the right default for a calculating grid a human types into.
The damage comes from the collision with a different job — reading a file another machine wrote, where every value is already correct and a helpful reinterpretation is the last thing you want. The format shares the blame: a CSV carries no schema, so it never tells Excel which columns are identifiers. So the conclusion is not “stop using Excel” — it is to use Excel to work with data, and something non-destructive to read a file and see what is in it.
Why does Excel remove the leading zeros from my CSV? It reads the field as a number, and as a number 01310 and 1310 are identical.
Can I get back a number that Excel turned into scientific notation? Only the display. The digits past the fifteenth were never stored, so only a fresh export recovers them.
How do I permanently disable scientific notation in Excel? There is no switch that stops Excel formatting a number you type, but you can stop it converting one it reads. Under File → Options → Data, in Automatic Data Conversion, clear Convert digits surrounding the letter E and Truncate numeric data to 15 digits. After that 123E5 stays as text instead of becoming 1.23E+07, and a sixteen-digit number keeps all its digits. Two limits worth knowing: the setting lives on the machine it is set on, not in the file, so a colleague opening the same CSV still loses the digits; and it cannot repair a file already saved, because those digits were never stored.
Which Excel versions have the Automatic Data Conversion settings? Microsoft 365 and Excel 2024, on Windows and Mac. Excel 2019 and earlier have no such panel — there the only reliable route is importing through Data → From Text/CSV with the columns set to Text, or not opening the file in Excel at all. If File → Options → Data shows no Automatic Data Conversion heading, your build does not have it.
How do I stop Excel from changing my CSV data? Import with Data → From Text/CSV and set the columns to Text, or switch the conversions off under File → Options → Data.
Why does my CSV open with everything in one column? Excel splits on your Windows list separator, which on a Turkish or European machine is a semicolon rather than a comma.
Why are Turkish characters broken when Excel opens my CSV? Without a byte order mark Excel falls back to the system code page instead of UTF-8; setting File Origin to UTF-8 on import fixes it.
Is Excel doing something wrong? No — every conversion is reasonable for a spreadsheet people type into, and destructive only because a CSV carries no type information to override the guess.
Excel changes a CSV while reading it, and saving makes the change permanent. Four of the five conversions are recoverable if you catch them before saving; the fifteen-digit truncation is not. Import through the Data tab with the columns you care about set to Text, switch the conversions off if your version offers it, and when you only need to read a file, use something that does not rewrite it.
Sheet Insights takes one Excel or CSV file and builds the whole analysis — executive summary, trends with forecasting, a statistics centre, data-quality checks and one-click cleaning — entirely in your browser, with no server behind it. It works offline after the first load.
See what Sheet Insights does