Software

Modify Excel's default sheet to fit your needs

Change Excel's defaults at the template level to be more efficient and productive.
When you open a new workbook, Excel creates a copy of a template file named book.xlt (the .xlt extension denotes a template file). If you normally make the same changes to every new workbook, I recommend that you make those changes as defaults in book.xlt. That way, every new workbook will open with the settings you need and you can go right to work! To customize book.xlt, open Excel, choose Open from the File menu, and navigate to one of the following folders to find book.xlt:
  • C:\Documents and Settings\Administrator\Application Data\Microsoft\Templates
  • C:\Program Files\Microsoft Office\OFFICEx\XLSTART
where x is the version number. For instance, if you're using Office 2003, that folder would be OFFICE11. If you can't find book.xlt, use Windows Search feature. [UPDATE] If you still can't find book.xlt, see the section below for instructions on creating it. Once you have book.xlt open in Excel, make the necessary changes to bring the template into compliance with your organization. You might alter the font, borders, and alignment defaults. Or you might delete or add sheets to control the number of sheets each new workbook offers. You might even add a generic header or footer. For instance, you can enter a header that will display each new workbook's name, as follows:

  1. Choose Header And Footer from the View menu.
  2. From the Header control, choose Book1.
  3. Click OK and Excel will display the workbook's name in the header. This header trick works in any workbook, not just Excel's template file.

When you've finished customizing book.xlt, save it as book.xlt. Don't change the filename. Specify Template (*.xlt) in the Save As Type control and be sure to save the altered template in the same folder where you found it. Customizing book.xlt makes sense. By reducing the amount of work necessary to get each new workbook file ready, you'll be more efficient and productive.

MIssing book.xlt? [UPDATE]

If your Windows search for book.xlt doesn't turn up a file, don't worry. Depending on how you installed Office, the built-in template might not be on your local system. If this is the case, just create the file yourself. Open a new workbook file and apply all the settings and formats you want. Then, save the file using the name book and make sure you specify Template (*.xlt) in the Save As Type control. In addition, you must save the file in one of two folders:

C:\Documents and Settings\Administrator\Application Data\Microsoft\Templates

C:\Program Files\Microsoft Office\OFFICEx\XLSTART

If you don't have administrator rights on the system, check for the following folder:

C:\Documents and Settings\username\Application Data\Microsoft\Templates

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

17 comments
Marshwiggle
Marshwiggle

Has book.xlt replaced/combined Personal.xls & Excel.xlb? The technique provided for creating book.xlt seems awfully similar to what I've been using for those two files, which, respectively, store macros and custom toolbars that you want to be available to all workbooks. If so, what version marks the change? I've used those two files to transfer my macros and toolbars to new installations for every Office version from 4.2 to 2003, maybe even 7.

JodyGilbert
JodyGilbert

For those of you who don't have the book.xlt file on your system, we've updated the original article to include instructions on how and where to create one.

brianpet
brianpet

I too found that there was no book.xlt where it should have been. I created a new workbook, made the necessary changes that I wanted in the new template and then saved the new template as book.xlt in the XLSSTART folder. Works ok now and the new template is used every time I start a new workbook.

Excelmann
Excelmann

Try your username, not "Administrator" in the path. Also, Office often will not let you save the book.xlt or normal.dot (Word) in the designated directory. Try a simple change and see if the change is saved. If not, the workaround is to save the amended default template in a different directory and then copy it to the correct directory. I have had to use this method almost exclusively on networked PCs.

ngkngh
ngkngh

I've got Excel 2000 (sorry!) as I'm a private indivisual and it works for me. Can't find this book.xlt template anywhere. However, how (in Excel 2000) do you alter the default font type and size in the comment boxes that you can insert in cells? Mine is Tahoma 8 bold...and I don't want it! I want Ariel 10 regular.

lsangree
lsangree

If "best" is what is quickest to load, least resource intensive, etc.... Is it better to create/modify a template, or to tweak Excel Options (2007)? Default fonts, number of tabs,and many other features can be defined there. I don't have time to test it out, but intuition tells me to use Options. I also don't have a book.xlt although there are several special purpose templates (*.xlt) in Documents and Settings.

TrueDinosaur
TrueDinosaur

Vista 64, Office 2007. Can't find book.xlt or book.xltx on my computer.

ssharkins
ssharkins

Create the file yourself, but be sure to save it in the right folder -- the one your system is using to store template files. That should be one of the folders listed in the original article. If you need more help, search on "Create a template" in Help.

jbenton
jbenton

(on my Excel 2003 on Windows XP) you need to alter the Display properties for the Appearance of ToolTips (obvious huh?) right click destop, select Properties, Appearance tab, Advanced settings. Then select ToolTip from the Item dropdown and alter properties as required this will mean that all your hover text will also appear like this whenever you hover over anything James

Ron_007
Ron_007

First, the defaults that are used by office apps (Excel, Word, PowerPoint etc) are built right into the program code. Second, older versions of Office apps installed their default template files. Newer versions, 2003+ (maybe 2002 also), do not. They create the required template file after you make some setting/configuration changes that need to be saved in the template. Third, the newer office versions, definitely 2003+ have started moving settings from the templates to the registry (boo!) Fourth, there is a configuration option to prompt you before it saves changes to the template. I find it a help to prevent "hackers" from making changes. If you don't remember making a change, then don't allow the save Word 2003: Tools / Options / Save tab / Prompt to Save Normal Template Word 2007: Office Button / Word Options button / Advanced / Save section / Prompt before saving Normal Template I looked but couldn't find it in excel, I thought it was there.

TrueDinosaur
TrueDinosaur

Vista 64, Office 2007 When I tried to save the file as a template it went to C:\Users\logonuserid\AppData\Roaming\Microsoft\Templates\ I saved as book.xltx and book1.xltx. But when starting Excel again it does not show my changes. Changes made in the Excel Options panel do seem to be sticky.

rafel
rafel

Hello TrueDinosaur, I'm still an oldy with win XP; office 2007. Didn't fid Book1.xlt either. So I made one myself in folder "C:\Documents and Settings\Administrator\Application Data\Microsoft\Templates\" seems to work fine. Maybe it works fo you as well grts

ngkngh
ngkngh

Thanks James - yes that is REALLY obvious! I've followed your instructions. Default was Tahoma 8 point. I tried to go for Ariel 11 point. The 11 point is now set, but it is still in Tahoma. Don't know why! But a mjor improvement, thanks.

ngkngh
ngkngh

Wish that was the issue. Don't think so, that is just my poor spelling!

ThumbsUp2
ThumbsUp2

Perhaps you're looking or seeing the wrong font listed. The one that comes with Windows is spelled Arial, not Ariel.

Editor's Picks