Choosing a File Format

A good question which keeps popping up is what file format should you choose for an Excel file. Excel can save in a variety of formats. I’m assuming we’re only dealing with Excel 2007 or later and will not be discussing the XLS format. For this post we’ll only be looking at the top three file formats:

  • XLSX
  • XLSM
  • XLSB

Excel file types

These are all my personal preferences and reasons behind why I would choose one over the other. If you really want to geek-out, hold onto your pants and here are some additional reading:

Excel file formats on Wikipedia

Introduction to new file name extensions

All about file formats on the Office blog

XLSX

Pro’s

  • A robust and well compacted file format
  • Opens in any version of Excel
  • Guaranteed to be macro free

Con’s

  • Cannot contain macros (double-edged sword, eh)
    • While this file type can contain macros, they cannot be saved in the file

Summary

I’ll use this file format in all files unless I need to use any code.

XLSM

Pro’s

  • Moderately well compacted file format
  • Opens in any version of Excel
  • May contain macros

Con’s

  • Often scary to those who aren’t fluent with Excel or macros
  • May contain macros

Summary

Typically I won’t use this file type. While it can contain macros, people are mostly afraid of the word “macros” in the file name. This confusion can be mitigated by either using XLSX or XLSB.

XLSB

Pro’s

  • Extremely well compacted file format
  • Opens in any version of Excel
  • Most secure file format
  • May contain macros

Con’s

  • May contain macros

Summary

This is my go-to file format. I can save my macros in my file and have the best file compression of all the file types. When sending people Excel files, a “binary” file format is generally less scary than a “macro-enabled” file format and will put them at ease.

To see the security of an XLSB in action we only need to change the extension of the file from XLSB to ZIP and then explore it’s contents. In the images below you’ll see an XLSX and XLSB file with their extensions changed to ZIP and explored. The important takeaway here is XML files are easily opened, while BIN (binary) files aren’t easily opened, and either need to be mounted as a separate drive or have a third party application to open them, hence making them tougher to look at their contents, thus more secure.

XLSX

XLSX zip

XLSB

XLSB zip

It’s because of these reasons my go-to file format is XLSB, and the only time I’m using XLSX is if I’m sending a file which doesn’t contain any macros at all.

Leave a Reply

Your email address will not be published. Required fields are marked *