What a CSV file is and why the format matters
A CSV file is a plain-text spreadsheet — the letters stand for "comma-separated values." Instead of storing data in a proprietary format like Excel does, a CSV file uses commas to separate columns and line breaks to separate rows. When you open a CSV file in a text editor, you see raw text. When you open it in a spreadsheet program, it displays as a table.
The reason CSV matters is that almost every program can read it. Excel reads CSV. Google Sheets reads CSV. Database software reads CSV. Bank systems read CSV. If you need to move data from one program to another, CSV is usually the safest bridge. The tradeoff is that CSV cannot store formatting — no bold text, no colored cells, no formulas. It stores only the data itself.
Most people create a CSV file by starting in a spreadsheet program like Excel or Google Sheets, entering their data, and then saving the file with a .csv extension instead of .xlsx or .xls. The spreadsheet program handles the conversion automatically.
Key Takeaways
- Save your spreadsheet as CSV by using "Save As" and choosing CSV format from the file type menu, not by renaming the file extension yourself.
- Remove any formatting — bold, colors, formulas — before saving, because CSV strips it away and sometimes formatting causes import errors.
- Check that your first row contains column headers (like "Name", "Email", "Date") because most programs expect this and use it to map your data.
- Test your CSV file by opening it in a text editor to verify commas are in the right places and no data contains unescaped quotation marks or line breaks.
- If your data contains commas, quotation marks, or line breaks, wrap those cells in quotation marks so the program reading the file does not misinterpret them.
Preparing your data before you save
Before you convert to CSV, clean up your spreadsheet. Remove any empty rows or columns in the middle of your data — programs reading CSV expect a solid block with no gaps. Delete any rows you added just for visual spacing.
Check your column headers. The first row should contain clear, simple names for each column: "First Name", "Email Address", "Purchase Date". Avoid special characters like @ or / in header names. Some programs choke on headers that contain symbols or spaces, so use underscores or hyphens instead if the receiving program is strict — for example, "First_Name" instead of "First Name".
Look through your data for cells that contain commas, quotation marks, or line breaks. If a cell says "Smith, John" or contains a quote mark, that cell needs special handling when you save. Most spreadsheet programs handle this automatically when you save as CSV, but it is worth checking your file afterward to be sure.
Saving your file as CSV in Excel
Open your spreadsheet in Excel. Click "File" in the top left, then click "Save As". A dialog box opens asking where to save and what to name the file.
In the "Save as type" dropdown menu near the bottom of the dialog, click the dropdown arrow. Scroll down and select "CSV (Comma delimited) (*.csv)". Do not just rename your file to end in .csv — use the dropdown to tell Excel to convert the format. Then click "Save".
Excel may show a warning that says the file will lose formatting and features. Click "Yes" to continue. This is expected — CSV cannot store Excel formatting, and that is fine. Your data itself is preserved.
Saving your file as CSV in Google Sheets
Open your spreadsheet in Google Sheets. Click "File" in the top left menu, then hover over "Download" and click "Comma Separated Values (.csv)". Your browser downloads the file to your Downloads folder with the same name as your sheet.
Google Sheets does not show a warning because it assumes you know what you are doing. The conversion happens silently. Check your Downloads folder for the .csv file.
Testing your CSV file before you use it
Open your CSV file in a text editor to see what it actually contains. On Windows, right-click the file, click "Open with", and select Notepad. On Mac, right-click, select "Open With", and choose TextEdit. On Linux, use any text editor like gedit or nano.
The file should look like this:
First_Name,Last_Name,Email John,Smith,john@example.com Jane,Doe,jane@example.com
Each row is one line. Columns are separated by commas. If a cell contains a comma, it should be wrapped in quotation marks, like this:
First_Name,Last_Name,Email "Smith, John",Smith,john@example.com
If you see something unexpected — extra commas, missing data, or garbled characters — go back to your spreadsheet, fix the problem, and save as CSV again.
Handling special characters and problem data
If your data contains commas, quotation marks, or line breaks, the spreadsheet program should wrap those cells in quotation marks automatically when you save as CSV. But some programs do this inconsistently, so check your text editor view to be sure.
If a cell contains a quotation mark, it should appear as two quotation marks inside the cell's quotation marks. For example, a cell containing the text John "Jack" Smith should look like this in the CSV file:
"John ""Jack"" Smith"
If your data contains accented characters (like é or ñ) or characters from other languages, make sure your spreadsheet is set to save as UTF-8 encoding. Most modern programs do this by default, but if the receiving program shows garbled text, go back and check the encoding. In Excel, when you save as CSV, look for an "Encoding" dropdown and select UTF-8.
Common mistakes that break CSV files
The most common mistake is renaming a file from .xlsx to .csv without actually converting it. The file is still an Excel file inside — it just has the wrong extension. Always use "Save As" and select CSV from the file type menu.
The second mistake is leaving empty rows or columns in the middle of your data. If row 5 is blank and row 6 has data, the program reading the file may stop at row 5 and ignore everything below. Delete empty rows before you save.
The third mistake is including formatting that you think is important — bold headers, colored cells, merged cells. CSV cannot store any of this. If you need formatting to survive, do not use CSV. Use Excel or Google Sheets format instead, and ask the receiving program if it can read that format.
The fourth mistake is not checking what you saved. Always open your CSV file in a text editor to verify it looks right before you send it to another program or person.
Frequently Asked Questions
Can I open a CSV file in Excel?
Yes. Double-click a CSV file and it opens in Excel as a spreadsheet. Excel automatically converts the commas back into columns. You can edit it in Excel, but if you save it, make sure to save as CSV again if you want to keep the CSV format — Excel defaults to .xlsx.
What is the difference between CSV and Excel format?
CSV is plain text with no formatting. Excel (.xlsx) can store formatting, formulas, multiple sheets, and images. CSV is simpler and more portable — almost any program reads it. Use CSV when you need to move data between programs. Use Excel when you need formatting or formulas to stay intact.
Why does my CSV file look wrong when I open it in Excel?
Excel sometimes misinterprets the columns, especially if your data contains commas or unusual characters. Open the file in a text editor first to see what it actually contains. If the text editor shows it correctly, the problem is Excel's import settings, not your file. Try opening Excel first, then using "File" > "Open" and selecting your CSV file — this gives you more control over how Excel interprets the columns.
Can I add formulas to a CSV file?
No. CSV stores only data, not formulas. If you save an Excel file with formulas as CSV, the formulas disappear and only the results remain. If you need formulas, keep the file in Excel format.
What encoding should I use for my CSV file?
Use UTF-8. It handles English, accented characters, and most other languages correctly. When you save as CSV in Excel or Google Sheets, UTF-8 is usually the default. If the receiving program shows garbled text, ask them what encoding they expect and check your spreadsheet program's save options.