Changing Excel 2007 Default template location

Q: Where do you go to change the default locations for templates in Excel 2007?

Luckily I already know the answer to this question because my bet is that it’s going to take your hours to work out how to do it. You see, there’s no way in Excel to change the default location for where its templates are stored, in particular as one of my blog readers found to his chagrin, the default location for saving chart templates.

Perhaps I’ll start this story again, this time at the beginning. In a recent blog entry I showed you how to save a chart template. The process is this: create your chart in Excel 2007 and then from the Design tab which appears only when you have the chart selected, click the Save As Template button in the Type group and save your file in your default templates folder which should be c:\Documents and Settings\username\application data\Microsoft\templates. The file should have the .crtx extension.

Close your Excel worksheet, close Excel, open Excel again, create the data for a new chart, select the data and choose Insert, Other Charts, All Chart types, Templates and your chart template should appear in the list. So far so good.

Problem is that not everyone’s installation of Excel 2007 looks to this default location for either saving chart templates or finding them when you need to use them. In particular you may confront this problem if you’re on a network. So the question then becomes where are your chart templates supposed to be saved to? Answer - who knows? You see, there's no setting in Excel to say where to put them, so you have no clue where Excel is looking for them, so you can't put them there because where is "there"?

So, we're now at the point where we know there’s no setting in Excel for specifying the default location for templates. If we could set this, we could save our template there and Excel could find it... simple to understand, but problematic to achieve.

The solution that I found and which works is to make the change in Word 2007 - not exactly the first place you'd look huh? If you visit this link: www.kbalertz.com/kb_924460.aspx you’ll see buried in the KnowledgeBase article quoted there, information on how the template locations in Office 2007 programs are managed.

There are some registry entries that you can change but the simpler solution is to change the location in which your templates are stored using the Word settings. When you use Word 2007 to change the location where your Word templates are stored you also change the location where all Office 2007 templates are stored. So Word’s settings control every other program which is sort of handy to know because you could spend all day looking in Excel for a place to change the Excel template location.

So here’s the short information on how to change Excel’s default template locations: --

Start Word 2007, click the Office button and choose Word Options, Advanced and locate the General group. Click File Locations, User Templates, Modify and in the modify location dialog change the setting in the folder name list or the look in list to point to the folder where your templates will be saved.

For ease of access and backup I suggest that you put it where they were supposed to be put in the first place which is Documents and Settings\username\Application Data\Microsoft\templates but theoretically you could put them anywhere.

This changes the setting in the Windows registry so that all templates are now saved to this location.

The KnowledgeBase article makes essential reading for anybody trying to manage Microsoft Office applications particularly in a network situation.