ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel
  • »
  • Technology»
  • Computers & Software»
  • Computer Software»
  • Office Software Suites»
  • Microsoft Office

Using Templates in Excel 2007 as a model to save you work and time formatting your spreadsheets and adding common data

Updated on December 21, 2013

Use pre-designed templates or creating new templates in Excel 2007 to reproduce frequently used workbooks quickly and easily

Welcome to my latest hub on Excel 2007. Today I will be looking at using Excel 2007 templates including both those that are installed by default within Excel and those that are available on the web via http://office.microsoft.com.

Templates allow you to save time and manual effort by enabling you to avoid doing the same tasks over and over. Say for example, your company sends out invoices to customers once a month. Rather than starting with a blank workbook, you can create an Invoice with all the formatting and data in it and save it as a template that you can reuse time and again.

Using templates is also an excellent way of quickly accessing calculators or analysis tools that have been created by other people. Alongside the calculators and financial tools, you can add everything from dinner party lists, resumes, certificates, labels, business cards to themes and even family trees using templates.

Examples of templates available at http://office.microsoft.com for Excel 2007.
Examples of templates available at http://office.microsoft.com for Excel 2007. | Source

This hub came about as I was doing research into creating a mortgage calculator and amortisation schedule for Excel. As often occurs when researching aspects of Excel, I came across something else I was not expecting which in this case was the wide array of built in templates available in Excel 2007.

My hub on creating a mortgage calculator can be found here http://robbiecwilson.hubpages.com/hub/Creating-an-Amortisation-loan-or-mortgage-schedule-using-Excel-2007-and-Excel-2010

and my hub on creating an amortisation calculator can be found here http://robbiecwilson.hubpages.com/hub/Creating-an-Amortisation-loan-or-mortgage-schedule-using-Excel-2007-and-Excel-2010

Lastly today, I will look into creating templates from your own workbooks.

Using the existing built in templates in Excel 2007

Excel comes with a number of templates built in. To access them:

Examples of built in templates available in Excel 2007.
Examples of built in templates available in Excel 2007. | Source
  • Click on the Excel button
  • Select New
  • Rather than selecting Blank Workbook as normal click on the Installed Templates tab
  • You will also notice on the left of the above figure there are a large number of categories of Templates to choose from
  • I selected Loan Amortization from the Installed Templates tab
  • Once you click Create, Excel will open your chosen template in a new workbook

Built in Amortisation template opened in Excel 2007.
Built in Amortisation template opened in Excel 2007. | Source

Once the template opens, you will quickly notice that it is locked down and the only cells you can manipulate are those cells in orange in the figure above.

Accessing additional online Templates from Microsoft or other sources

Should you find that Excel 2007 does not have the exact template that you need, you can search for additional templates online. There are two methods for doing this:

  • Click on the Excel button
  • Select New
  • In the box at the top where it says Search Microsoft Office Online for a template, type in what you are looking for and click the →

Alternatively, in a browser, navigate to http://office.microsoft.com/en-us/templates/ or to another reputable source of templates. On Office.Microsoft.com:

  • Search for Excel 2007
  • Click on Excel on the left and select 2007 from the product list
  • Or you can put “Excel 2007” at the beginning of each search.

A selection of the templates available for Excel 2007 on Office.Microsoft.com.
A selection of the templates available for Excel 2007 on Office.Microsoft.com. | Source

As you can see from the Office Online website, there are literally hundreds of templates available. Simply select one that you like and click and then download to install.

Save an online template to your computer so that you can use it in the future from your copy of Excel 2007.
Save an online template to your computer so that you can use it in the future from your copy of Excel 2007. | Source
  • Once you get to the File Download dialogue box, click Save and give it a more memorable name
  • Save the file into your templates folder. To find out where your templates folder is:
  • Click the Excel button
  • Click New and Select My Templates
  • Right click on one of your templates and click Properties
  • Under the Location, copy the path to your templates
  • Save your new template to that same folder so that it is added to My Templates in Excel

You can see I added Break-Even Analysis that way

An online template downloaded, saved and added to your template library in Excel 2007.
An online template downloaded, saved and added to your template library in Excel 2007. | Source
  • Once it has downloaded, click Open

Creating a template from an existing workbook in Excel 2007

If you have a workbook you want to make into a template so that you can use it as the base for other workbooks, you can also do this in Excel 2007. To achieve this:

  • First, you will want to protect the cells you don’t want people to be able to change.
  • To do this, you will actually unlock the cells you want people to access and lock the rest (by default all cells are locked in Excel 2007, although they are not fully locked until you protect the sheet or workbook).
  • Right click on cells that you want people to access and select Format Cells
  • On the Protection tab, clear the Locked check box
  • On the Review tab, click the Protect Sheet button in the Changes group
  • Clear the Allow all users of this worksheet to: Select locked cells
  • Assign a Password to unprotect sheet:
  • Confirm the password
  • Click OK

I have a hub that goes into protecting and sharing workbooks or sheets in Excel 2007 in much greater detail. This hub can be found here:

http://robbiecwilson.hubpages.com/hub/Sharing-and-protecting-documents-in-Excel-2007

Now that we have protected parts of the workbook we want to protect, we then create the template itself. To do this,

  • Click the Excel button
  • Select New
  • Select New from Existing Workbook
  • Browse to the workbook
  • Select Create New

Excel will then add your file to the template folder so it will be selectable from Blank and Recent in Templates under New Workbook.

Note: The new file will still be an *.xlsx file. You should save it as a *.xltx (Excel Template) or an *.xltm (Macro enabled Excel template) and save it to your templates folder as described above.

Conclusion

Excel 2007 along with all other versions of Excel both past and present has a number of built in templates available. There are also a large number available online, either on the Office Online or elsewhere on the web. Using a template allows you to create workbooks with identical formatting and data quickly and easily. You can use templates for invoices, calculators and a plethora of other things using an existing template that you either created or alternatively you downloaded from the web.

In today’s hub, I have looked at:

  • Accessing the large number of built-in Excel templates as well as adding them from Office Online.
  • I also investigated creating new templates based on your own workbooks and discussed which folder templates should be stored in.

Many thanks for reading this hub; I do hope that you found it useful and informative. Please feel free to leave any comments you may have below.

© 2013 Robbie C Wilson

Comments

    0 of 8192 characters used
    Post Comment

    No comments yet.