There's a good chance that any software you use to export data to Excel is surreptitiously making your day a little harder than it should be. This is because many programsâ€”particularly certain popular cloud-based accounting programsâ€”export reports to Excel in an obsolete format. In this article I'll discuss three ways that you can convert such workbooks into a usable format, and streamline the process down to two keystrokes.
The XLS workbook format is often referred to as the Excel 97-2003 workbook format, and for good reason. Those versions of Excel supported spreadsheets with 65,636 rows and 256 columns. Modern workbook formats, such as XLSX allow over one million rows and roughly sixteen-thousand columns. However, you may not realize the depths of frustration that XLS workbooks can cause because the other side effects are subtle. First off, starting with Excel 2007 the Compatibility Checker prompt sometimes appears when you save XLS workbooks, which you can dispatch on a file-by-file basis by clicking the checkbox shown in Figure 1. As illustrated in Figure 2, you'll run into disabled features, and Excel will block you from copying worksheets from an XLSX file into the XLS workbook.
Figure 1: The Compatibility Checker Prompt is the least of your concerns with XLS workbooks.
Figure 2: Disabled features and cryptic prompts triggered by XLS workbooks often stymie Excel users.
There's a simple solution, as illustrated in Figure 3, which is to use the Convert command to upgrade the workbooks to a modern format. The Convert command may feel elusive, as it only appears when needed, but is easy to use:
- Excel 2010 and later: Choose File, and then click Convert on the Info tab.
- Excel 2007: Click the Office button, and then the Convert command which appears under the Open command.
- Click the â€œDo not ask me again about converting workbooksâ€ checkbox, and click OK.
- Click Yes on the second prompt that appears.
Figure 3: Use the Convert command to upgrade your workbook to an XLSX format.
No muss, no fuss, your XLS workbook is swapped out in place for a modern version that won't have any of the baggage shown in Figures 1 and 2. These commands aren't difficult to carry out, but if you're frequently exporting reports to Excel from a platform that can only generate XLS workbooks, you'll likely want to streamline your workflow as much as possible. This is where the two-keystroke shortcut comes into play.
As shown in Figure 4:
- Click the arrow at the end of the Quick Access Toolbar in Excel 2007 and later.
- Choose More Commands.
- Choose File Tab.
- Double-click Convert to Open Office XML Format (this allows you to skip the Add button).
- Click OK to close the Excel Options dialog box.
When you tap the Alt key, Excel will reveal the shortcut number for the new Convert command, which is predicated on the order that the command appears within your Quick Access Toolbar. If the Convert command is the fourth icon on the toolbar, its keyboard shortcut will be Alt-4. Much of the time in Excel this new command will be disabled, but any time it lights up you'll want to do these two steps:
- Press Alt-4 (or the number associated with the command)
- Press Enter to close the â€œDo you wish to close and reopenâ€ prompt.
The command will now be disabled again. If you encounter an additional prompt regarding converting workbooks, click the checkbox to suppress that prompt in the future.
Figure 4: Streamline your workflow by adding the Convert command to the Quick Access Toolbar.
About the author:
David H. Ringstrom, CPA, heads up Accounting Advisors, Inc., an Atlanta-based software and database consulting firm providing training and consulting services nationwide. Contact David at [email protected] or follow him on Twitter. David speaks at conferences about Microsoft Excel, teaches webcasts for CPE Link, and writes freelance articles on Excel for AccountingWEB, Going Concern, et.al.
About David Ringstrom, CPA
David H. Ringstrom, CPA, is an author and nationally recognized instructor who teaches scores of webinars each year. His Excel courses are based on over 25 years of consulting and teaching experience. His mantra is “Either you work Excel, or it works you.” David offers spreadsheet and database consulting services nationwide.