CSV Opens Wrong in Excel? Fix the Columns

Stop double-clicking the file. Open Excel first, go to the Data tab, and in the Get & Transform Data group choose From Text/CSV, then pick the file. The preview dialog that appears is where the four settings that break CSVs actually live: Delimiter, File Origin, Data Type Detection, and the per-column type. Double-clicking a .csv skips all four and lets your regional settings decide, which is why the same file opens cleanly on one machine and lands entirely in column A on another. Import it through that dialog and the columns split correctly, accented characters survive, leading zeros stay, and long ID numbers stop turning into 1.23457E+17. One warning before you start: not every symptom is repairable. If digits have already been truncated and the source CSV is gone, they are gone too.

A CSV that looks wrong in Excel is almost never one problem. It is four unrelated problems that happen to produce a similar shrug, and people waste an afternoon applying the fix for one of them to a file suffering from another. Widening the column does nothing for scientific notation. Formatting a cell as Text does not bring a leading zero back. Changing the display format does not rescue a mangled accent.

So the first move is diagnosis, not repair.

Which of the six failures are you looking at?

Find your symptom in the left column. The setting named in the middle is the one that caused it, and the last column is the part nobody tells you: whether the damage can be undone in the sheet you already have, or whether you need the original file back.

Symptom on screen	What actually happened	Setting responsible	Fix	Undoable in this sheet?
Every row sits in column A, commas visible inside the text	The file's separator is not the separator Excel expected	Windows list separator, or the Delimiter box in the import preview	Re-import and set the delimiter explicitly	Yes, no data was lost
Columns split, but rows drift sideways and text bleeds into the next column	A separator character sits inside a field that was never quoted	The exporting system's quoting, not an Excel setting	Re-export with proper quoting	No, the file itself is malformed
Accents read as é, ñ, ’	UTF-8 bytes were read as a single-byte code page	File Origin in the import preview, or a missing UTF-8 signature	Re-import with File Origin set to UTF-8	Yes, the file was never damaged
00417 shows as 417	Text that looked numeric was converted to a number	Data Type Detection, and Automatic Data Conversion	Re-import with that column typed as Text	No in the sheet, yes from the CSV
A 17-digit ID shows as 1.23457E+17	Scientific notation display, plus truncation past 15 significant digits	Excel's 15-digit precision ceiling	Re-import as Text if the ID is 16 digits or longer	Display yes, the digits past 15 no
A product code like 1-2 became 02-Jan	Type inference matched a date pattern	Data Type Detection on import, autocorrect on typing	Re-import with that column typed as Text	No in the sheet, yes from the CSV

Three of the six are fully reversible, two need the original CSV, and one needs a new export from whatever produced the file. Knowing which row you are on saves the afternoon.

Why does everything land in one column?

Because CSV does not carry its own separator, and Excel does not sniff for one when you double-click.

The RFC 4180 specification that defines the format is short and blunt about this: fields are separated by commas, records by line breaks, and any field containing a comma, a quote or a line break must be wrapped in double quotes. There is no header byte, no declared delimiter, no metadata of any kind. The file is a wall of text and the reader has to guess.

Excel's guess comes from your Windows list separator. On a machine with a US or UK regional format that separator is a comma, so a comma-separated file opens neatly. On a machine set to German, French, Dutch or Spanish, the decimal separator is a comma, so the list separator is a semicolon, and a comma-separated file has nothing to split on. Every line becomes one long string in column A. Nothing is broken. Excel simply looked for semicolons and found none.

That is also why the same file behaves differently for you and your colleague, and why "it works on my computer" is genuinely true rather than dismissive.

There are three real fixes, in descending order of how much you should like them.

Import instead of opening. Data tab, Get & Transform Data group, From Text/CSV. Microsoft's own import and export guidance for text files names this path, and the preview dialog lets you pick the delimiter from a list and watch the columns rearrange before anything is written to a sheet. This fixes the file in front of you without changing anything global.

Change the system list separator. Windows Settings, Region, Regional format, Additional settings, List separator. This makes every future double-click behave, and it also changes what Excel writes when you save as CSV, which may be exactly what a downstream system does not want. Useful, but it is a machine-wide change for a file-level problem.

Add a sep= line. Excel honours a first line of sep=; or sep=, when opening a CSV and uses it instead of the regional setting. It works, and it is the reason some exports ship with a strange-looking first row. It is also not part of the CSV specification, so anything other than Excel reads that line as a data row. Only use it for files that go to Excel and nowhere else.

