Export ranges from Excel to CSV with any delimiter and any character encoding

(Note: the video may not reflect the latest upgrades. Please use the guide below.)

Widespread use of plain text CSV files is easily explained – this format is supported by the majority of applications. So saving Excel spreadsheets as comma-delimited CSV files is a familiar operation. But what if you need to export all worksheets at once, or apply a different delimiter? Besides, saving as CSV automatically closes the source Excel file – and it becomes tiresome when you have to export data repeatedly.

The Export to CSV feature simplifies the conversion of XLSX (XLS) tables to CSV format:

  • Choose a delimiter: comma, semicolon, tab or a pipe
  • Set any character encoding: UTF-8, ISO or other
  • Add double or single quotes as qualifiers
  • Save all worksheets as separate CSV files at once

Before you begin, add Export to CSV to Excel

Export to CSV is one of the 20+ features within XLTools Add-in for Excel. Works in Excel 2019, 2016, 2013, 2010, desktop Office 365.

Get started with XLTools ribbon
– free trial gives you 14 days of full access to all features.

How to export a selected range from Excel to CSV

  1. On XLTools tab, click the Export button Choose Export to CSV from the drop-down list A window will open.
  2. Choose Selected range Select the range you want to export.
  3. Set processing options.

    Select the range you want to export to CSV
  4. Check the box Open file after export, if you want to see the result immediately.

    Note: the resulting CSV file will open either in Notepad, or in Excel. This depends which program you have as default to open *.csv files on your computer.
  5. Click OK Save the CSV file to your computer Done.

See that the source Excel file remains open and unchanged. Learn more.

See result: the selected range is saved as CSV

How to export one or multiple worksheets as CSV files at once

  1. On XLTools tab, click the Export button Choose Export to CSV from the drop-down list A window will open.
  2. Choose This worksheet or All worksheets in this workbook.
  3. Set processing options.

    Select all worksheets in this book and export to CSV
  4. Сheck the box Open file (folder) after export, if you want to see the result immediately.

    Note: the resulting CSV file will open either in Notepad, or in Excel. This depends which program you have as default to open *.csv files on your computer.
  5. Click OK Save the CSV file(s) to your computer Done.
See result: all worksheets in a workbook are saved as CSV

Another approach to save multiple worksheets as CSV files, is to use XLTools Workbook Organizer.

  1. On XLTools tab, click the Workbook Organizer button A window will open.
  2. See the list of all worksheets in all open workbooks Select one, all or just a few worksheets you want to export.
  3. Click the Save icon From the drop-down list select Save sheet(s) as separate files.

    Select the worksheets in Workbook Organizer and export them to CSV
  4. The window will open Choose to save as comma-separated values *.csv files Set the processing options.
  5. Click OK Save the CSV file(s) to your computer Done.

How to set processing options for exporting from Excel to CSV

You can define how to process and export values to CSV format:

Set processing options to export Excel data to CSV
  • Save values As formatted values

    Select this option, if you want to export values in the format that matches Excel formatting – as displayed in cells. For example, 10%, 12-Mar-2016, $40.

    Set option: export formatted values to CSV
  • Save values As actual values

    Select this option, if you want to export values in default format – as displayed in Excel formula bar. For example, 0.1, 3/12/2016, 40.00.

    Set option: export values to CSV
  • Check the box With qualifiers

    Check this option, if you need to set field qualifiers. You can type " (double quote), or ' (single quote), or any other character. The add-in will add the qualifiers to every field in the resulting CSV file. This option would be helpful if you process CSV files in legacy systems.

    Set option: add quotation marks or other characters as field qualifiers

    Additionally, you can check the box remove _ from data. This will keep the qualifier qualifier character only in the beginning and end of the field and remove it from all other instances.

    For example, if you add ' (single quotes) as field qualifiers, while some cells in your source Excel table already contain ' (single quotes or apostrophes), this will obscure the field limits in the CSV file. So, you can choose to remove any other single quote characters that are not qualifiers from the resulting CSV file. The source Excel file will remain intact.

    Set option: add qualifiers and remove this character from any other position
  • Check the box Unmerge cells and duplicate values

    Check this option, if your source table has merged cells. The add-in will duplicate values from merged cells into the corresponding places of the resulting CSV text string.

    Set option: unmerge cells when exporting to CSV

