Save content
Have you found this content useful? Use the button above to save it to your profile.
iStock_xfgiro_exceltips

Excel tip: Convert .xls files to .xls in two clicks

by
18th Mar 2016
Save content
Have you found this content useful? Use the button above to save it to your profile.

Are you still tormented by mysterious messages and strange behaviour working with older .xls files? David Ringstrom is here to help.

Even though it’s almost a decade since Microsoft Excel abandoned the old .xls format, many people persist in using older versions of the spreadsheet program, and some software continues to export files to the old format.

Having bumped up against this irritation in the past, Excel trainer David H Ringstrom posted a tip on our sister site AccountingWEB.com explaining how to convert the obsolete format to .xlsx in two keystrokes.

Up until Excel 2003 the program could only cope with 65,636 rows and 256 columns, until the limit was boosted to more than 1m rows and around 16,000 columns in Excel 2007.

Apart from that basic frustration, there are some other niggles that are a little more subtle.

In newer versions the Compatibility Checker prompt will sometimes appear when saving XLS workbooks, requiring the user to clear the “Check compatibility when saving this workbook” option before continuing.

Elsewhere, however, certain features are disabled when working on the older file format and Excel will stop you copying .xlsx worksheets into the .xls workbook:

Prompts appear when working on older .xls files

These frustrations can be overcome using the Convert command. This elusive beast will only show up when you’re working on an .xls file, and will be indicated by the [Compatibility Mode] warning in the workbook window header. Once you are aware that it’s available, here’s how to use it:

  1. Excel 2010 and later: Choose File, and then click Convert on the Info tab.
  2. Excel 2007: Click the Office button, and then the Convert command which appears under the Open command.
  3. Click the “Do not ask me again about converting workbooks” checkbox, and click OK.
  4. Click Yes on the second prompt that appears.

Excel's Convert command can turn workbook into .xlsx format

[Click image for higher resolution view]

That’s all there is to it. However, if your reporting tools only export .xls workbooks, this can become a tedious, repetitive task. If that applies to you, here’s how to automate the process:

  1. Click the arrow at the end of the Quick Access Toolbar in Excel 2007 and later.
  2. Choose More Commands.
  3. Choose File Tab.
  4. Double-click Convert to Open Office XML Format (this allows you to skip the Add button).
  5. 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. Usually this shortcut will be disabled, but when it does appear, follow these steps:

  1. Press Alt-4 (or the number associated with the command)
  2. 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 about converting workbooks, click the checkbox to suppress that prompt in the future.

Add the Convert command to the Quick Access Toolbar

[Click image for higher resolution view]

Replies (1)

Please login or register to join the discussion.

avatar
By Drumbeg
24th May 2016 13:46

Really helpful, but I think the heading 'Convert .xls into .xls in two clicks' needs to be changed.

Thanks (0)