Make Changes to the Default Excel Blank Workbook

Last week we looked at how to create custom formats in Excel and how useful and time saving they can be.

However, sometimes in Excel, there are certain things that aren't saved globally such as custom formats or styles for example. If I take the time to create a new format, chances are, I'll want to use that format again in other workbooks.

Fortunately, there is a way that you can do this. The blank workbook which is launched when you click on File, New can be customised. Here's how...

  1. First, you need to know where to store the blank workbook template.
  2. To locate it:Press Alt+F11 to open the visual basic editor
    Press Ctrl+G to go to the immediate windowType
    ?Application.StartupPath
    and press Enter will give you the personal XLSTART andType
    ?Application.Path

    and press Enter will give you the Excel folder to which you add \XLSTART for the all-users XLSTART.

  3. Make a note of this and close the visual basic editor.
  4. Next, create a new blank workbook and create your custom formats or styles that you want to store.
  5. Choose File, Save As and set the Save as Type to Excel Template.
  6. In the filename box type book.xlt
  7. Ensure the file path you are saving to is the xlstart folder (note this default can be changed in File, Options in the Advanced section)
  8. Click on Save
  9. Any changes you made to the book.xlt file will be loaded when you start a new workbook.

If your Windows settings don't allow you to make changes to the XLStart folder see your administrator to change permissions or consider recording a macro. Here's a video which shows how to speed up formatting using a custom number format.

See you again soon, please comment and share if you found useful!
Anita Lund
www.trainers-direct.com.au  Sydney
www.stormtraining.com UK