If the columns split but the rows drift, you have the second problem, not this one. A field containing an unquoted comma pushes everything after it one column to the right, and only on the rows where that comma appears. No import setting fixes that, because the file genuinely does not say where the field ends. Go back to whatever produced it and ask for quoted output.

Why are accented characters garbled?

Because the bytes are fine and the interpretation is wrong.

When café renders as café and a curly apostrophe renders as ’, you are looking at UTF-8 bytes being read one at a time as if each were a character in a legacy code page such as Windows-1252. The é is two bytes in UTF-8, and read singly those two bytes are Ã and ©. Nothing has been lost. The file is intact, the reading is wrong, and re-importing with the right encoding restores everything.

In the From Text/CSV preview, the File Origin dropdown at the top left is the control. Choose the Unicode (UTF-8) entry and the preview redraws immediately with correct characters. If the file came from an older Windows system, Western European (Windows) is the other common answer. You are allowed to just try both and look.

The reason Excel gets this wrong on a double-click is the byte order mark. A UTF-8 file may begin with a three-byte signature, EF BB BF, which tells a reader what it is holding. The Unicode consortium's own FAQ on the UTF-8 BOM is clear that this signature is optional and serves no byte-ordering purpose in UTF-8. It exists purely as a hint. Excel leans on that hint heavily: Microsoft's note on opening CSV UTF-8 files correctly says a UTF-8 file saved with a BOM opens normally, and steers you to the Data tab import route when it does not have one.

Most systems that export CSV do not write the BOM, because for everything except Excel it is noise at the start of the file. That is the whole conflict, and it is why this symptom shows up on exports from CRMs, ecommerce platforms and analytics tools far more than on files people made themselves.

Why did leading zeros disappear?

Because Excel decided the column was numeric, and a number has no leading zeros.

Zip code 02134, a product SKU 000417, a German phone number, a bank sort code: all of them are strings that happen to be made of digits. Type inference reads the column, sees digits, and stores a number. The zeros are not hidden by a format, they were never stored.

This one has an important asymmetry. In the sheet in front of you it is unrecoverable, because 417 does not know whether it used to be 0417 or 000417. From the original CSV it is trivially recoverable, because the text is still sitting there in the file. So the fix is always the same: go back to the CSV and import it again with that column typed as Text.

In the From Text/CSV preview, set Data Type Detection to "Do not detect data types" if most columns are identifiers, or click Transform Data and set just the offending columns with Home, Transform, Data Type, Text. Microsoft's guidance on keeping leading zeros and large numbers walks the same route and adds the two manual alternatives worth knowing: format the cells as Text before typing, or prefix a single value with an apostrophe.

What does not work, and what people try first, is selecting the column after the fact and choosing Text, or applying a custom format code like 00000. The second one is worse than useless in a file you will send onward, because it paints zeros back on for display while the stored value stays short, so anything that reads the values rather than the screen still gets 417. If you want to understand where that line between stored value and displayed value sits, the number formatting guide covers the grammar properly.

Why did a long ID become scientific notation?

Two separate things happen to long numbers, and only one of them is cosmetic.

The cosmetic one is display. Past roughly twelve digits Excel switches a General-formatted cell to scientific notation, so 123456789012345 shows as 1.23457E+14. The value is whole and correct underneath. Widen the column or set a number format with zero decimal places and the digits come back.

The other one is not cosmetic. Excel stores numbers with a maximum of 15 significant digits. That figure is published in Microsoft's Excel specifications and limits as a hard property of the calculation engine, and it applies the moment a value is stored as a number. A 16-digit credit card number, an 18-digit order reference, a 20-digit IBAN fragment: everything past the fifteenth digit becomes zero. Not rounded to something near, replaced with zero.

That damage is permanent in the workbook. There is no setting, no format and no formula that restores digits Excel never wrote down. If the source CSV still exists you re-import the column as Text and move on. If the source CSV has been overwritten by a save from Excel, the digits are genuinely gone and the only honest answer is to ask for a fresh export.

This is the single most expensive failure on the list, because it is silent. A garbled accent announces itself. A truncated account number looks like a perfectly plausible account number until somebody tries to use it.

Which failures can you undo, and which need a new export?

Sorted by what you actually have to do:

Fixable from the sheet you are looking at. The all-in-column-A case, because nothing was lost. You can even repair it in place by selecting column A and running Data, Text to Columns, which is the fastest path when you cannot re-import for some reason. It overwrites the columns to its right without asking, so read the Text to Columns walkthrough before you run it on a populated sheet. Scientific notation on an ID of 15 digits or fewer is also a pure display fix.

