ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel

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.

    working

    This website uses cookies

    As a user in the EEA, your approval is needed on a few things. To provide a better website experience, hubpages.com uses cookies (and other similar technologies) and may collect, process, and share personal data. Please choose which areas of our service you consent to our doing so.

    For more information on managing or withdrawing consents and how we handle data, visit our Privacy Policy at: https://hubpages.com/privacy-policy#gdpr

    Show Details
    Necessary
    HubPages Device IDThis is used to identify particular browsers or devices when the access the service, and is used for security reasons.
    LoginThis is necessary to sign in to the HubPages Service.
    Google RecaptchaThis is used to prevent bots and spam. (Privacy Policy)
    AkismetThis is used to detect comment spam. (Privacy Policy)
    HubPages Google AnalyticsThis is used to provide data on traffic to our website, all personally identifyable data is anonymized. (Privacy Policy)
    HubPages Traffic PixelThis is used to collect data on traffic to articles and other pages on our site. Unless you are signed in to a HubPages account, all personally identifiable information is anonymized.
    Amazon Web ServicesThis is a cloud services platform that we used to host our service. (Privacy Policy)
    CloudflareThis is a cloud CDN service that we use to efficiently deliver files required for our service to operate such as javascript, cascading style sheets, images, and videos. (Privacy Policy)
    Google Hosted LibrariesJavascript software libraries such as jQuery are loaded at endpoints on the googleapis.com or gstatic.com domains, for performance and efficiency reasons. (Privacy Policy)
    Features
    Google Custom SearchThis is feature allows you to search the site. (Privacy Policy)
    Google MapsSome articles have Google Maps embedded in them. (Privacy Policy)
    Google ChartsThis is used to display charts and graphs on articles and the author center. (Privacy Policy)
    Google AdSense Host APIThis service allows you to sign up for or associate a Google AdSense account with HubPages, so that you can earn money from ads on your articles. No data is shared unless you engage with this feature. (Privacy Policy)
    Google YouTubeSome articles have YouTube videos embedded in them. (Privacy Policy)
    VimeoSome articles have Vimeo videos embedded in them. (Privacy Policy)
    PaypalThis is used for a registered author who enrolls in the HubPages Earnings program and requests to be paid via PayPal. No data is shared with Paypal unless you engage with this feature. (Privacy Policy)
    Facebook LoginYou can use this to streamline signing up for, or signing in to your Hubpages account. No data is shared with Facebook unless you engage with this feature. (Privacy Policy)
    MavenThis supports the Maven widget and search functionality. (Privacy Policy)
    Marketing
    Google AdSenseThis is an ad network. (Privacy Policy)
    Google DoubleClickGoogle provides ad serving technology and runs an ad network. (Privacy Policy)
    Index ExchangeThis is an ad network. (Privacy Policy)
    SovrnThis is an ad network. (Privacy Policy)
    Facebook AdsThis is an ad network. (Privacy Policy)
    Amazon Unified Ad MarketplaceThis is an ad network. (Privacy Policy)
    AppNexusThis is an ad network. (Privacy Policy)
    OpenxThis is an ad network. (Privacy Policy)
    Rubicon ProjectThis is an ad network. (Privacy Policy)
    TripleLiftThis is an ad network. (Privacy Policy)
    Say MediaWe partner with Say Media to deliver ad campaigns on our sites. (Privacy Policy)
    Remarketing PixelsWe may use remarketing pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to advertise the HubPages Service to people that have visited our sites.
    Conversion Tracking PixelsWe may use conversion tracking pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to identify when an advertisement has successfully resulted in the desired action, such as signing up for the HubPages Service or publishing an article on the HubPages Service.
    Statistics
    Author Google AnalyticsThis is used to provide traffic data and reports to the authors of articles on the HubPages Service. (Privacy Policy)
    ComscoreComScore is a media measurement and analytics company providing marketing data and analytics to enterprises, media and advertising agencies, and publishers. Non-consent will result in ComScore only processing obfuscated personal data. (Privacy Policy)
    Amazon Tracking PixelSome articles display amazon products as part of the Amazon Affiliate program, this pixel provides traffic statistics for those products (Privacy Policy)