Exporting a sheet from Microsoft Excel to a CSV file is straightforward. But how to export selected cells to a CSV file?
The short answer is you can't export selected cells in CSV quickly. Exporting a selection to a CSV file or another Excel file is not directly possible. Here is an alternative solution: copy the data to another sheet and export the sheet to a CSV file.
Step 1: Copy-Paste selected cells
Select the cells you want to export. And copy the data to your clipboard (ctrl+c or Menu Edit => Copy)
Export only visible cells (without hidden columns)
If you have hidden columns, Excel will add their content to your clipboard. This is not what we want. We want to export only our selected and visible cells.
To prevent hidden columns to be copied, open the Go to Special menu in Find & Select.
And select Visible cells only. It will tell Microsoft Excel we only want to copy the visible cells.
Step 2: Create a new sheet and paste your data
Then, create a new sheet and paste your data (ctrl + v or Menu Edit => Paste)
Add columns to have CSV headers
CSV files are used to store structured data, and CSV headers add context to know what your CSV contains.
CSV headers are optional. But if you need to import your CSV into another software or clean it with a CSV editor, you better have them.
Just copy - paste the columns from the first sheet or write them directly as the first line in the new sheet.
Step 3: Export sheet to CSV
The last step is to export the new Excel sheet to a CSV file. Once you are on the new sheet, select Save As in the File menu.
Important: Set the active sheet to the sheet to export!
Then change the File format to CSV UTF-8 (Comma-delimited) (.csv). And click Save.
A warning message will pop up with the message "This workbook cannot be saved in the selected file format because it contains multiple sheets.".
Bypass the warning message by clicking on OK. Microsoft Excel tells you that it can't export all sheets to a single CSV. Only your active sheet will be exported to a CSV file. This is what we want.
Edit it with a CSV Editor
If you need to further clean your CSV data, use a CSV Editor such as Datablist.
Datablist provides advanced tools to deduplicate data, consolidate several CSV files, or clean your data.