15 years of XLSX: choose file formats carefully

Pluto

Still using .XLS to save new spreadsheets?

It has now been 15 years since the Open XML based formats (XLSX and XLSM) were introduced as the default spreadsheet file format, replacing the previous binary interchange format (XLS).

Changing of the guard

Once users upgraded from pre-2007 software versions, the XLSX benefits became evident:

  • More rows and columns
  • More flexible formatting
  • More secure
  • More robust (apparently)

These easily outweighed the most obvious XLSX downsides:

  • Workbooks with macros used the companion XLSM format instead
  • Can’t share XLSX files with users on old program versions

We recently outlined some common formats and use cases at available file formats to help choose which one suits each situation.

For more rationale on the change along with further technical details, see the original information at Introducing the Office (2007) Open XML File Formats.

What happens when you choose the wrong format?

Spare a thought for all the data wranglers scattered around the globe working on pandemic case data recently. Several accounts have surfaced of file format issues causing data integrity problems, leading to mis-reporting case numbers, infection rates or related data.

In the example at Excel: Why using Microsoft’s tool caused Covid-19 results to be lost, it looks like importing a CSV data file into a spreadsheet saved as XLS meant that only 216 rows were saved; all the rows beyond row 65,536 slipped through the net.

A timely reminder

This is an opportunity to be mindful of the file format you choose whenever saving a new workbook.

Every day, professionals and amateurs alike shuffle a lot of data between a lot of sources, in a wide variety of formats. Rather than point at user error, training, choice of software or other cheap bytes, we simply suggest making the choice of file format for new workbooks an active, considered choice.

If you find yourself still choosing XLS… make sure you have a good reason!