Aug 09 2021 01:54 AM
Hi
We are working with big excel files, around 30MB-200MB.
We used standard XLSB file format which is default.
1. As I know XLSB offers better performance and maybe other advantages also which is a much smaller file.
1.1 If XLSB is better why Microsoft uses XLSX by default?
2. If we save all our big excel files (with formulas, links, etc.) does the data structure, formulas, links remain unchanged? This is a big deal for us so any data/format loss will be very bad.
I just want to know why XLSX is used by default? and if we change files to XLSB will the data remains unchanged?
Thanks
Aug 09 2021 02:46 AM
The .xlsx format does not support macros (VBA code), hence it is inherently safer than .xlsm and .xlsb. Since most users only create small workbooks, the file size doesn't really matter for them.
Therefore .xlsx as default works well for the majority of Excel users.
If you work with very large workbooks, as you do, it makes sense to use .xlsb as default format. It won't affect the contents of the workbooks, just the way the workbooks are stored.
Aug 09 2021 03:01 AM
Aug 09 2021 03:05 AM
By safer, I meant that a .xlsx workbook by definition cannot contain VBA code, so no macro viruses.
There is no stability concern with .xlsb files.
Aug 09 2021 03:10 AM
Side effect could be with Power Query if you use it. For example, if you have table within the file with formatted numbers
Power Query on xlsx file returns actual numbers
but Power Query on binary file returns masked by format data
and ignores structured tables.