Why Excel Formatting and Formulas Disappear in CSV
When you convert an Excel file (.xlsx) to CSV (.csv), all formatting—colors, fonts, column widths, merged cells, and formulas—is stripped away, leaving only the raw values. This happens because CSV is a plain-text format that stores only data separated by commas, with no ability to encode formatting or formulas. The conversion is a one-way lossy process, so you must plan ahead if you need to preserve any of that information.
What CSV Actually Is
CSV stands for Comma-Separated Values. It is a simple text format where each line represents a row, and commas separate the columns. It was designed in the early days of computing for transferring tabular data between programs that had nothing in common. Because it is plain text, a CSV file can be opened in any text editor, and it contains no metadata about styles, fonts, colors, or formulas.
Excel’s native format, .xlsx, is a compressed XML-based file that stores much more: cell formatting, formulas, charts, images, multiple worksheets, and more. When you export to CSV, Excel takes only the displayed values from each cell and writes them as text, discarding everything else.
Why Formulas Become Values
In Excel, a cell can contain a formula like =A1+B1. When you save as CSV, Excel evaluates that formula and writes the result (e.g., 5) into the CSV file. The formula itself is not saved because CSV has no syntax for formulas—it only stores data. This means you lose the ability to recalculate if you change the source data.
For example, if you have a spreadsheet that calculates total sales with =SUM(C2:C10), the CSV will contain the total number, not the SUM function. If you later edit the CSV and change one of the sales figures, the total will not update—it is now just a static number.
Why Formatting Disappears
Formatting includes anything that changes the appearance of a cell: font type, size, bold, italics, text color, fill color, borders, number formats (currency, percentages, dates), and column widths. CSV cannot store any of this because it is a pure data format. When you open a CSV in Excel, it applies default formatting—usually black text on a white background, with no borders and generic number formats.
This is why a CSV often looks “ugly” compared to the original spreadsheet. Even if you reopen the CSV in Excel, the formatting is gone permanently. You would have to reapply all styles manually.
How Excel Performs the Conversion
When you choose “Save As” and pick CSV, Excel uses the active worksheet only. It does not save other sheets in the workbook. It writes each cell’s value as text, using the system’s list separator (often a comma, but in some locales it is a semicolon). If a cell contains a comma, Excel wraps the value in double quotes to avoid breaking the column structure.
Excel also applies its default number formatting when writing to CSV. For instance, a date that was displayed as “January 5, 2024” might be written as 1/5/2024 or even as a serial number, depending on your regional settings. This can cause data to appear differently when opened elsewhere.
Common Problems and Misconceptions
One common misconception is that you can convert CSV back to XLSX and recover the original formatting. That is false—the formatting was never saved, so it cannot be restored. Another issue is that CSV files do not support multiple worksheets, so if you have a workbook with several tabs, you will only get the active sheet.
Another frequent problem is the loss of leading zeros. If a cell contains a ZIP code like 01234, Excel may write it as 1234 because the CSV format does not store number formatting. Similarly, long numbers like credit card numbers can be truncated or converted to scientific notation.
How to Choose the Right Export Format
If you need to preserve formatting or formulas, do not use CSV. Instead, consider:
- Excel Workbook (.xlsx) – keeps everything, but may not be compatible with other software.
- PDF – preserves the visual layout for printing or sharing, but is not editable.
- OpenDocument Spreadsheet (.ods) – an open standard that supports formatting and formulas, compatible with LibreOffice and Google Sheets.
- Tab-Separated Values (.tsv) – similar to CSV but uses tabs; still no formatting.
Use CSV only when you need a simple, universal data exchange format where the raw values are all that matter—for example, importing into a database, uploading to a web service, or sharing data with a colleague who uses a different spreadsheet program.
Alternatives to Avoid Data Loss
If you must export to CSV but want to keep some formatting, you can use a two-step approach: export a copy of the data as CSV, and also save a separate Excel file with the formatting intact. Another option is to use a tool like a script that reads the Excel file and writes the CSV manually, allowing you to control how dates and numbers are formatted.
For formulas, you could export the formulas as text by using a formula-to-text conversion, but that is not a standard feature. In practice, you should keep the original Excel file as the master copy and treat CSV as a one-way export.
Frequently Asked Questions
Can I recover formatting after saving as CSV?
No. Once you save as CSV, the formatting is permanently lost. Your only option is to reopen the CSV and manually reapply formatting, or to go back to the original Excel file if you still have it.
Why do my formulas turn into numbers?
CSV stores only values, not formulas. When you save, Excel calculates each formula and writes the result. This is by design—CSV is a data format, not a calculation format.
Why are my dates showing as numbers in the CSV?
Excel stores dates as serial numbers internally. When writing to CSV, it uses the date format from your regional settings, but if you open the CSV in a different program, it may interpret the value as a plain number. To avoid this, format the date cells as text before saving, or use a date format that is unambiguous, like YYYY-MM-DD.
Does CSV support multiple sheets?
No. A CSV file contains only one table of data. When you save a workbook with multiple sheets, only the active sheet is exported. You would need to save each sheet as a separate CSV file.
Why are my leading zeros missing in the CSV?
CSV does not store number formatting, so Excel writes the number as a numeric value, dropping leading zeros. To preserve them, you can format the cells as text before saving, or add an apostrophe before the number in Excel, which forces text format.
Is there a way to export Excel to CSV without losing formatting?
No, not directly. CSV is a plain-text format that cannot contain formatting. If you need formatting, use a different format like XLSX or ODS. For a compromise, you could export to HTML or PDF, which preserve layout but are not editable as spreadsheets.
Related
Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.