Skip to main content

Lean Tools, Training, and Systems

Tips for choosing

Excel file formats

Caution: Never save a macro-enabled workbook as xlsx

If your workbook is already a (non-macro-enabled) .xlsx file,

then it is okay to keep it that way.

If your workbook has macros, then when Excel asks you to choose a file format, you can choose:

  • Macro-Enabled Workbook (.xlsm)
  • Binary Workbook (.xlsb)
  • 97-2003 Workbook (.xls)
Do NOT choose .xlsx

Do NOT choose the default Excel Workbook (.xlsx)

which will strip all macros!

Learn more about macros

How to change your default file format

As delivered from Microsoft, the default file format is .xlsx

(the one file format that you should never use, because it strips all macros)

To change your default file format...

File > Options > Save > Default Format
and then choose .xlsm, xlsb, or .xls

Save Format


If different people in your work team use various versions of Microsoft Office

Tip: The Compatibility Checker dialog often comes up with warnings that there will be "Significant Loss of Functionality" if you save your file in .xls Excel 97-2003 compatibility format. Most of these warnings are simply wrong. The functionality that might be lost is itemized, so you can test it for yourself, but you will find that there is usually no loss of functionality.

If everyone on your work team uses Office 2007 or higher
  • .xlsm, (macro enabled workbook), or...
  • .xlsb (which has even more improved performance, but less backward compatibility)

Oops - I saved as xlsx

Now what do I do?

Option 1) Restore the deleted VBA

Open a fresh new blank template of the same type.

Copy the VBA module(s) from the new workbook to the damaged one.

Excel > ALT+F11 (to open Visual Basic for Applications)

Click the 'plus' symbol (+) to expand both workbooks so that you can see the modules in each.

Drag the M1Systems2win module to copy it from your undamaged workbook to the damaged one.

If there are additional missing modules, copy them too.

(Systems2win modules all start with the letter M)

Save the damaged workbook in a format that won't strip the macros.

(as described in the section at the top of this page)

VBA Copy Module

Option 2) Migrate your data to a new workbook

Open a fresh new blank template of the same type.

Copy data from your damaged workbook, and paste it to the new one

using Paste Special > Values or Paste Special > Formulas

(not regular Paste)    Learn more