6.3 Templates

Learning Objectives

A template is a predefined pattern for a spreadsheet that has already been created for you. Hundreds of templates, already created by Microsoft, are available for you to use in Excel. These templates are very helpful if you have limited time to get a new task done in Excel, and you don’t know where to start. Templates do a lot of the work for you! Templates include all the formulas, formatting, etc. needed in a professional Excel spreadsheet. All that’s left to do is enter the data. Predefined Microsoft templates include everything from billing statements to blood pressure trackers to business cards. Depending on your version of Excel or Office 365, template categories may include: Business, Personal, Planners and Trackers, Lists, Budgets, Charts, and Calendars. In this chapter we will explore using existing Microsoft templates.

To access the templates in Excel, do the following:

1. Click the File tab in the ribbon.
2. Click New in Backstage View.  Mac Users: Click the File menu option and choose New from Template
3. The top of your screen should look similar to Figure 6.9. If you have opened templates previously, these may show in this screen as well.

In the Search for online templates box shown above, you can type a description for the template you want to use. Let’s start by searching for a Travel Expense Report.

1. Click in the Search for online templates box.
2. Type Travel Expense and press ENTER.
3. Click on the Travel Expense Report that looks like Figure 6.10 below and click Create.  NOTE:  If this template is not available, select a similar one.

Your screen should look similar to Figure 6.10 below. Notice the design, layout, and formulas have already been set up for you.

Try using this template by doing the following:

1. Change the Name to your name.
2. Change the Department to Technology.
3. Hold down CTRL and click ~  to see where the formulas are in the sheet.  Working in formula view helps you see where the formulas are, so you won’t delete them.
4. In formula view, carefully delete just the data, including the Description of Expense and the dollar amounts. Don’t delete any formulas!
5. Hold down CTRL and click ~ again to return to Normal view.
6. Enter Dates, Descriptions and expenses for a trip of your imagining in the first three rows under the column headings.
7. Save the completed file as CH6 Travel Expenses. Close the file.

We will practice using one more Excel template to create a class schedule. If you already know your schedule for next term, you can use it to complete this template. Otherwise, use your current class schedule.

1. Click the File tab in the ribbon.
2. Click New in Backstage View.  Mac Users: Click the File menu option and choose New from Template
3. Click in the Search for online templates box.
4. Type Class Schedule and press Enter.
5. Click on the Student schedule that looks like Figure 6.11 below and click Create.  NOTE:  If this template is not available, select a similar one.
   

   

6. Click on the Class List sheet. Replace the information in the Class, ID, Day, Location, Start Time and End Time columns with your own class schedule. In the Day column, use the dropdown arrow in the lower right corner of the cell to select the day of the week.
7. Click back on the Class Schedule sheet to view your completed schedule. Click in cell H2, then click the dropdown arrow in the lower right corner and select 60 MIN for the Time Interval.
8. Save the completed file as CH6 Class Schedule. Close the file.

Skills Refresher

To use a Microsoft predefined template:

Key Takeaway