Most users of Microsoft Excel know that the product supports templates. There are the many out-of-the-box templates you are presented with when you first launch Excel, and there is the “Save as Template” XLTX options through which you can save your own custom personal templates for re-use later. However, there may be some advanced options that you are not aware of, and I’ll cover those in depth below.

Basic Microsoft Excel Templates

First, let’s take a look at the two most basic Microsoft Excel template options.

The first template-set to be aware of (and are impossible to miss) are those which ship with the product and are provided by Microsoft and independent solution providers who have uploaded their templates to the Office store.

When you first launch the application, you are presented with “Featured” templates and also options to filter and search through other online templates.

Two major biased highlights here:

• The tutorial templates
  • Many people do not know how to use formulas or pivot tables and/or they don’t have good sample data to start trying to learn through.
  • I can personally attest to the quality of the Formula and PivotTable templates – if you need a learning vehicle to get you started on those topics then I suggest you give the Microsoft templates your time-and-attention.
• The Sales Invoice Tracker
  • This template is super powerful right out of the gate, and can be easily customized with extra fields and calculations that you might need.
  • I have used this template for almost 5 years now. That’s dozens of invoices delivered and hundreds of invoice line items therein. This template is fantastic.

The second type of basic templates are those which you create and are then surfaced in the “Personal” tab:

Some important highlights here:

• You create these templates by changing the “Save as type” option to “Excel Template (*.xltx)” in the Save As dialog.
• The default save as location is configurable, but probably shouldn’t be changed unless you really have a reason for doing so – like sharing them with a team on a network drive.

Here is a reference screen shot for the “Save as type” option:

Notice the path: Documents > Custom Office Templates. As noted above, you’ll only want to change this if you have a business reason. And, if so, you can do this by clicking “Options” and then setting the “Default personal templates location” value in the Excel “Save” options:

Most of us can meet requirements for many scenarios using the out-of-the-box provided templates and those we create and store as Personal templates. However, if you’re trying to setup and enforce a business process, have team compliance and governance mandates, or are simply working in more advanced scenarios…there are some other more template options for you to take advantage of.

Advanced Microsoft Excel Templates

Overriding the Default New Workbook

Let’s consider a scenario where you are working with a team who delivers on a specific business process. 99% of the time, your team is creating/populating the same spreadsheet which has a very specific form and function and can be saved as a template.

Here, it makes sense to provide a quick means to instantiate that template from within Excel without always needing to go to the File menu and then browsing for the template. Instead, you can actually override the template that gets created with the “New Workbook” button.

• Open Excel Options
• Navigate to Trust Center
• Click “Trust Center Settings”
• Copy the “Excel default location: User StartUp” path

That path is where you are going to save your template using the Save As > Excel Template option covered above.

And, the critical step here is that you save the template exactly as “book.xltx”.

Now, whenever the user clicks “New Workbook” from within Excel, the custom template will be used/created:

If this is an organization/team wide setting, and you have an IT department to help implement defaults on coworkers’ computers, this is something IT can manage when they configure the machine image, install settings, or they can push the configuration through group policy.

Overriding the Default New Worksheet

Overriding the default behavior of the New Worksheet button will likely be based on similar motivation to the previous template example (i.e. overriding the default new workbook).

Here though, instead of instantiating a workbook that consists of many worksheets, you’ll be designing only a single worksheet to be saved as a template.

The steps for doing this are almost identical to overriding the default New Workbook. The only difference is that, instead of saving the template as book.xltx, you save the template exactly as sheet.xltx.

Now, every time the user clicks the “+” sign to add a new worksheet, it will be the custom worksheet template that is used.

Adding New Insert Options

Finally, you may not want to override Microsoft Excel’s defaults. Instead, and in my opinion, the most likely of these advanced scenarios is that you want to create a workbook template (i.e. a set of worksheets and functions) that is re-usable within any type of workbook.

Here, yet again, the steps are very similar to those in the other advanced scenarios.

Create your workbook, save it in the same path you captured from the Trust Center settings in the other two scenarios, but simply do not save it with the name “book.xltx” nor “sheet.xltx”. Save it as anything else. Now, this workbook can be inserted by right-clicking an existing sheet and choosing Insert.

You will see your custom workbook template, alongside quite a few other reusable workbooks. When selected, it will be inserted “inline” as the sum of its worksheet parts into your existing workbook.

You see above, along with your own custom templates, you could even re-use existing templates to create your own training workbook or other powerful Excel templates built from a combination of those which ship with the product.

Thanks for reading.

Categories: Business, Microsoft Excel

Tags: Business Management, Microsoft Excel, Office