Speed up your excel with this simple tip!
Load time = 4 times quicker
Save time = 2 times quicker
File size = 1.5 times smaller
This mainly applies to larger excel files. There is less difference for smaller excel files. See below for the basis of these results.
So what do I have to do to speed up my excel?
All you do is change the file format to xlsb. That’s it!
Tell me more!
Depending on your version of excel it will most likely default to saving as a xlsx (or potentially xls if it’s an older spreadsheet) file extension.
When saving your excel file, drop down the options menu next to “Save as type:”, just below the “File name:” box.
Choose the option called “Excel Binary Workbook (*.xlsb)”. See the screen grab below for an illustration:
So what exactly is xlsb?
xlsb is a binary file format. It’s the coding used to save the excel file, although this will be completely irrelevant to the vast majority of excel users. Binary format is an alternative to the open standard XML programming language. The binary format is specific to excel.
Will it make any difference to my excel usage?
There is no functional difference to your excel usage that you will notice in practice.
What does a tomato have to do with excel?
Nothing! I just really liked the picture! It was a nice big to small analogy and looked more interesting than a screen shot of my folder showing reduced excel file sizes!
Are there any limitations?
Yes there are a few although these will not impact on the majority of excel users.
(1) Other spreadsheet packages will be unable to read the file. Some people import excel files into other packages, such as open office. These are now unlikely to be able to read your file. If you just stick to excel then this is not an issue. You can always get round this limitation by ‘saving as’ xlsx or xls formats.
(2) The default format of xls or xlsx supports Extensible Markup Language (XML) which is an international standard format that can used across IT systems, can be viewed with a text editor as well as other uses. Xlsb is a binary file format which does not follow these standards and cannot be read or manipulated by other IT systems. This may result in a compatibility problem if you link systems together, for example import/export excel into your accounting system. As with the above limitation this will have no impact on the majority of regular excel users.
(3) Although xlsb supports macros, you may not be able to tell if there are macros in the file, unlike xlsx has to be changed to xlsm to support macros. It is therefore advisable to change your macro settings to automatically disable macros with prompt to avoid any macro issues. At the same if you use VBA regularly you may like this feature as you don’t have to change the file type to xlsm when adding a macro &/or VBA code. If you don’t use macros or VBA then this is not an issue.
(4) There have been some reports of issues when moving between versions of excel, especially in Excel 2007. I believe Microsoft made some enhancements in 2010 onwards. So to be safe it will be safer to use this format in versions of excel after 2007 e.g. 2010 or 2013.
Do you like this tip and want more tips?
(1) Sign up below to receive free notifications of other tips by email.
(2) Check out our open course on spreadsheet skills. Below is a link to our public excel course:
We also hold in-house corporate excel training, see link below:
Source of test results
*Tested in 2012 on a generated worksheet with 10,000 rows * 1,000 columns = 10,000,000 (10^7) cells of simple chained =…+1 formulas by mike7952 an excel forum expert from Florida, USA.