CSV vs XLSX: Which File Format to Use

The short answer to CSV vs XLSX: use XLSX for any file people work in, because it keeps formulas, formatting, sheets and charts, and use CSV to move data between systems, because it is plain text that almost any program can read. The catch is that CSV keeps only values, and opening one in Excel can quietly change your data. This comparison is for anyone who downloads reports from a system, uploads lists to one, or has seen zip codes and dates go wrong after opening a CSV.

CSV vs XLSX at a glance#

CSV is a simple text format for data exchange; XLSX is a full workbook format for working in Excel.

CSV stands for comma-separated values. In some countries where the comma is the decimal separator, Excel uses semicolons instead, but the idea is the same.

What each file contains#

The two formats store the same rows of data in very different ways.

Inside a CSV file#

A CSV file is just text. Open one in Notepad or TextEdit and you see something like this:

EmployeeID,Name,StartDate,Salary
00123,Priya Shah,2024-03-15,52000
00456,Tom Okafor,2024-09-02,48500

Each line is a row and each comma marks a new column. There are no fonts, no formulas and no notion of a "date" or "number": every value is a piece of text. That simplicity is the point. Databases, accounting systems, web apps and scripts can all read and write CSV, which is why exports and imports so often use it.

Inside an XLSX file#

An XLSX file is Excel's standard workbook format, used since Excel 2007. It stores each cell's value and its type, so a date stays a date and text stays text. It also stores formulas, number formats, multiple worksheets, tables, charts, pivot tables, data validation, conditional formatting and comments. Inside, it is a compressed bundle of XML files, which keeps file sizes manageable. If your workbook has macros, the related .xlsm format is used instead.

The same data saved both ways#

Imagine the employee list above as a workbook, with a formula column for annual bonus and a header row in bold. Here is what survives each format:

The salary survives fine, because it's a plain number. The ID and date are where CSV causes trouble.

Problem 1: leading zeros disappear#

When you double-click a CSV, Excel sees 00123, decides it's a number, and stores 123. IDs, zip codes, phone numbers and product codes all suffer from this. If the zeros are already gone and every code should have the same length, you can rebuild them:

=TEXT(A2,"00000")

If A2 holds 2134, the result is the text 02134. The format code "00000" means "at least five digits". The text and value lesson explains why the same digits can be stored as text or as a number, and TEXT vs VALUE shows how to convert in each direction.

Problem 2: dates change or arrive as text#

A CSV date like 03/04/2024 can be read as 3 April or 4 March, depending on your computer's regional settings. Some dates won't convert at all and stay as text, aligned to the left of the cell, so date maths fails. To fix text dates, see how to convert text to dates and the comparison of DATE vs DATEVALUE. The date basics lesson explains how Excel stores a real date as a number.

Problem 3: long numbers lose digits#

Excel keeps 15 significant digits. A 16-digit card or account number in a CSV becomes something like 1.23457E+15 on screen, and digits after the 15th turn into zeros. Import such columns as Text.

How to open a CSV safely in Excel#

Don't double-click the file. Import it instead, so you choose each column's type before Excel guesses:

1. Open a blank workbook.
2. Go to Data > From Text/CSV (Excel 2016 and later, and Microsoft 365).
3. Pick the file, then click Transform Data.
4. Click the header of an ID or code column, and set its type to Text.
5. Set date columns to Date, choosing the right locale if needed, then click Close & Load.

Recent Microsoft 365 versions also have settings under File > Options > Data to stop some automatic conversions, such as removing leading zeros. These settings affect only your computer, so importing is still the safer habit when sharing instructions with others.

When to use CSV#

Use CSV when data must travel between systems. It fits:

• Uploading lists to a CRM, payroll, email or e-commerce system.
• Exporting from a database or app so you can analyse the data.
• Sharing data with people who don't use Excel, or with scripts and programming tools.
• Very large raw data, where a simple, universal format matters more than features.

When saving, choose CSV UTF-8 (Comma delimited) if the data has accented letters or non-English characters, so they come through intact.

When to use XLSX#

Use XLSX for anything a person works in. It fits:

• Reports, models and dashboards with formulas, charts or pivot tables.
• Files with several sheets, such as data, lookups and a summary.
• Anything that must keep formatting, such as an invoice or a timesheet.
• Data with IDs, codes or dates that must not change when the file is reopened.

Formulas like TODAY vs NOW only keep updating in XLSX; in a CSV they freeze as the values shown when you saved. The same goes for date calculations such as adding days, months and years.

Common mistakes with CSV and XLSX#

1. Working in a CSV. Formulas and formatting look fine until you save and reopen. Then they are gone. Save as XLSX first.
2. Double-clicking CSV exports. Excel guesses types and can drop zeros or change dates. Import with Data > From Text/CSV.
3. Saving over the original export. Once Excel has changed 00123 to 123 and you save the CSV, the zeros are gone from the file too.
4. Expecting every sheet in the CSV. Only the active worksheet is saved.
5. Ignoring the warning on save. When Excel says some features might be lost, it means formulas, formatting and other sheets.
6. Uploading the wrong delimiter. If a system expects commas and your settings produce semicolons, the upload fails. Check the system's requirements.

Our recommendation#

Treat XLSX as your working format and CSV as a shipping format. Keep your real file as XLSX, with formulas, formatting and every sheet. When a system needs data, save a CSV copy for that upload. When you receive a CSV, import it with Data > From Text/CSV and set ID and date columns deliberately, then save your work as XLSX.

In short: work in XLSX, exchange in CSV, and import CSV files instead of double-clicking them.

Practise it#

Try the exercise on this page: rebuild zip codes that lost their leading zeros. Then practise converting text and numbers in a live spreadsheet and the date basics lesson, so imported data never catches you out. You type each formula, and it checks your answer instantly.