Fixable by re-importing the original CSV. Garbled encoding, lost leading zeros, codes converted to dates, and any ID of 16 digits or more that has been truncated. The file is intact. Only the reading was wrong. This is why the rule is to keep the raw download until the import is confirmed good, rather than saving over it.

Needs a new export. A malformed file whose fields are not quoted, and any truncation where the original CSV is gone. Also dates that arrived in two different orders in one column, which is a different diagnosis with its own repair path for mixed date formats, because the ambiguity between 03/04/2026 as March 4 and as April 3 is real and no tool can resolve it for you.

How do you make the fix stick next time?

Four changes, roughly in order of payoff.

Make importing the habit. On Windows, Data, Get & Transform Data, From Text/CSV. On Mac it is the Data tab, Get Data command, and it requires a Microsoft 365 subscription. In Excel for the web, Power Query support is partial and there is no equivalent local file import, so a CSV that matters should be handled in the desktop app. Microsoft's page on Power Query availability across Excel versions is the one to check if a menu is not where you expect.

Turn off the conversions you never want. In current builds, File, Options, Data, Automatic Data Conversion carries individual switches for removing leading zeros, truncating long numbers to 15 digits, and reading E-containing codes as scientific notation, plus a checkbox that warns you when a CSV load is about to convert something. Microsoft documents these under set automatic data conversions. Turning off the truncation warning-free behaviour is the highest-value five seconds in this article.

Ask upstream for UTF-8 with a BOM. If you receive the same export monthly from a system somebody in your organisation controls, this is one setting on their side and it removes the encoding problem permanently.

Keep the raw file. Import into a new workbook, save as .xlsx, and leave the .csv untouched. Every recoverable failure above depends on that file still existing.

Where SuperSheet fits

Our own CSV to Excel converter exists for the same reason this article does. It parses the file in your browser, reads the whole column before deciding what type it holds rather than committing on the first row, and shows you the inferred type so you can disagree before anything is written. What comes out is a formatted .xlsx with real number and date formats, a frozen header, sized columns and a totals row built from live SUM formulas, none of which a CSV can carry.

It is honest about its edges. It works on the first 5,000 rows and files up to 15 MB, so very large exports need slicing or trimming to the columns that matter. It deliberately does not unmerge cells or restructure a tab holding several tables, because guessing at structure is how files get quietly wrong. And it cannot recover digits that Excel already truncated in a saved workbook, because nothing can. Feed it the original CSV and it will not truncate them in the first place.

Nothing uploads. There is no upload endpoint and no storage.

Common questions

How do I open a CSV without Excel splitting it wrongly?

Open Excel to a blank workbook first, then go to the Data tab and choose From Text/CSV in the Get & Transform Data group. Select your file and the preview dialog appears with three controls that matter: File Origin for the encoding, Delimiter for the separator, and Data Type Detection for whether Excel is allowed to reinterpret your columns. Set them, watch the preview redraw, and only then click Load. Double-clicking the .csv in File Explorer skips this dialog entirely and hands every one of those decisions to your regional settings.

How do I keep leading zeros in a CSV?

Import the file rather than opening it, and type the affected column as Text before it is loaded. In the preview dialog, either set Data Type Detection to "Do not detect data types", or click Transform Data and set the specific column with Home, Transform, Data Type, Text. Once a value has been stored as a number the zeros are gone from the stored value, and applying a custom format like 00000 only paints them back on screen while the underlying value stays short. For files you create yourself, formatting the cells as Text before typing, or prefixing a single entry with an apostrophe, both work.

What encoding should a CSV use for Excel?

UTF-8, written with the byte order mark. UTF-8 handles every character set you are likely to meet, and the three-byte signature at the start of the file is the hint Excel uses to recognise it on a plain double-click. Without that signature Excel falls back to a legacy code page and accented characters come apart. If you cannot control the export, the encoding is still fixable at your end: set File Origin to the Unicode (UTF-8) option in the import preview and the characters come back intact, because the file was never damaged in the first place.

Why does my CSV look fine in Notepad but wrong in Excel?

Because Notepad shows you the file and Excel interprets it. Notepad prints the characters as they are, with the commas visible and no opinion about what any field means. Excel has to decide where the columns break, what encoding the bytes are in, and what type each column holds, and it makes all three decisions before showing you anything. That is the whole gap. Notepad is genuinely the right tool for the first thirty seconds of diagnosis: open the CSV there, look at the first two lines, and you will see which separator the file uses and whether fields are quoted. Then go into Excel knowing the answer instead of guessing at it.