Sheet ReserveSpreadsheets worth keeping.

Formats

What is the difference between XLSX and CSV?

Short answer

XLSX is a ZIP package of XML that stores formulas, formatting, data types and several worksheets. CSV is a plain text file of comma-separated values for one table, with no formulas, formatting or types. Use XLSX to keep a working spreadsheet intact, and CSV to move raw rows between programs, databases and scripts.

Updated

An XLSX file is a complete workbook. A CSV file is just a table of text. Everything else follows from that.

Side by side

XLSX CSV
Structure ZIP package of XML parts One plain text file
Worksheets Many One
Formulas Stored, with the last result Not stored, only the value
Formatting Fonts, colors, widths, number formats None
Data types Number, text, date, boolean Everything is text
Charts, images, validation Yes No
Standard ECMA-376, ISO/IEC 29500 RFC 4180 describes it, but it is informal in practice
Opens in Spreadsheet apps and many libraries Any text editor, any database, any language

The same data, both ways

Take a sheet with these cells, where C2 holds the formula =A2*B2:

Item Quantity Total
Pencil 12 6.00

As CSV, the whole file is two lines:

Item,Quantity,Total
Pencil,12,6

The formula is gone and only the result remains. Whether 6 is a number or the text "6" is decided by whatever program reads it next. As XLSX, the cell stores both the formula A2*B2 and the cached value, plus its number format.

What CSV does well

  • It is tiny, readable and universal. Databases, statistics tools, scripts and every spreadsheet app can import it.
  • It diffs cleanly in version control.
  • It has no hidden content, so it is safe for data exchange.

Where CSV fails

  • Types are guessed. Excel turns 00123 into 123, and a value like 3-4 can become a date. See how to keep leading zeros in a CSV file.
  • There is no encoding or delimiter declaration. Accented characters can appear garbled, and in locales that use a decimal comma, Excel expects semicolons. See why Excel puts a CSV in one column.
  • Only one table fits. A workbook with several sheets needs one CSV per sheet.
  • The exact rules for commas, quotes and line breaks inside fields come from RFC 4180: a field containing a comma or quote is wrapped in double quotes, and quotes inside it are doubled.

Which to use

Choose XLSX when the recipient needs to calculate, filter, format or print. Choose CSV when a program, not a person, is the consumer, or when you want the most portable plain data. A common pattern is to keep the working file as XLSX and export a CSV only for the import step.

If you must convert, the checklist in how to convert XLSX to CSV without losing data covers the traps. Datasets such as the GDP by country table suit both formats; the blog post on choosing a format explains when each is better.