Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
One of the realities of working with Excel is that you often create files that are similar to files you created before; for example, if you create monthly sales reports, it's very likely those workbooks will be almost the same, except for the data they contain. If you create workbooks with the same structure more than once or twice a year, you should consider creating a template, which is a workbook that contains existing formatting and headings. All you need to do then is plug in your data. If you see the templates that are installed as part of Office 2011, you can go to the toolbar and click the New from template button.
When you do, you see the Excel Workbook Gallery, which Excel displays when you start the program, unless you turned that option off. So if there are a number of templates here. You'll see have a consultant time tracker. We have a weekly timesheet. In this case, what I'm looking to do is create monthly sales report. So rather than work with one of these templates, instead, I'll work with an existing workbook and turn that into a template. But just to show you real quick, if you do want to create a document based on one of these templates, you can click it, as I've done here with the Inventory Tracker - you see it's outlined in orange - and then I can press the Return key to create that document.
So there's my workbook. I can modify it and save it, if I want to make any changes and keep my data there. I don't in this case. I want to create my sales report using an existing file, so I will close this one, not saving any changes, and I'll start working with this file. When you create a template, what you want to keep are a generic title - one that can be changed easily to reflect the specific report you're creating - and also any headers that will remain constant. What you want to get rid of is any data that would be specific to a particular month. So, for example, in this case, I want to make this a sales report that can work for any month.
So I will delete the word 'April' from Sales Report, and I'm also going to delete the data here in the body of the worksheet, by selecting it, Ctrl+Click > Clear Contents. So now all I have is my heading, which can work for any month; my list of varieties; and the three sales channel: through the web, physical store and also any bulk orders from suppliers. To save this document as a template, I'll click File > Save As, and then in the Save As dialog box, I can change the format from Excel Workbook to Excel Template.
You'll notice an Excel template has a different file extension - in other words, the letters after the period at the end of the file name. A regular workbook has the extension xlsx. A template workbook, has the extension xltx. When I click it, it changes, and you'll also notice that the Where, in other words the folder where I'm saving it, changed to My Templates. It's important that you save your workbook templates in the My Templates folder. That way Excel can find them, and I'll show you how to do that in a moment.
So everything looks good. I'll just call it SalesReportTemplate to distinguish it from the other file. Everything else looks good. I'll click Save and the file name changed here on the title bar. So it is now our SalesReportTemplate.xltx. Now I'm going to close this file, go to File, and click New from Template. When I do, I can look here under the Templates, and click My Templates, and that's how you get to use these items over and over.
When you click on My Templates, Excel looks in your My Templates folder and displays them. So here we have Sales Report, which we created earlier. I've clicked it to highlight it. Press Return, and Excel creates a new workbook based on that template. If I want to save it for, say, May, I can change it to the May Sales Report and when I save it, Excel gives me a new workbook name. It still wants to save it in the My Templates folder, so I will change that to my Chapter02 exercise files, and I'll change the name to MaySalesReport.
Everything looks good. It's in the proper format as an Excel workbook, so I can click Save, and there it is. Templates save you a lot of time and effort we need to create workbooks based on a consistent structure. Just be sure to save the new workbook, the one that contains the data, as a regular file and not a template and also in the folder where you intend the file to go.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 97595 Viewers
80 Video lessons · 141082 Viewers
59 Video lessons · 59430 Viewers
52 Video lessons · 72750 Viewers
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.