Sometimes an Excel document becomes too large to email or share via your filesharing service. Or it just takes forever to calculate. How can you speed up your spreadsheet or reduce the size of it?

Obviously, you should always look at the structure of the file and see if you can minimize the use of volatile functions such as OFFSET, INDIRECT, RAND, TODAY etc. The volatile functions recalculate every time you make a change in the workbook. It is also a good idea to be careful with Conditional Formatting, which is also a volatile feature.

But the easiest way to reduce the size and increase the speed of you file is to save it as an Excel Binary Workbook:

If you save your file as Binary (xlsb), it will usually be 20-40% smaller than an xlsx file, it opens faster and it calculates faster. In this example I have a file with 500,000 rows of sample data which looks like this:

And when I save the same file in the three most common formats, we see that the normal .xlsx and the macro-enabled .xlsm are exactly the same size, while the (also macro-enabled) .xlsb format is about 32% smaller in size.

I won’t go into the technical details here, but all you need to know is that the Binary format is optimized for performance, and any file you create in Excel you can store in this format. No data is lost, and it even supports macros. The disadvantage is that some programs only understand .xlsx and .xlsm, so if you create Excel files that need to be opened in third party applications, you should test this before you convert all your files.

More Excel tutorials:

How to find cells that contain a formula in Excel
How to Join Text from Several Cells in Excel using TEXTJOIN
How to get Week Numbers right in Excel using ISOWEEKNUM
The Easiest Way to Hide Zeros in Excel

Are you using a non-English version of Excel? Click here for translations of the 140 most common functions in 17 different languages:

Catalan
Czech
Danish
Dutch
Finnish
French
Galician
German
Hungarian
Italian
Norwegian
Polish
Portuguese (Brazilian)
Portuguese (European)
Russian
Spanish
Swedish
Turkish