{"id":3633,"date":"2021-10-04T00:14:45","date_gmt":"2021-10-04T07:14:45","guid":{"rendered":"https:\/\/SUMMALAI.COM\/?p=3633"},"modified":"2021-10-04T00:14:49","modified_gmt":"2021-10-04T07:14:49","slug":"understand-excel-file-types-xlsx-xlsm-xlsb-and-more","status":"publish","type":"post","link":"https:\/\/SUMMALAI.COM\/?p=3633","title":{"rendered":"Understand Excel File Types, XLSX, XLSM, XLSB, and More."},"content":{"rendered":"\n

When you save an Excel workbook for the first time or use the \u201cSave As\u201d function, you are asked for a folder, file name as well as the file type. By default, Excel suggests the XLSX file type (unless your file doesn\u2019t have VBA macros). The window looks similar to the screenshot on the right side.<\/p>\n\n\n\n

So which file type are you going to use? The answer \u2013 like so often: It depends. Before we conclude in a decision tree, we we take a look at the most important file extensions in Excel with their advantages and disadvantages first.<\/p>\n\n\n\n

XLSX<\/h3>\n\n\n\n

The \u201cdefault\u201d file extension is XLSX. The large majority of Excel workbooks uses this format these days. Microsoft says in the Excel help text about the XLSX file format:<\/p>\n\n\n\n

The default XML-based file format for Excel 2007-2013. Cannot store Microsoft Visual Basic for Applications (VBA) macro code [\u2026].<\/p><\/blockquote>\n\n\n\n

Most of the time you use the XLSX format: It\u2019s save (can\u2019t store malicious code), has the maximum number of rows and columns and is best known. XLSX is available since Excel 2007 and replaces the old XLS file type. It uses the open XML standard so which is documented well.<\/p>\n\n\n\n

Knowing these quick facts, there are the following advantages and disadvantages for the XLSX file format:<\/p>\n\n\n\n

Advantages<\/th>Disadvantages<\/th><\/tr><\/thead>
The default file format and because of that the best known and widest spread file type.Can use up to 2^20 (1048576) rows and 2^14 (=16384) columns.Uses the open file format and is in general accessible with other applications.Secure because it can\u2019t save VBA macros.Can use and save all the functions available in Excel.<\/td>A little bit contradicting: But not being able to include VBA macros can be an advantage and disadvantage at the same time.Needs more disk space than other file formats (e.g. XLSB).Can\u2019t be opened with Excel versions before 2003 (e.g. 2003, at least without any further conversion of Excel extension\/ add-in).<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

XLS<\/h3>\n\n\n\n
\"advice,<\/figure>\n\n\n\n

The XLS file type has been the default Excel file extension and format before Excel 2007. In Excel 2007, XLSX replaced the XLS format. Therefore, XLS is kind of outdated right now. In the daily life you won\u2019t find it often any more. But some data base tools still export data as XLS files.<\/p>\n\n\n\n

The XLS file type has some disadvantages towards the newer file types XLSX or XLSM: It\u2019s rather unsafe as it can contain VBA macros with malicious code. Also, it needs more disk space than the other file types and at the same time has less rows and columns.<\/p>\n\n\n\n

On the other side the only advantage: You can easily open XLS files with older versions of Excel.<\/p>\n\n\n\n

Advantages<\/th>Disadvantages<\/th><\/tr><\/thead>
Can be opened with Excel versions before 2007 (e.g. 2003).<\/td>Unsafe as it can contain VBA macros.Needs more disk space than most of the other file formats (e.g. XLSX, XLSB).Can\u2019t use all newer Excel functions, e.g. formulas or functionality (you will be warned when saving an XLS file though).Can only use up to 65,536 (2^16) rows and 256 (2^8) rows.Outdated\u2026<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

XLSM<\/h3>\n\n\n\n

If you include VBA macros in your XLSX file, you will be asked to change the file extension to XLSM. Only that way you can save your macro within the Excel file (there are other file types possible, e.g. XLSB \u2013 but you can\u2019t use XLSX for VBA macros). Therefore, it has most of the advantages and disadvantages of the XLSX file type. Positive: You can immediately identify Excel files with VBA macros.<\/p>\n\n\n\n

Advantages<\/th>Disadvantages<\/th><\/tr><\/thead>
The default Excel file type for workbooks with VBA macros.Can use up to 2^20 (1048576) rows and 2^14 (=16384) columns.Uses the open file format and is therefore accessible with other applications.Can use and save all the functions available in Excel.Uses the open XML standard.<\/td>Unsafe: Can contain malicious code within the VBA part.Needs more disk space than other file formats (e.g. XLSB).Can\u2019t be opened with Excel versions before 2003 (e.g. 2003, at least without any further conversion of Excel extension\/ add-in).<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

XLSB<\/h3>\n\n\n\n
\"advice,<\/figure>\n\n\n\n

Many people don\u2019t know that there is an actual alternative to the XLSX and XLSM file type: The XLSB file extension. XLSB files store the data a little bit different than the XLSX or XLSM file types: They don\u2019t use the XLM file structure. Instead, XLSB files try to save disk space because the data is stored in the binary structure. The main difference: Binary files are computer- but not human-readable. For more information on binary files in general, please refer to this Wikipedia entry<\/a>.<\/p>\n\n\n\n

The main disadvantage: Binary Excel files can contain VBA macros. So unless you don\u2019t know the origin of a file, please consider well before opening them. Besides that: All the other disadvantages seem minor.<\/p>\n\n\n\n

Advantages<\/th>Disadvantages<\/th><\/tr><\/thead>
Smaller file size.Faster opening and closing of files.Formulas with more than 8192 characters allowed.Can use and save all the functions available in Excel.<\/td>Security: Files can contain VBA code whereas XLSX files can\u2019t.You can\u2019t change the Excel ribbon.Some third party tools (e.g. OpenOffice) might not be able to open your file.You can\u2019t open your files with Excel 2003 or earlier (which nowadays should not be a problem any more\u2026).Not very well known. So people might get confused receiving XLSB files from you.<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

CSV<\/h3>\n\n\n\n
\"csv,<\/figure>\n\n\n\n

CSV stands for comma separated values. These files are basically plain text \u2013 so there are no formulas or formatting. CSV is often used for data exchange. Excel provides the functionality to import or even directly open CSV files.<\/p>\n\n\n\n

The basic application for CSV files: Import or export data. Data base programs often export data as CSV files. Also if you want to transport Excel data into a database program, often the CSV file type works.<\/p>\n\n\n\n

Advantages<\/th>Disadvantages<\/th><\/tr><\/thead>
Smaller file size.Faster opening and closing of files.Unlimited rows and columns.No code or macros possible and therefore save to open.<\/td>CSV files are basically text documents and therefore can\u2019t contain formulas or formatting, pure data.No more than one \u201cworksheet\u201d.<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

ODS<\/h3>\n\n\n\n

ODS stands for Open Document Spreadsheet. The file type is designed to work on both Excel and other applications, e.g. LibreOffice. From the Excel point of view, ODS doesn\u2019t support all the features. Text and data is usually saved well though. But formatting and many other features (for example charts, tables, conditional formatting) might run into problems.<\/p>\n\n\n\n

Advantages<\/th>Disadvantages<\/th><\/tr><\/thead>
Can be opened in Excel and LibreOffice.<\/td>You might lose formatting or features which are not supported. For a detailed lists of the features supported, please refer to this website<\/a>.<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

XLSX (Strict)<\/h3>\n\n\n\n

Besides the regular XLSX format, Excel offers to save your Excel workbook as \u201cStrict Open XML Spreadsheet\u201d. So what is the difference between those two? Basically, the two file types use the same structure. But:<\/p>\n\n\n\n

The Strict variant has less support for backwards compatibility when converting documents from older formats.<\/p><\/blockquote>\n\n\n\n

Furthermore:<\/p>\n\n\n\n

The Strict variant of XLSX disallows a variety of elements and attributes that are permitted in the more common Transitional variant [\u2026]<\/p><\/blockquote>\n\n\n\n

Advantages<\/th>Disadvantages<\/th><\/tr><\/thead>
Same as XLSX.Additionally: Opening in other applications than Excel might work better.<\/td>Some backward compatibility problems might occur.<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

PDF<\/h3>\n\n\n\n
\"pdf,<\/figure>\n\n\n\n

As you\u2019ve probably heard of PDF files, we won\u2019t go much into detail here. PDF stands for portable document format and has the reputation of not easily being manipulated. Also, it\u2019s quite save to say that how a PDF file looks on your computer, it will most probably look like this on most other computers.<\/p>\n\n\n\n

And that\u2019s it for the advantages. I recommend using it when you present your final results in the following cases:<\/p>\n\n\n\n