CSV vs Excel: which format to use, and what each one silently drops

“Just send it as Excel” and “can you export that as CSV” are both said dozens of times a day in every office, usually without anyone deciding — which is how data ends up shuttling between two formats that preserve different things, losing a little in every hop. The formats aren’t rivals; they’re tools for different jobs. Here’s the actual difference, and the conversion rules that avoid the losses.

What each format actually is

CSV is text. Rows of values separated by a delimiter, readable by literally everything from a 1980s mainframe to a Python one-liner. It has no types, no formatting, no formulas, no sheets — a cell containing 02134 is five characters, and what they mean is the reader’s problem.

.xlsx is a package. A ZIP archive of XML parts describing typed cells (number, text, boolean, date-formatted), formulas and their cached results, styles, multiple sheets, charts, pivot caches. A cell isn’t five characters; it’s a value plus a type plus a format — which is why Excel doesn’t have to guess what it means.

That one design difference — types in the file versus types guessed by the reader — explains almost every CSV horror story: Excel guessing at CSV bytes is where ZIP codes lose zeros and gene names become dates.

What survives a round trip (and what doesn’t)

Going .xlsx → CSV you lose, permanently: formulas (only their last computed values export), all formatting (currency symbols, colors, column widths), every sheet but the one you exported, charts and pivot tables, and cell types themselves. You also expose dates to ambiguity: a date cell becomes whatever string the exporter writes — good exporters (this one) write unambiguous year-first dates; Excel’s Save As writes your locale’s format, which the next reader may misparse.

Going CSV → .xlsx you lose nothing, but you gain risk: something has to assign types, and a careless converter (or Excel’s open-and-save) assigns them by guessing. A careful CSV → Excel conversion writes real numbers as numeric cells while forcing leading-zero values and >15-digit IDs to text — the data arrives typed and intact.

When CSV is the right call

Any machine is on the receiving end: imports, APIs, databases, scripts. CSV is the lingua franca; .xlsx needs a library.
The data is big. CSV streams — tools can process it row by row, which is how multi-GB files open in a browser. An .xlsx must essentially be unpacked in memory; the same data as .xlsx is often unusable at sizes where the CSV is routine. Excel’s grid also hard-stops at 1,048,576 rows.
You want diffs and version control. CSV in git shows meaningful line diffs; .xlsx is an opaque binary blob to diff tools.
Longevity matters. A CSV from 1995 opens today; it will open in 2055.

When .xlsx is the right call

Humans will read it. Frozen headers, column widths, number formatting — the things that make data presentable — only exist in the spreadsheet format.
Types must survive the trip. Sending a CSV of ZIP codes to a colleague who will double-click it into Excel guarantees mangling; sending .xlsx with those columns typed as text prevents it. When the recipient is Excel, speak Excel.
Formulas or multiple sheets are the content. CSV simply can’t carry them.
One file, one artifact. Reports, deliverables, anything that gets emailed and reopened months later benefits from being self-describing.

The conversion rules that avoid every loss above

Convert deliberately, not by open-and-save. Excel’s Save As applies its conversions on the way through; a direct converter doesn’t.
Dates: make them unambiguous at the boundary. Year-first (2024-03-31) survives every locale; 03/04/2025 means two different days in Boston and London.
IDs and codes are text. ZIPs, phone numbers, SKUs, anything with leading zeros or more than 15 digits — if it won’t be summed, it isn’t a number.
Keep the CSV as the source of truth when a file must exist in both formats, and regenerate the .xlsx from it — not the other way around, because the .xlsx → CSV direction is the lossy one for anything with formulas.
Check the round trip once. Convert, open, compare five known-tricky cells (a ZIP, a long ID, a date, an accented name, a number you can sum). Two minutes, and it validates the whole pipeline.

The honest summary

CSV is for data in motion — between systems, at scale, into the future. Excel is for data being read — by people, with meaning attached. Most workflows genuinely need both, and the losses only happen at careless boundaries. Put a careful converter at those boundaries and the two formats stop fighting: the pipeline keeps its faithful bytes, the humans get their typed, readable workbook, and nobody’s ZIP codes go missing in between.