Excel to CSV: What You Lose and How to Avoid Surprises

A spreadsheet is a program. A CSV is a list of values. Exporting one to the other throws away everything that made it a program, and most of what people find surprising afterwards follows from that single fact.

What goes, and why

• Formulas. A cell holding =SUM(B2:B40) exports as the number it evaluated to. There is no CSV syntax for an expression, so the result is all that can survive.
• Formatting. Bold, colours, borders, number formats, conditional formatting and column widths are properties of the workbook, not of the data.
• Every sheet after the first. A CSV is one table. A workbook with twelve monthly tabs exports as one of them.
• Merged cells. They flatten, and the value lands in the first cell of what used to be the merge with the rest empty. Rows below often end up misaligned as a result.
• Charts, pivot tables, named ranges, data validation, comments and macros. None of these have anywhere to go.

The surprises that are avoidable

Dates come out in the machine's format

Excel stores a date as a number of days since 1900 and shows it according to the operating system's regional settings. On export it writes what it was showing. An export made in London writes 03/04/2026 for the third of April; the same file exported in New York writes it for the fourth of March.

The fix is to change the cell format to an unambiguous one before exporting. ISO 8601, which is yyyy-mm-dd, is unambiguous everywhere and sorts correctly as text, which is a second benefit.

Leading zeros disappear

If Excel is holding 0044 as a number, it is holding 44 and displaying it with a custom format. The export writes 44. The zeros were never in the data, only in the display.

This has to be fixed at import time, not export time. Once a column has been read as numeric, the zeros are gone. When opening a CSV in Excel, use Data then From Text and mark the column as Text rather than double-clicking the file.

Long numbers lose their tails

Excel holds numbers at fifteen significant digits. A sixteen-digit credit card number or a twenty-digit identifier read as a number comes back with zeros on the end. Same fix: import as text.

Accented characters arrive as mojibake

Excel on Windows writes CSV in the local code page unless told otherwise, and reads it the same way. A file written on a machine set to Western Europe and opened on one set to Central Europe will disagree about every accented character.

Save As and choose CSV UTF-8 rather than plain CSV. When going the other way, a UTF-8 file needs a byte order mark for Excel to recognise it, which is a checkbox in the output options here.

What to do about multiple sheets

There is no way around one CSV per sheet. Either export each sheet separately, or accept that you are exporting one of them. If the sheets have the same columns, exporting each and merging them afterwards produces one file with all the data, which is usually what was wanted.

A safer sequence

• Format any identifier column as Text before you start.
• Set date columns to yyyy-mm-dd.
• Check for merged cells and unmerge them.
• Save As, and pick CSV UTF-8.
• Open the result in a text editor, not in Excel, and look at the first few lines. Excel will happily re-mangle a file you just exported correctly.

That last step catches almost everything. A CSV is text, and looking at the text is the only way to see what is actually in it.