2. Working with Workbooks and Templates
In this chapter, you’ll learn how to interact with workbooks and create your own custom templates. Topics in this chapter include the following:
→ Opening, saving, and closing workbooks
→ Creating your own template
Most of the commands for working with workbooks, including templates, are found on Backstage view, accessed by clicking the File tab. You don’t always have to start from scratch when creating a workbook—you can use templates offered online from Microsoft or create your own.
Managing Workbooks
Workbook is another name for an Excel file. It’s the workbook you open, save, and close. This section shows you how to create a new workbook, open an existing workbook, and save and close a workbook.
Create a New Workbook
When you open Excel, it already makes a new workbook available, but you can always create another.
1. Click File to open Backstage view.
2. Select New and then click Blank Workbook.
Do It Quicker
Use the keyboard shortcut Ctrl+N to create a new workbook.
Open an Existing Workbook
If you need to open an existing workbook, you have to tell Excel where it is so it can open the workbook.
1. Click File to open Backstage view.
2. Select Open.
3. Select where the file is located, such as This PC.
4. If the desired folder appears in the list to right, select it then click on the file to open. If it is not listed, choose Browse.
5. Navigate in the Open dialog box to the file and select it.
6. Select Open.
Use the Recent Workbooks List
The Recent Workbooks list makes it easy to open files you’ve recently worked on.
1. Click File to open Backstage view.
2. Select Open then Recent.
3. Select the desired file from the list.
Remove a Workbook from the List
To remove a workbook from the list, right-click it and select Remove from List.
Change the Number of Recent Workbooks
By default, the Recent list shows the most recent 25 workbooks you opened. You can change this by clicking File, Options, Advanced. Then under Display, change the value for Show This Number of Recent Workbooks.
Save a Workbook
When you click the File tab, you see two options for saving your workbook: Save and Save As. Selecting Save saves the workbook with the current name, overwriting the existing file. If the workbook is new and doesn’t have a filename yet, clicking Save opens the Save As dialog box. The Save As dialog box allows you to save the active workbook with a new name in a different location.
1. Click File to open Backstage view.
3. Select where the file will be saved, such as This PC.
4. If the desired folder is listed, such as Documents, select it. Otherwise, choose Browse.
5. Navigate in the Save As dialog box to the folder you want to save in.
6. Enter the filename.
7. Select Save.
Quick Saves
If the workbook already has been saved and you just want to save your work without renaming the file or creating a copy, you can use the Save icon on the QAT.
>>>Go Further: Choosing a Different File Type
• Excel Workbook (.xlsx)—A workbook without macros.
• Excel Macro-Enabled Workbook (.xlsm)—A workbook with macros.
• PDF—The Portable Document Format can be used to distribute your report while still preventing users from making changes (because you retain the original, editable file).
Close a Workbook
When you’re done working with a workbook, you need to close it. If changes have been made (including recalculating formulas) since the last save, Excel will prompt you to save the file. When the final workbook is closed, Excel shuts down.
1. Click File to open Backstage view.
2. Select Close.
Using Templates to Quickly Create New Workbooks
Templates are a great way for keeping data in a uniform design. You could simply design your workbook and reuse it as needed, but if you accidentally save data before you have renamed the file, your blank workbook is no longer clean. When using a template, there’s no risk of saving data in the template because you are working with a copy of the original workbook, not the workbook itself.
Use Microsoft’s Online Templates
Microsoft offers a variety of templates, such as budgets, invoices, and calendars, to help you get a start on a project.
1. Click File to open Backstage view.
2. Select New.
3. Type keywords in the search field and press Enter, or choose one of the recommended templates.
4. A preview of the selected template is shown. If you want to create a workbook based on this template, click Create. Excel will download the file (the first time) and then open it.
5. If you don’t want to use the previewed template, click the X in the top-right corner to close it.
Pinning Templates
When you place your cursor over a template icon, a pushpin appears in its lower-right corner, giving you the option to pin it so that it always appears in the list. If you found your template by searching for it, this is a way to make the template easily accessible the next time you need it.
Save a Template
You can create your own templates by starting a new workbook, setting it up as you like it, and then saving it as a template. The important thing here is to save it with the correct extension—either as *.xltx, if it doesn’t have any macros, or as *.xltm if it does include macros.
1. Click File to open Backstage view.
2. Select Save As.
3. Select where the file will be saved, such as This PC.
4. Select Browse.
5. Select the extension for your file. If the workbook doesn’t have any macros, choose Excel Template (*.xltx); if it does include macros, choose Excel Macro-Enabled Template (*.xltm).
Undoing Excel’s Location Change
When you choose a template file type, Excel automatically changes the Save As location to the default personal template location. If that’s not where you want to save the file, but you had the desired location selected before choosing the file type, click the Back arrow.
6. Type the filename.
7. Click Save.
If You Want to Save in Another Location
You do not have to save templates to the configured templates location if you plan to use the double-click or right-click method to open a copy of the file.
Open a Locally Saved Template to Enter Data
You aren’t limited to the templates available online. You can use templates saved on your computer.
1. Click File to open Backstage view.
2. Select New.
3. Select Personal. The Personal option is not available unless a personal template location has been specified. See “Change Personal Templates Location” later in this chapter to define this location if needed.
4. Click the template you want to work with to open a copy of it.
Other Ways to Open Templates
If you have easy access to a template (for example, on your desktop), you have two other options for opening a template to work with: double-click the file from its saved location or right-click the file and select New.
Edit the Design of a Locally Saved Template
If you do open a template using one of the previous methods and make changes, you must use the Save As option and save it as a new template. If you want to edit the original file, you’ll need to open the actual file, not create a copy of it.
1. Click File to open Backstage view.
2. Select Open.
3. Select where the file is located, such as This PC.
4. If the desired folder is listed, select it. Otherwise, click Browse.
5. Navigate in the Open dialog box to the template and select it.
6. Select Open.
Open It Faster
If you have easy access to a template (for example, on your desktop), you can edit it by right-clicking the file and selecting Open.
Change Personal Templates Location
The first time you save a template, Excel take you to the default personal templates location, C:UsersusernameDocumentsCustom Office Templates). You can change this location to something more convenient for you.
1. Click File to open Backstage view.
2. Select Options.
3. Select Save.
4. Enter your preferred path in the Default Personal Templates Location field.
5. Click OK.
Ensure the Path Exists
The location must exist before you enter it in the field. To make sure you get the path correct, navigate to the folder in File Explorer, copy the full path from the address bar, and then paste it in the Excel field.