Changing format to XLSB - big excel file with many formulas, links etc.

%3CLINGO-SUB%20id%3D%22lingo-sub-2626638%22%20slang%3D%22en-US%22%3EChanging%20format%20to%20XLSB%20-%20big%20excel%20file%20with%20many%20formulas%2C%20links%20etc.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2626638%22%20slang%3D%22en-US%22%3E%3CP%3EHi%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWe%20are%20working%20with%20big%20excel%20files%2C%20around%2030MB-200MB.%3C%2FP%3E%3CP%3EWe%20used%20standard%20XLSB%20file%20format%20which%20is%20default.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E1.%20As%20I%20know%20XLSB%20offers%20better%20performance%20and%20maybe%20other%20advantages%20also%20which%20is%20a%20much%20smaller%20file.%3C%2FP%3E%3CP%3E1.1%20If%20XLSB%20is%20better%20why%20Microsoft%20uses%20XLSX%20by%20default%3F%3C%2FP%3E%3CP%3E2.%20If%20we%20save%20all%20our%20big%20excel%20files%20(with%20formulas%2C%20links%2C%20etc.)%20does%20the%20data%20structure%2C%20formulas%2C%20links%20remain%20unchanged%3F%20This%20is%20a%20big%20deal%20for%20us%20so%20any%20data%2Fformat%20loss%20will%20be%20very%20bad.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20just%20want%20to%20know%20why%20XLSX%20is%20used%20by%20default%3F%20and%20if%20we%20change%20files%20to%20XLSB%20will%20the%20data%20remains%20unchanged%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2626638%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2626796%22%20slang%3D%22en-US%22%3ERe%3A%20Changing%20format%20to%20XLSB%20-%20big%20excel%20file%20with%20many%20formulas%2C%20links%20etc.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2626796%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F469173%22%20target%3D%22_blank%22%3E%40giors600%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20.xlsx%20format%20does%20not%20support%20macros%20(VBA%20code)%2C%20hence%20it%20is%20inherently%20safer%20than%20.xlsm%20and%20.xlsb.%20Since%20most%20users%20only%20create%20small%20workbooks%2C%20the%20file%20size%20doesn't%20really%20matter%20for%20them.%3C%2FP%3E%0A%3CP%3ETherefore%20.xlsx%20as%20default%20works%20well%20for%20the%20majority%20of%20Excel%20users.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20you%20work%20with%20very%20large%20workbooks%2C%20as%20you%20do%2C%20it%20makes%20sense%20to%20use%20.xlsb%20as%20default%20format.%20It%20won't%20affect%20the%20contents%20of%20the%20workbooks%2C%20just%20the%20way%20the%20workbooks%20are%20stored.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2626852%22%20slang%3D%22en-US%22%3ERe%3A%20Changing%20format%20to%20XLSB%20-%20big%20excel%20file%20with%20many%20formulas%2C%20links%20etc.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2626852%22%20slang%3D%22en-US%22%3ERight.%20By%20saying%20%22inherently%20safer%22%20you%20actually%20mean%20viruses%20which%20sometimes%20come%20from%20and%20are%20stored%20in%20malicious%20Excel%20files%20%3F%20or%20you%20are%20referring%20to%20other%20safety%20concerns%3F%20(like%20Excel%20stability%2C%20data%20stability%20with%20XLSB%20files)%3CBR%20%2F%3E%3CBR%20%2F%3ESo%20generally%20if%20you%20are%20working%20with%20big%20files%20and%20there%20is%20no%20security%20concerns%2C%20you%20should%20used%20XLSB%20format%3F%20As%20you%20know%20computers%20struggle%20opening%2Fworking%20with%20big%20files%20so%20with%20XLSB%20and%20it's%20advantage%20of%20smaller%20file%20sizes%2C%20must%20improve%20performance%20I%20think.%3CBR%20%2F%3EI%20understand%20that%20contents%20doesn't%20change%2C%20only%20the%20format.%20Many%20thanks%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2626855%22%20slang%3D%22en-US%22%3ERe%3A%20Changing%20format%20to%20XLSB%20-%20big%20excel%20file%20with%20many%20formulas%2C%20links%20etc.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2626855%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F469173%22%20target%3D%22_blank%22%3E%40giors600%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EBy%20safer%2C%20I%20meant%20that%20a%20.xlsx%20workbook%20by%20definition%20cannot%20contain%20VBA%20code%2C%20so%20no%20macro%20viruses.%3C%2FP%3E%0A%3CP%3EThere%20is%20no%20stability%20concern%20with%20.xlsb%20files.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2626863%22%20slang%3D%22en-US%22%3ERe%3A%20Changing%20format%20to%20XLSB%20-%20big%20excel%20file%20with%20many%20formulas%2C%20links%20etc.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2626863%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F469173%22%20target%3D%22_blank%22%3E%40giors600%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESide%20effect%20could%20be%20with%20Power%20Query%20if%20you%20use%20it.%20For%20example%2C%20if%20you%20have%20table%20within%20the%20file%20with%20formatted%20numbers%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20117px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F301735i1C3B45A437E64276%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EPower%20Query%20on%20xlsx%20file%20returns%20actual%20numbers%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20450px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F301736i96D6C85FE5487EE6%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Ebut%20Power%20Query%20on%20binary%20file%20returns%20masked%20by%20format%20data%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20466px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F301737i0C5275D7AE2234CB%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Eand%20ignores%20structured%20tables.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

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

4 Replies

@giors600 

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.

Right. By saying "inherently safer" you actually mean viruses which sometimes come from and are stored in malicious Excel files ? or you are referring to other safety concerns? (like Excel stability, data stability with XLSB files)

So generally if you are working with big files and there is no security concerns, you should used XLSB format? As you know computers struggle opening/working with big files so with XLSB and it's advantage of smaller file sizes, must improve performance I think.
I understand that contents doesn't change, only the format. Many thanks

@giors600 

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.

@giors600 

Side effect could be with Power Query if you use it. For example, if you have table within the file with formatted numbers

image.png

Power Query on xlsx file returns actual numbers

image.png

but Power Query on binary file returns masked by format data

image.png

and ignores structured tables.