Advantages of using a binary workbook in Excel

excel tips by dataminded

Another Friday rolls around and the summer is going fast! Welcome to your weekly 3 tip Friday update, it’s just a short dose of Excel help before you start the weekend. Each week we’ll cover a tip, a shortcut and a function - making it super easy for you to get better at Excel.

If you find this useful or have requests or ideas for future editions, send me a message or comment at the bottom of this post! Now, on with the tips. 

A quick win you can learn today -

There are several different types of Excel file that you can use to save your work - the default is .xlsx, and if you’re using macros you’ll need to use .xlsm (a macro enabled workbook) to save your code with your work. 

There’s also a third main type of Excel file - a Binary Workbook, .xlsb, which can be used to compress large data files.

The advantages of using a binary workbook with a large file are:

  • Loading time is faster

  • Formulas calculate faster

  • File size is smaller (often by around 30%)

  • Macros are still supported

  • More secure - when password protected, these files are harder to decrypt.

A keyboard shortcut I'm adding to my list - 

I’m always looking for ways to save time in Excel! One function I only recently learned adds the SUM function to the end of a table automatically. Use Alt + = to enter the SUM() function (AutoSum) to sum the adjacent block of cells. Just make sure you check that Excel selects the right range in the function!

A function I've found super useful this week - 

Sometimes you can have cells which can contain either text or a number (For example, a sales column might have ‘Not applicable’ written in some cells. So it can be useful to apply a function to determine if a cell contains a number or not. Use =ISNUMBER to return TRUE if the value is a number - you could combine this with an IF to perform an action on the cell depending on whether or not it contains a number. 

An added bonus - as Excel also sees dates as numbers, you can use this function to determine if a cell contains a readable date.

 Check some more top tips for working in Excel on Microsoft’s website!

I hope these tips were useful, keep the suggestions coming on the facebook group here, be sure to join if you're not already a member.

- Martyn

Edinburgh, Scotland

August 2018