This guide will explain how to fix the issue where your Excel spreadsheet is not saving as a CSV with commas.
Compared to export formats such as .xlsx, the CSV format can be read by more software. This includes plain text editors such as your default Notepad application.
The CSV (Comma Separated Values) format is popular due to its simplicity and reliability. CSV files are human-readable and also easy to edit manually outside of the Excel application.
Excel offers a quick way to save the current file in the .csv file format. However, the user may encounter issues during the saving process.
One common issue with saving as a CSV is that the exported file will only contain data from the current active sheet. This is because CSV does not have support for multiple sheets.
This may be the reason why certain data is not showing up in your CSV file. You will have to either save each tab manually as a CSV or use an Excel Macro to do this automatically.
If you want to save an Excel file as a comma-delimited CSV, we must ensure that the system separators for numerical values are appropriate.
Most users apply a period as a decimal separator. In the number ‘1,000,000.00’, the comma character is used as a thousands separator.
We can find the option to change the system separator in the Advanced Excel Settings menu under Editing options.
Now that you know some methods to fix issues saving your file in the CSV format, let’s take a look at some real examples.
A Real Example of Saving CSV Files in Excel
In the following section, we will look at a few ways we can save CSV files in Excel.
First, let’s take a look at the different types of CSV formats you can export your file to.
Excel provides four different CSV file formats. The CSV (comma delimited) is the most reliable file format if you want to use the file in another Windows program.
Users can use CSV UTF-8 (Comma delimited) for files that contain non-ASCII characters. This is because the first option removes non-ASCII characters during the export process.
The other two CSV format options, CSV (Macintosh) and CSV (MS-DOS), are best used for their respective operating systems.
While the CSV file typically uses a comma as a delimiter, you may find CSVs with other characters as delimiters. For example, some CSV files use the semicolon or tab character to separate values.
Do you want to take a closer look at our examples? You can make your own copy of the spreadsheet above using the link attached below.
If you’re ready to try saving your Excel data as a CSV file, head over to the next section to read our step-by-step tutorial on how to do it!
How to Fix File Not Saving as CSV in Excel
This section will guide you through each step you need to fix the issue of your file not saving as a CSV. You’ll learn how to set the system separators for your Excel program.
Follow these steps to fix the CSV saving issue:
- First, navigate to the File tab in your current spreadsheet.
- Next, click on the Options button found at the bottom of the left sidebar.
- In the Excel Options dialog box, navigate to the Advanced tab. Under the section labeled ‘Editing options’, select the option Use system separators. Set the Decimal separator as the dot ’.’ and the Thousand separator as the comma character. Click on OK to apply these changes.
- In the File tab, click on the Save As option.
- After typing in your desired file name, click on the file type dropdown icon. Select the CSV UTF-8 option from the dropdown menu. This format is best for data with non-ASCII characters.
- You may open the file using Notepad to confirm that your data has been saved as a CSV successfully.
These are all the steps needed to fix files not saving as a CSV with commas in Excel.
Frequently Asked Questions (FAQ)
- Why should I change the Decimal separator?
The Decimal separator must be different from the list separator used when exporting CSVs. Some countries use the comma as a decimal separator, which may cause issues when exporting the file to CSV.
This step-by-step guide should be all you need to save your file as a CSV with commas in Excel. Our guide shows how to use the Excel Options to indicate the decimal and thousands separator explicitly.
After reading our guide, you should now understand how to select the proper output format yourself in the Save As dialog menu.
Saving files in CSV format is just one of several ways you can export your data in Microsoft Excel. Our website covers hundreds of other Excel methods and functions that you can apply to your own spreadsheets.
With so many other Excel functions available, you can surely find one that suits your use case.
Are you interested in learning more about what Excel can do? Subscribe to our newsletter to look into the latest Excel guides and tutorials from us.