«

»

Dec 14

XLS or XLSX file ?

With the release of Excel 2007, the extensions of Excel files (or Office in general) were changed.

Previously, it was possible to save workbooks in xls. Since Excel 2007 you can also save your workbooks in xlsx or xlsm. It is now 6 years since the extensions xlsx and xlsm were created but many people are still saving their Excel file with the xls extension.

This article will present the main reasons why it is strongly advised to convert all your workbooks to xlsx (or xlsm).

Xlsx compresses the file

If you have on your computer an xls file and you save it in xlsx, you will see that the size has been significantly reduced. This is due to the fact that the new Excel file extension is written in XML standard, hence the x at the end of the extension xlsx.

xlsx_1The XML code is lighter than the previous mode of saving and if you want to visualize the content of an xlsx file, you just have to change the extension to zip. Then, you can open this zip file and you display the XML structure of  your Excel file.

 

 

 

Each xml files can be opened. But be careful ! If you edit the xml files, you may destroy the architecture of the XML file and you will no longer be able to open your Excel file (after renaming the file zip file xlsx).

xlsx_2_en

Protection against macros

We have seen that the last x of a xlsx file indicates that the file is compressed and respects the XML standard. The M, of a xlsm file, indicates that the file contains macros.

A macro is a program written by an end-user (and not the developer of Excel) to provide automation (opening files, data comparison, data aggregation across multiple sheets, ...) but also a program may cause malfunctions. In addition, a macro can run directly when you open an Excel file, without prompting the user.

With an xls file, you don't know whether your workbook contains macros or not. Whereas with a xlsx file you are certain that there is no macro. If you do the test to write a small macro and then save your workbook with the xlsx extension, you will lose all your work without the possibility to recover your program.

Xlsx workbooks have no macro, unlike a xlsm file.

excelfix_en

Extension of the working area.

xlsx_3In a Xls workbook, the row limit is 65,536 (216) and 256 columns (28) which corresponds to the column IV.

 

 

 

 

 

 

 

xlsx_4Now with xlsx workbooks (and xlsm), the limits are 1,048,576 rows (220) and 16,384 columns (214) or the column XFD.

 

 

 

 

 

 

Support Tables

The concept of Table appears with the Excel 2007 version. This new concept  improves handling of large amounts of data (Big Data) like for Business Intelligence .

Before Excel 2007, the selection of a range of cells could only be done with references. As in the following image shows you, the formula VLOOKUP reads the information in the range of cells B5:E15.

 xlsx_5_en

The problem with this method is that if your data source expands with the insertion of new lines, you must change the references used in your formulas to integrate the new data.

With the use of a Table, the range of data automatically adapts to the addition of new lines and your formulas are calls directly to named ranges.

xlsx_6_en

Only a Xlsx file can interpret the concept Table and dynamic ranges.

New color and new themes

With the xlsx files, you have a bigger range of color. With a xls file, you can only use 56 colors but with an xlsx file, you have all the combinations possible RGB (red, blue, green).

Theme_4

In an xlsx file, the colors are grouped into themes. With a new theme, you change the general color of your workbook, and the font used.

Embed a workbook in a Web page

As is the case in all the functions' pages of this site, all the exercises proposed are not images but are real Excel workbooks where you can reproduce the examples explained in each page. This is made ​​possible because the xlsx files are based on XML structure, then it is possible to easily embedded in web pages (as is the case with the following example presented in section IF function ).

Once you see the indication Excel Web App in the status bar, that means it is a workbook embedded in your web page . But, it is not always possible to interact with the workbook, everything depends of the rights given by the author of the workbook. In the following example, you can make calculations directly into the cells, but you can not save your changes (only the owner of the workbook can do it).

To embed an Excel file in a web page, you must save your workbook in your Skydrive. The next page shows you how to perform the integration.

Conclusion

An Excel workbook saved as xlsx has many advantages over the old format xls. It is therefore recommended that you migrate all your xls files to the new format xlsx to benefit in Excel 2007, Excel 2010 and Excel 2013.

Tags:  ,  ,  , 

1 comment

  1. ana

    I want to do some easy excersize of subtotal function

Leave a Reply