6.3 Templates

Learning Objectives

  1. Use existing Microsoft Excel templates to create new spreadsheets.

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. Excel for Mac icon 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.
Shows the New Template Screen in Backstage View. The Search for online templates box is highlighted.
Figure 6.9 New Template Screen in Backstage View

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.

Partial view of template with large, black title bar at top: "Travel Expense Report." Three decorative images at left of title bar: airplane, train, car. Fields beneath title bar: Name, Department, Period, Authorized by, Date Submitted. Table beneath fields with columns such as Date, Description of Expense, Airfare, Lodging, etc. Rows of completed entries with a bold totals row at bottom.
Figure 6.10 Travel Expenses Report Template

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. Excel for Mac icon 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.
    Figure 6.11 Class Schedule Template
  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:

  1. Click on the File tab in the ribbon.
  2. Click on New
    Excel for Mac icon Mac Users: Click the File option and choose New from Template
  3. Type the desired template description in the Search box, and press ENTER.

Key Takeaway

  • There are many pre-designed templates in Excel developed in Excel that you can use.  This will save you the time and effort of designing and creating these files from scratch.

Attribution

“6.3 Templates” by Mary Schatz and Diane Shingledecker, Portland Community College is licensed under CC BY 4.0

License

Icon for the Creative Commons Attribution 4.0 International License

Beginning Excel 2019 by Noreen Brown, Barbara Lave, Hallie Puncochar, Julie Romey, Mary Schatz, Art Schneider, and Diane Shingledecker is licensed under a Creative Commons Attribution 4.0 International License, except where otherwise noted.