(This video chapter begins at 00:28 and ends at 02:40. Click on the 00:28 timestamp to play the video for this module.)

Manage Workbook Options and Settings

This module looks at templates and other ways of working with multiple workbooks, like the proper way to reference data in another workbook or using structured references. We’ll also look at displaying hidden Ribbon tabs. Workbook Reviews incorporate options for marking a workbook as final, protecting it with a password or restricting editing. We’ll look at protecting a workbook structure and managing versions, as well as setting calculation options.

 

Our focus quote for this module:

“Productivity is being able to do things that you were never able to do before.” ~Franz Kafka

 

 

Manage Workbooks

Save a Workbook as a Template

Templates are a way of pre-designing a workbook so that you can create workbook quickly without having to start from scratch.

Show the participants how to save the current workbook as a template.

1 Select the File tab from the Ribbon to open the Backstage View.

2. Select Save As.

 

3. Select Excel Template (*xltx) from the Type drop down list.

Notice that when you make that selection, the location for the file changes to the default templates folder. Use this location to make it available for your use when creating new workbooks.

4. Give the template a new name if desired.

5. Select Save.

 

 

Reference Data in Another Workbook

You can create a reference to get values from another workbook.

Show the participants how to create an external reference.

1. In the cell where you want the reference, type =.

 

2. Click on the cell in other workbook that you want to reference.

3. Press Enter.

 

 

 

Reference Data by Using Structured References

When working with tables in Excel, you can reference table names and column names instead of explicit cell references. This is helpful in case you add or remove data from the table.

Show the participants how to create a structured reference.

1. When creating a formula, click on a table’s cells. The table name and item specifier are used in the formula instead of the explicit cell references.

2. Enter the close parenthesis and press Enter to enter the formula.

Display Hidden Ribbon Tabs

You can customize the Ribbon to display hidden tabs or hide tabs you don’t need.

Show the participants how to show the hidden Developer Ribbon tab.

1. Select the File tab from the Ribbon to open the Backstage View.
2. Select Options.

3. Select Customize Ribbon from the left side.

In the right column, Excel lists the tabs available in the application.

4. Check the box next to the Developer tab to show it.

You can also remove commands or rearrange them on the right column.

5. When you have finished, select OK.

Manage Workbook Review

Mark a Workbook as Final

There are a few different ways to restrict editing to prevent a workbook from unauthorized changes.

Show the participants how to mark a workbook as final.

1. Select the File tab to open the Backstage view.
2. Select Info if that tab is not already displayed.
3. Select Protect Workbook.
4. Select Mark as Final.

5. Select OK in the confirmation window.

 

6. In the confirmation window, select OK.

When the workbook is opened, a status bar warns the user that editing is discouraged.

 

 

Protect a Workbook with a Password

You can require a password before a workbook can be opened.

Show the participants how to password protect a workbook.

1. Select the File tab to open the Backstage view.
2. Select Info if that tab is not already displayed.
3. Select Protect Workbook.
4. Select Encrypt with Password.

5. Enter the password you want to use and select OK.

6. Reenter the password and select OK.

7. Save the file.

The Info tab displays the protected status.

 

 

Protect a Worksheet to Restrict Editing

When you protect a current sheet, you can control the types of changes other users can make to that sheet.

Show the participants how to protect a current sheet of a workbook.

1. Select the File tab from the Ribbon to open the Backstage View.
2. Select Protect Workbook.
3. Select Protect Current Sheet.

4. Excel displays the Protect Sheet dialog box.

5. You can enter a password if desired to prevent unprotected the sheet.

6. Check the boxes for the actions that you want to allow other users to perform on the sheet.

7. Select OK.

 

The Info tab displays the protected status.

 

 

 

Protect Workbook Structure

Protecting the workbook structure prevents adding, removing or moving sheets within the workbook.

Show the participants how to protect a workbook structure.

1. Select the File tab from the Ribbon to open the Backstage View.
2. Select Protect Workbook.
3. Select Protect Workbook Structure.

4. Excel displays the Protect Structure and Windows dialog box.

5. Check the boxes for the options you want to protect.

6. You can enter a password if desired to unprotect the workbook.

7. Select OK.

The Info tab displays the protected status.

Manage Workbook Versions

If your workbook is stored in One Drive, One Drive for Business or SharePoint, you can view historical versions of the workbook because these versions are saved automatically.

Show the participants how to view workbook versions.

1. Select the clock icon next to Share in the top right of the Excel window.

 

 

1. In the Activity pane, select Open version to view a read-only copy of a previous version.

2. The previous version opens as read-only in a new window with options to Compare or Restore the version.

 

 

Setting Calculation Options

Large workbooks calculate more slowly than smaller ones, which can lead to errors generated by the slower response time. Prevent errors by controlling how calculation occurs in a workbook.

 

Show the participants how to set calculation options at the workbook level.

1. Select the Formulas tab from the Ribbon.
2. Select Calculation Options.

3. Select one of the calculation options.

4. If you do not use Automatic calculation, you can use the Calculate Now and Calculate Sheet commands in the same group on the Formulas