We often open and save Excel workbooks without much thought behind the document format itself. For many of us, if the spreadsheet opens in Excel and lets us keep our work, then we’re happy. However, a deeper look at the different types of Excel spreadsheets can unearth some surprising findings. In this article, I’ll describe the pros and cons of each of the primary document formats that you can use for saving Excel workbooks.
The Excel Workbook format (sometimes referred to as Excel Spreadsheet Open XML) is the standard file format for Excel 2007 and later. Unlike the previous XLS file format, which I’ll discuss later in this article, modern spreadsheets use the text-based XML (eXtensible Mark-up Langage format). XML is akin to HTML (HyperText Mark-Up Language), which is the lingua franca for the Internet. This format ensures broader compatibility with other spreadsheet applications, such as the free Libre Office suite that offers a Calc spreadsheet.
Characteristics of the XLSX format include:
- 1,048,576 rows and 16,384 columns per worksheet, as shown in Figure 1
- A more compact file format
- Improved file recovery capabilities
- The ability to support modern features such as Sparklines, Slicers, Timelines, Charts, Pivot Table formats, and so on
- Programming code (macros) cannot be saved to an XLSX document
- A color palette of 4.3 billion colors (up from 56 colors in the XLS file format)
In most cases, your spreadsheets should be saved in the XLSX document format, unless you have a reason to make use of the XLSM or XLSB file formats. If you’re among the few still holding on to Excel 2003, installing the free Microsoft Office Compatibility Pack will enable you to open XLSX workbooks in that version of Excel, although you’ll still be limited to only seeing 65,536 rows and 256 columns of data. Further, you won’t be able to see any modern features that require a subsequent version of Excel.
The XLSM format has all the same characteristics of the XLSX format, however it may contain programming code known as macros. An Excel macro is one or more lines of programming code that can be played back to automate a process within an Excel workbook. Experienced Excel users can press Alt-F11 in Excel to launch the Visual Basic Editor to write macros from scratch.
Alternatively, the Macro Recorder, tucked away on Excel’s View menu under the Macro command, offers anyone a jump start on creating macros. The Macro Recorder transcribes some—but by no means all—actions you carry out in Excel into programming code that can be played back. If you add one or more macros to an Excel workbook, you’ll need to save the document as an XLSM or XLSB file, or else run the risk of discarding your macros, as shown in Figure 2.
I can’t count the number of times over the years that consulting clients have contacted me about spreadsheet-based tools that I’ve created that suddenly stopped working. Almost every time it’s because a user wanted to rename a file or save it to a new location, and inadvertently chose the Excel Workbook (*.xlsx) format instead of the Excel Macro-Enabled Workbook (*.xlsm) format. If one day you find yourself rushing through a task and inadvertently click Yes to the prompt shown in Figure 2, all is not lost if you still have the spreadsheet open. Document format changes don’t fully take effect until you close the workbook and reopen it. Thus, if you still have the file open, you can immediately resave it as an XLSM file and keep any macros intact.
Do note that when you open a macro-enabled workbook you may see some variation of an Enable Macros prompt. Sometimes this takes the form of an on-screen pop-up, as shown in Figure 3. In other cases a message bar-based prompt will appear, as shown in Figure 4.
I haven’t been able to discern a pattern as to when Excel presents the on-screen prompt versus the message bar prompt in Figure 4. However, when that message bar does appear, you do have a chance to suppress the security notice entirely. Instead of clicking Enable Content, instead choose File, choose Enable Content on the Info tab, and then select Enable All Content. Notice the parenthetical “make this a Trusted Document.” Once you do so, the document is considered safe to open without alerting the user as to the presence of macros. The Security Notice in Figure 3 may still appear randomly from time to time, but generally the file will open without any further action required on your part.