Dawn Bjork Buzbee
The Software Pro®
Microsoft Certified Trainer (MCT)
Certified Microsoft Office 2010 Specialist (MOS 2010) Master Instructor
Certified Microsoft Office 2007 Specialist (MOS 2007) Master Instructor
Microsoft Certified Application Specialist (MCAS) Instructor
Certified Microsoft Office Expert
Certified Women's Business Enterprise (WBE)
WOSB (Women-Owned Small Business) Certified
>>Back to Articles Home
When you create a new Excel workbook, you get a standard default
workbook. But what if you don’t like that workbook? For example, maybe
you have a standard header that you always (or almost always) use on
printed pages of your workbook. Or possibly you prefer a different
default font style or size, typically use the comma number format or you
often change the layout of column widths when you create a new
worksheet. Do you frequently print your worksheets on legal-size paper
with a landscape orientation? Does your company require a disclaimer or
confidentiality footer on key Excel workbooks?
it turns out, Excel gives you quite a bit of control over the look and
layout of your worksheets. It’s fairly straightforward to create an
entirely customized default workbook. The trick behind this magic in
Microsoft Excel 2010 and Excel 2007 is creating a template file named
book.xltx (or book.xltm if your default
workbook contains macros), and then saving this file to the appropriate
location on your hard drive.
Creating a New Excel Workbook Template
To create a new default Excel workbook template:
- Open a new blank Excel workbook.
- Next, customize the blank workbook exactly as you want it to
- Save the workbook with a specific file name in a designated
folder. Additional ideas and steps are provided below.
Some Excel workbook elements you might change:
- Font style and font size: Highlight the portions of the
worksheet you want to change and select your preferences for number,
alignment, and font formatting from the Font group in the Home tab.
- Print settings: Select one or more worksheets and then
choose Page Layout tab > Page Setup group to specify print settings
including the header and footer, margins and orientation, and
indicate other print layout choices.
- Number of sheets: Add or delete worksheets, re-name sheet
tabs, and even change worksheet tab color.
- Column widths and layout: If you normally prefer
different column widths, select the columns or even the entire
worksheet and then modify the column width.
NOTE: Any new worksheets you insert into your custom default
workbook will revert back to the original formatting and layout. You may
want to add extra worksheets to the original workbook, or reserve an
extra or master worksheet you can copy as desired.
Applying Changes to Multiple Cells and/or Worksheets
To add custom formatting changes to every cell, column, or row,
first highlight all cells with Select All (press [Ctrl] + A).
When you are done, press [Ctrl] + [Home] to clear the cell
To apply changes such as formatting or print settings to multiple
worksheets in a workbook, right-click on any sheet tab then left-click
on Select All Sheets which groups the worksheets together for
shared actions. When you have finished your changes, left-click again on
any sheet tab to clear the worksheet grouping.
You don’t need to create a new default workbook if all you want to
do is change the number of worksheets in a new workbook (the default is
3). In Excel 2010, choose File > Options, pick the General
category, and specify the desired number of sheets in the Include this
many sheets setting. In Excel 2007, pick the Microsoft Office Button
and then click Excel Options. Choose the Popular category
and specify the desired number of sheets in the Include this many
Saving Your New Workbook
To save your new default workbook:
- When the new default workbook is set up to your preferences,
choose the File tab or Microsoft Office Button and
then Save As > Excel Workbook.
- In the Save As dialog box, choose the Save As Type
drop-down list, and select Excel Template (*.xltx).
- Name the file as book.xltx
The file needs to be saved in your XLSTART directory which
is on your local C: drive. The location of this directory varies
depending on your version of Windows and Microsoft Office; search
your hard drive for the folder.
- After you save the template file, you can close it.
- Close Excel.
- Start Excel to see your new workbook.
Now, every time you start Excel, the new blank workbook will be
based on the template you created. In addition, when you click the
New toolbar button (or press [Ctrl]+ N), the new workbook
will be created from your template.
As always, this or any other workbook can still be individually
customized as needed.
Keep in mind that creating and saving a custom default Excel
workbook only changes the default workbook on the active computer and
does not affect the workbook used by others on your computer network.
You can, however, share your default workbook by copying your
book.xltx file to the proper location on another computer.
If the XLSTART directory is on a network, you may not have
permission to save files. Instead, you can create a startup directory on
your own system with any name you want and store the book.xltx
file in this new alternate startup directory. The directory name you
choose doesn’t matter, but you will need to tell Excel where it is.
To save your default workbook in an alternate directory:
- Create a new folder on your C: drive where you will store your
- In Excel 2010, pick File > Options, and then click the
Advanced category. In Excel 2007, click the Microsoft Office
Button, choose Excel Options, and then choose the
- Under the General section, type the full path of the
folder that you want to use as the alternate startup folder in the
At startup, open all files in box.
- If a workbook with the same name is in both the XLSTART folder
and the alternate startup folder, the file in the XLSTART folder
Caution: Because Excel will try to open every file in the
alternate startup folder, make sure you specify a folder that contains
only files that Excel can open and only files you want to see every time
you start Excel.
Note: If you are not able to save your updated default workbook, you
may need to run Excel as an administrator.
Create your own custom workbook today to save you time and effort in
© Dawn Bjork Buzbee, MCT, The Software Pro®
Dawn Bjork Buzbee
is The Software Pro®
and a Microsoft Certified Trainer (MCT) as well as a certified Microsoft Office
Specialist (MOS) Master Instructor, certified Microsoft Applications Specialist
(MCAS) Instructor, and a certified Microsoft Office expert. Dawn shares smart
and easy ways to effectively use software through her work as a software
speaker, trainer, consultant, and author of 8 books.
This article and
more can be reprinted at no charge in your publications and website with
copyright and attribution.
more about how easy it is to share these valuable tips, tricks, and techniques.
Be an Excel Expert!
How effectively are you using Excel to create, format,
and analyze your important data?
Become an Excel expert with the
Excel with Excel
Learning Series. Master functions, filtering,
linking, PivotTables, charts and graphics, macros, and more!
Click Here to
Choose Your Excel Training
More Microsoft Excel Tips, Tricks & Handouts Too!
More Smart & Easy Software Tips, Tricks &
Techniques for You
<<Back to Articles
Right-click on any workbook tab for
worksheet management commands such as Delete, Rename, Select All
|Excel 2010 and
Excel 2007 files with an .xlsm extension are
macro-enabled workbooks, that is, they can or do contain macros to
automate your work in Excel.