Choosing the wrong Excel file format can lead to lost features or compatibility errors. The main formats are XLSX, XLSM, and CSV, each designed for a specific purpose. This article explains the technical differences between these three common formats. You will learn which format to use for data sharing, macros, or simple lists.
Key Takeaways: Excel File Format Selection
- .XLSX (Excel Workbook): The default modern format for most workbooks, supporting all features except macros.
- .XLSM (Excel Macro-Enabled Workbook): Saves and runs VBA macro code, required if your sheet contains automation.
- .CSV (Comma Separated Values): A plain-text format for exchanging raw data with almost any software system.
Understanding the Core Excel File Formats
Excel uses different file extensions to indicate the type of data and features stored inside. The format determines if you can use formulas, charts, pivot tables, or macros. Picking the correct one ensures your file opens correctly for you and anyone you share it with.
What is the XLSX Format?
The XLSX format is the standard Excel workbook introduced with Excel 2007. It is based on the Open XML standard, which stores data as a collection of XML files inside a compressed ZIP container. This structure makes files smaller and more recoverable than the old XLS binary format. XLSX files support all modern Excel features like multiple sheets, advanced formulas, conditional formatting, charts, and pivot tables. However, they cannot contain VBA macro code. If you try to save a workbook with macros as XLSX, Excel will warn you and strip the macros out.
What is the XLSM Format?
The XLSM format is identical to XLSX in its Open XML structure but includes a special component for macro storage. The “M” stands for Macro-Enabled. When you record or write a VBA macro, you must save the file as XLSM to preserve that code. The file itself is a ZIP package containing XML parts for worksheet data, plus a \`vbaProject.bin\` file that holds the compiled macro project. This format is necessary for any automation, from simple button clicks to complex data processing scripts.
What is the CSV Format?
CSV is not a true Excel format but a plain-text data interchange standard. A CSV file contains only raw values separated by commas, with each row on a new line. It does not support sheets, formulas, formatting, macros, or any other Excel-specific elements. When Excel opens a CSV, it interprets the commas and loads the data into a single worksheet. This format is universally supported by databases, programming languages, and other spreadsheet applications, making it ideal for exporting and importing raw data sets.
Steps to Save and Convert Between Formats
You can change a workbook’s format at any time using the Save As command. The process is the same, but the outcome depends on your starting file type.
- Open your workbook in Excel
Launch the file you want to save in a different format. - Select File > Save As
Click the File tab on the ribbon, then choose Save As from the menu. - Choose your save location
Navigate to the folder where you want the new file saved. - Select the file type from the dropdown
Click the “Save as type” dropdown box below the file name field. Scroll and select “Excel Workbook (*.xlsx)”, “Excel Macro-Enabled Workbook (*.xlsm)”, or “CSV (Comma delimited) (*.csv)”. - Click Save
Excel will save a copy of your file in the new format. If you are saving a workbook with macros as XLSX, a warning dialog will appear asking you to confirm.
Common Mistakes and Format Limitations
Using the wrong format often leads to lost work or confusion. Be aware of these specific limitations.
Macros Disappear After Saving as XLSX
If your workbook contains VBA code and you save it as an XLSX file, Excel removes all macros permanently. You will see a warning message: “The following features cannot be saved in macro-free workbooks…”. To keep your macros, you must choose the XLSM format from the Save As type list. There is no way to recover macros from an XLSX file once they are removed.
CSV Files Only Save the Active Sheet
When saving as CSV, Excel exports only the data from the currently active worksheet. All other sheets in the workbook are ignored. Any formulas are converted to their current calculated values, and all formatting is lost. If you need to save data from multiple sheets, you must save each sheet as a separate CSV file.
Opening CSV Files Can Corrupt Data Formats
Excel may misinterpret data types when opening a CSV. For example, long numeric strings like product codes can be converted to scientific notation. Leading zeros in numbers are often dropped. To prevent this, import CSV data using the Data > From Text/CSV tool. This opens the Power Query Editor, where you can manually assign the correct data type to each column before loading.
XLSX vs XLSM vs CSV: Key Differences
| Item | .XLSX | .XLSM | .CSV |
|---|---|---|---|
| Primary Use | Standard workbooks without macros | Workbooks containing VBA macros | Raw data exchange between systems |
| File Structure | Open XML (ZIP package) | Open XML with VBA project | Plain text, comma-separated values |
| Macro Support | No | Yes | No |
| Multiple Worksheets | Yes | Yes | No, single sheet only |
| Preserves Formulas | Yes | Yes | No, saves values only |
| Preserves Formatting | Yes | Yes | No |
| File Size | Small to medium | Medium to large | Very small |
| Universal Software Compatibility | High | Medium (requires macro security approval) | Very high |
You can now select the correct Excel format for data analysis, automation, or export. Use XLSM when you need to run a macro that sorts data or generates a report. For your next project, try using Data > From Text/CSV to import a CSV file while controlling column data types. Remember that pressing F12 is the keyboard shortcut to open the Save As dialog quickly.