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
- [Instructor] 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 tracking, it might be likely those workbooks will be the same every time 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 is plug in your values. I'll demonstrate how to work with templates in this movie.
My sample file is the year template workbook, and you can find it in the chapter two folder of your exercise files collection. This workbook is essentially an outline. I have years, months, and then a series of formulas in row 17 that will calculate the total for each year. If I want to save this workbooks as template so that I can use it later, I can press Command + Shift + S to open the save as dialogue box.
I will save it as, instead of year template, I'll call it MonthlyTemplate, and I'll leave the where the same, working in chapter two, and then for file format I'll click its down arrow and I will select Excel template XLTX, and that is the format for version of Excel after 2004. So I'll click that. I have my template. Click save, and now I have my file called MonthlyTemplate.
Now before I go any further, I will close this file. So I'll press Command + W. Now I will go to the chapter two folder, which I currently have displayed, and I will double click MonthlyTemplate.xltx, and there I have MonthlyTemplate1, which is the name of the file that I created. So just for demonstration purposes I'll type a value in cell B5. I'll make it 14,000 and press tab, and then I'll press Command + S.
When I do, Excel offers me the opportunity to provide a new name and new location for the file, and also note that it is saving it as a new Excel workbook instead of updating the template. The template will remain the same. I haven't made any changes to it. I've just made changes to a workbook created based on that template. In this case I won't save my change. I'll just press cancel and go back to the workbook, and one thing to note, and I'll press Command + Shift + P again to go back to the gallery, and this is what you'll see if you start Excel for the first time.
Here you can create based on a blank workbook, you can make a list, you can total a list, and so on, and clicking one of these templates allows you to create a new file based on them. So, for example, if I were to click personal budget and then click create, I get a new workbook, which is pre-formatted and I can use to create a new file. Also note the similar naming convention of Personal Budget1. When I press Command + S to save, it saves it as an Excel workbook and allows me to change the name.
As before I'll click cancel. Templates save you a lot of time and effort when you 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 replacing the template.
- Creating workbooks
- Manipulating cell data
- Sorting, filtering, and managing worksheets
- Using core functions and formulas
- Formatting worksheet elements
- Creating and managing conditional formats
- Working with charts
- Adding images and shapes
- Working with PivotTables
- Exporting workbooks
Skill Level Beginner
What you should know1m 11s
1. Getting Started with Excel
2. Managing Workbooks
3. Working with Worksheets, Cells, and Cell Data
4. Sorting, Filtering, and Managing Worksheets
5. Summarizing Data Using Formulas and Functions
6. Formatting Worksheet Elements
7. Working with Charts
8. Working with External Data and Objects
9. Exploring PivotTables
10. Reviewing and Sharing Spreadsheets
Further information1m 2s
- Mark as unwatched
- Mark all as unwatched
Are you sure you want to mark all the videos in this course as unwatched?
This will not affect your course history, your reports, or your certificates of completion for this course.Cancel
Take notes with your new membership!
Type in the entry box, then click Enter to save your note.
1:30Press on any video thumbnail to jump immediately to the timecode shown.
Notes are saved with you account but can also be exported as plain text, MS Word, PDF, Google Doc, or Evernote.