CSV vs XLSX: which spreadsheet format should you use?

If you are exporting a spreadsheet and it asks whether you want .csv or .xlsx, here is the short answer: pick CSV when you are moving data into another system, and pick XLSX when you have a working document with formulas and formatting you want to keep. The two files can look identical when you open them, but they are not the same kind of thing at all.

TL;DR: CSV is plain rows of text separated by commas, with no types, no formulas, no formatting, and no way to hold more than one sheet. XLSX is really a zip file full of XML that keeps all of that. Use CSV to hand data between systems. Use XLSX when a person works in the file.

What a CSV actually is

CSV stands for comma-separated values, and that name is the entire format. It is a plain text file, one record per line, with a comma between each value. Open one in Notepad or any text editor and you see your data directly, like this:

name,city,zip
Jane,Boston,07001
Ravi,Newark,07102

That is the whole file. No fonts, no colors, no formulas, no second tab. A CSV does not know that 07001 is a zip code or that 07102 is a number. It just holds the characters. Because it is plain text, almost every program ever written can read it: databases, Python scripts, old mainframes, and every spreadsheet app.

The thing a CSV does not carry is meaning. There are no types. A date, a phone number, and a price are all just text. Whatever reads the file has to guess what each column is, and that guessing is where most of the trouble starts.

What an XLSX actually is

XLSX is the modern Excel workbook format, and it is more than a table. Here is a fact you can check yourself: an XLSX is a zip file. Take any .xlsx, rename it to .zip, and unzip it. Inside you get a folder tree of XML files. xl/worksheets/sheet1.xml holds the cells, xl/sharedStrings.xml holds the text, xl/styles.xml holds the formatting. The whole workbook is a bundle of small XML documents zipped together.

Because it stores all that, an XLSX keeps things a CSV cannot. A formula like =SUM(A1:A10) recalculates when you change an input, because the workbook saved the formula and not just its last answer. A cell formatted as currency stays currency. A date stays a date. And one file can hold many sheets, each as its own part inside the zip.

The cost is size and dependence on software that understands the format. An XLSX is bigger than the same data as CSV, and you need a program that speaks it, though nearly every spreadsheet app does now.

When to use CSV

Use CSV when a machine is the next thing to read your data. Exporting records for a database import, feeding a script, handing a clean table to a tool that does not open Excel files: that is CSV’s job. It is universal and small, so it travels well.

CSV also wins on size. Stripped of formatting, it is usually a fraction of the equivalent XLSX. And since it is plain text, you can open it, diff it in git, or inspect it anywhere without special software.

The rule of thumb: if a machine reads your data next, CSV is almost always right.

When to use XLSX

Use XLSX when a person works in the file. The moment you need formulas that update, formatting that survives, cell types that stay correct, or more than one sheet, CSV runs out of road. A budget with formulas, a report with a summary tab and a detail tab, a model where one number feeds dozens of calculations: all of that lives in XLSX and would be flattened by CSV.

Keep your real working files as XLSX. Export to CSV only at the edge, when you hand raw numbers to something else. Your master copy stays the richer format, because you cannot rebuild formatting or formulas from a CSV after the fact.

The gotchas most guides skip

CSV’s simplicity has sharp edges, and most spreadsheet disasters trace back to a handful of them. None are bugs. They are all the same root cause: a CSV has no types, so whatever opens it has to guess.

Leading zeros vanish

A zip code like 07001 is just five text characters in the file. When your spreadsheet app opens the CSV, it decides the column is a number and drops the leading zero, so 07001 becomes 7001. The same thing hits phone numbers, product codes, and anything else where the zeros matter. The file on disk is correct. The app’s guess on the way in is what broke it. To avoid it, import the column as Text rather than letting the app auto-detect.

Things that look like dates get converted

This one is famous enough to have a name in genetics. The gene symbol SEPT2 looks like a date to Excel, so it gets read as 2-Sept and stored as a date. MARCH1 becomes a date too. The problem was bad enough that in 2020 the body that names human genes renamed several of them so spreadsheets would stop mangling the data. It is not only gene names: a value like 1/2 (meant as a ratio or a part number) becomes January 2nd. The fix is the same as leading zeros. Bring the column in as Text.

The delimiter is not always a comma

The comma is in the name, but you cannot count on it. In regions where the comma is the decimal separator (so 3,14 means three point one four), CSV files usually put a semicolon between fields instead. Tab-separated files are common too. There is no header in the file that says which separator it uses, so a CSV that opens in neat columns on one machine can land entirely in column A on another. When you export, know which delimiter your reader expects.

Values that contain the delimiter

If a value has a comma in it, like Smith, Jane in a name column, a naive reader sees two fields and the row shifts. The format’s fix is quoting: wrap the value in double quotes, "Smith, Jane", and the comma inside counts as text. Good tools do this automatically. Hand-edited CSVs often forget, which scrambles the columns after it.

Encoding surprises

Plain text still needs a character encoding, and a CSV does not record which one it used. Save a file as one encoding, open it as another, and accented letters, currency symbols, or non-Latin scripts turn into garbled characters like é. Saving and opening as UTF-8 on both ends avoids nearly all of it.

Converting between the two

Richness only flows one way, and that is the key thing to remember. XLSX to CSV flattens the workbook to a single sheet of plain values and throws away every formula, format, chart, and extra tab. CSV to XLSX just wraps the existing values in a workbook. It cannot invent formatting or formulas that were never in the CSV.

So convert on purpose. Export XLSX to CSV when you truly want only the raw values from one sheet, for an import or a handoff, and keep the original XLSX as your master so nothing is lost. If you want a quick conversion without uploading a file anywhere, you can turn a workbook into CSV in your browser with the free Excel to CSV converter at excel.hivly.net. Nothing leaves your machine.

The short version holds up: CSV for moving data between systems, XLSX for a working document with formulas and formatting. Match the format to whether a machine or a person reads it next, and most of these headaches never happen.