How to export from Excel to CSV with a semicolon, tab or pipe delimiter

You can save an Excel table as a CSV file with a standard comma delimiter, or choose a different suitable separator between values in the output CSV file:

  • comma
  • semicolon
  • tab
  • vertical bar (pipe)
Export Excel tables to CSV with a comma, semicolon, tab or pipe delimiter

How to export from Excel to CSV and set character encoding

You can choose the necessary character encoding for the output CSV file. Unicode UTF-8 is the most common encoding and is recommended as a default option:

  • Unicode (UTF-8)
  • ISO
  • ISCII
  • Unicode, or any other
Export Excel tables to CSV with any character encoding

Save Excel tables as CSV without closing Excel file

When you convert Excel files using "Save As…CSV", a message appears: "Some features in your workbook might be lost". Next, it seems that your source XLSX file is replaced by the CSV file. What actually happens is that your original Excel file is automatically closed, and the newly generated CSV file opens in Excel interface. So each time you save an Excel worksheet as CSV, you have to reopen the source Excel file again and again. It is rather inconvenient and brings the risk of losing unsaved changes to the source data.

The Export to CSV feature streamlines the XLSX to CSV routine:

  1. Notice that the source Excel file remains open and unchanged. Its extension did not change from *.xlsx to *.csv.
  2. Continue – uninterrupted – work with the source file, or export ranges to CSV again.

How to export data from Excel to other formats

If you want to export data to other formats, such as TXT, HTML, PDF or others, check out the Export Sheets tool. It will help you save multiple Excel worksheets as separate files in the format you need.

Any questions or suggestions?
Tracy BarberHi - It seems that the underscore (underline) formatting doesn't get saved when exporting to .CSV. I have tried so many different ways to get this done and none were successful. I want to import the .csv file into MySQL. All are set up to use UTF-8 but it doesn't seem to matter. Any ideas?
March 4, 2020 at 11:42
Tracy BarberBTW, I am trying to export underlined r, o, t and a few others that would take days to try to do a markup process and use css to underline the letters. The letters are parts of words. I have seen MySQL import such situations, it's just that middle step that is causing grief. Am I to understand that there is no >File Save As for .csv, Unicode, UTF-8 although many people claim that this works in posts found on Google. Clues, hints, HELP! would be appreciated. Thanks...
March 4, 2020 at 11:48
Maria BalobanovaHi Tracy, exporting formatted values should be no problem. Let us investigate why the format is not saved. We will need some more details from you (I’ve sent you an email). You are right, there is no native Excel functionality to quickly “Save file as…” csv with another encoding. There are workarounds for sure, but we wanted to develop something more intuitive. We use the Export to CSV feature ourselves quite often, that’s actually how the add-in appeared.
March 4, 2020 at 13:14
Robert SchoneThis is a great tool. :) But there is one more question left: I would like to export just specific rows to csv. In my first colum is specified which rows. I learned that I can do that with "Split Table". But that is not exactely what I am looking for. Isn't it possible to export just the rows which I can see (after filtering) and which are not empty?
March 25, 2019 at 12:57
Maria BalobanovaThank you for the kind feedback! Regarding the possibility to export only specific rows – this functionality is not yet supported. But that is a great idea for a future release. Thank you! In the meanwhile, here is what I can suggest: filter the table by your first column > copy the visible rows into a new sheet > export to CSV.
March 25, 2019 at 20:34
Scott HardwickIs it possible to set the qualifier? ex 100,900.83,,Hello instead appears as "100","900","83","","Hello"
February 25, 2019 at 21:51
Maria BalobanovaHi Scott, thank you for the question! Qualifier will be automatically applied 1) only to the data in cells that contain commas, 2) if you choose comma as a delimiter: E.g. cells in Excel (pipes represent cells): |100|900.83||Hello|Doe, John| When you use Export to CSV with a comma delimiter, you'll get: 100,900.83,,Hello,"Doe, John" Do you have a use case when a special option to set qualifiers to all data would be useful? If so, we can pin to our development pipeline.
February 26, 2019 at 13:45
How can we help?