Working with Workbooks and Templates - My Excel 2016 (2016)

My Excel 2016 (2016)

2. Working with Workbooks and Templates

Image

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

Using templates

→ 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.

Image

2. Select New and then click Blank Workbook.

Image


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.

Image

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.

Image

5. Navigate in the Open dialog box to the file and select it.

6. Select Open.

Image

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.

Image

2. Select Open then Recent.

3. Select the desired file from the list.

Image


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.



>>>Go Further: Pin a Workbook to the List

The most recently opened workbook is always at the top of the list, but it will move down the list and eventually off of it unless you pin it.

A pushpin appears when you place your cursor over a workbook on the Recent list. The pushpin points to the left when not in use. If you place your cursor over the pushpin and click, the pushpin will face downward. The workbook will be moved to the top section of the list. A workbook with a pin facing down is stuck—that is, the workbook will not be removed from the list of recent workbooks until you unstick it by clicking the pushpin again.

Image

Image


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.

Image

2. Select Save As.

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.

Image


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.


Image


>>>Go Further: Choosing a Different File Type

By default, Excel saves your workbook with an .xlsx extension. However, if you have macros in your workbook or want to create a PDF, you must choose another Save As file type. Commonly used extensions are as follows:

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).

Image

If you find yourself often changing the Save As file type to something else, such as Macro-Enabled Workbook, you can change the default setting by clicking File, Options, Save and changing the Save Files in This Format value. You will still have the ability to select another file type when saving.


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.

Image

2. Select Close.

Image

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.

Image

2. Select New.

3. Type keywords in the search field and press Enter, or choose one of the recommended templates.

Image

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.

Image


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.

Image

2. Select Save As.

3. Select where the file will be saved, such as This PC.

4. Select Browse.

Image

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.

Image


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.

Image

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.

Image


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.



It’s Not All Good: Can’t Browse for Personal Templates

The personal templates list shows only templates stored in a configured location. There is no way to browse to another location when trying to open a template. Instead, you have to change the configured location where Excel looks for your personal templates. See the “Change Personal Templates Location” section for instructions on how to set up a new location.


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.

Image

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.

Image

5. Navigate in the Open dialog box to the template and select it.

6. Select Open.

Image


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:\Users\username\Documents\Custom Office Templates\). You can change this location to something more convenient for you.

1. Click File to open Backstage view.

2. Select Options.

Image

Image

3. Select Save.

4. Enter your preferred path in the Default Personal Templates Location field.

5. Click OK.

Image


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.