Differences between xls, xlsx, xlsm, xlsb and csv
Introduction to 5 excel worksheet formats
".xls" - Early excel worksheet
In 1987, Microsoft developed the first Excel spreadsheet for the Windows system, and the product has been updated more than a dozen versions so far. Up to now, the highest version is Excel 2019, and the ".xls" format is the file format generated by Excel 2003 and previous versions. Its biggest feature is that it has only 65536 rows and 256 columns. There is a huge difference in the amount of data stored with Excel 2007 and later versions (1048576 rows, 16384 columns). It can be said that the xls format file is a product in the evolutionary history of Excel. Nowadays, it is basically replaced by ".xls", but there are still many companies using Excel 2003 to save costs.
".xlsx" - Excel 2007 and later worksheet format
".xlsx" is the extension of Microsoft Office Excel 2007/2010/2013/2016/2019, and it is the mainstream format of Excel spreadsheets. Compared with ".xls", theoretically it can store 1048576 rows and 16384 columns of data.
".xlsm" - macro-enabled workbook
".xlsm" is basically the same as ".xlsx" and can be opened with Excel 2007 and above. The only difference is that ".xlsm" starts the macro. Under normal circumstances, if we record a macro in the ".xlsx" file, or insert a section of VBA code, it cannot be saved normally. Only save the table in the ".xlsm" format. Simply understand, ".xlsx" cannot save macro (VBA), but ".xlsm" can.
".xlsb" is Excel's binary text format. Compared with the previous format, the table is smaller, opens faster, and can save macro codes. The disadvantage is that the compatibility is not as good as the ".xlsx" format.
".csv" - comma separated value file
The ".csv" comma-separated value file format stores tabular data (numbers and text) in plain text, which can be opened with Excel software. If you want to save the Excel table as ".csv" format, you need to pay special attention. When saving as ".csv" format, because it is in plain text format, the formula, font color and other settings in the previous table will be discarded, and the ".csv" file only retains one insert. A major feature of ".csv" is that, in principle, the amount of stored data is unlimited. Unlike Excel, which can only store 1048576 rows×16384 columns of data at most, but when the amount of data is too large, open with Excel, and the extra part will be truncated. At this time, you need to use other software to read the data, such as SQL, Python, R.
Differences in file size of the 5 formats
In addition to some functional differences, the biggest difference between these types of tables is the file size. For the same table content, the ".xlsb" format has the smallest file size, followed by the ".xlsx" and ".xlsm" formats, then the ".csv", and finally the xls format. Generally speaking, for the content of the same table, the table size in ".xls" format is 3.6 times that of ".xlsx". So, friends who are still using Excel 2003 version, it is time to upgrade your Excel version.
Conversion between the five formats
The five formats can be converted to each other. You need to install Excel 2007 and above. After opening the form, click "Save as", drop down the "Save as type" at the bottom, you can find that all the above five formats are in it and need to be saved Just click to select the type. The only thing to note is that if you save it in the ".xls" format, the data in the original table that exceeds 65536 rows and 256 columns will be truncated. After all, the ".xls" format table can only store 65536 rows and 256 columns; save as In the ".csv" format, the original table formulas and format settings will be lost, and only one insert will remain.
To sum up, ".xls" and ".xlsx" are basic, common and commonly used tables. ".xlsm" is a more advanced form with code inside that can perform certain functions automatically. xlsb is a more basic table, closer to computer language than ".xls" and ".xlsx". Hope that after reading this article, your understanding of the relationship between them can be clearer.