Chapter 8: Workbook Settings, Protection, and Collaboration

This chapter starts the MO-211 material. MO-211 assumes you can do everything on MO-210, and it adds skills for workbooks that several people use or that depend on other files. This chapter covers the first objective domain, Manage workbook options and settings: linking workbooks together, managing versions, protecting what others can change, and controlling when formulas recalculate. The macro settings in the same domain are in chapter 12.

8.1 Referencing other workbooks

A formula can use a value from another workbook. This is called an external reference or a link. Use one when the data is maintained in a separate file, such as when each department keeps its own budget and a summary workbook adds them up.

8.1.1 Creating an external reference

The easiest way is to click the cell you want while you build the formula:

1. Open both workbooks.
2. In the destination workbook, select the cell and type = (or start a formula, such as =SUM().
3. Switch to the source workbook (View › Switch Windows, or click it on the taskbar), go to the right sheet and click the cell or select the range.
4. Press Enter. Excel returns you to the destination workbook.

The formula now includes the workbook's name in square brackets:

='[Marketing Budget.xlsx]FY27'!$D$14

Notice three things:

• The workbook name is in square brackets, followed by the sheet name and an exclamation point.
• The single quotation marks are needed because the names contain spaces.
• Excel makes the reference absolute ($D$14). If you'll copy the formula and want the reference to change, remove the dollar signs.

When the source workbook is closed, Excel shows the full path:

='C:\Budgets\[Marketing Budget.xlsx]FY27'!$D$14

You can type an external reference by hand, but clicking avoids typos. External references work in any formula, such as =SUM('[Marketing Budget.xlsx]FY27'!$D$4:$D$13).

8.1.2 Managing links

When you open a workbook with links, Excel may show a security warning and ask whether to update them. Click Enable Content to allow updates from the source files.

To see and manage every link in the workbook, choose Data › Edit Links (in some versions, Data › Workbook Links, which opens a pane):

Command	What it does
Update Values / Refresh	Gets the current values from the source workbook.
Change Source	Points the links at a different file, such as a newer version.
Open Source	Opens the source workbook.
Break Link	Replaces every formula that uses the link with its current value. This can't be undone.
Check Status	Shows whether each source file can be found.
Startup Prompt	Chooses whether Excel asks to update links when the workbook opens.

8.2 Workbook versions

Excel saves earlier versions of your work in two ways, depending on where the file is stored.

8.2.1 Version history

For a workbook stored in OneDrive or SharePoint, Excel saves a version every time the file is saved, including by AutoSave. To see them, choose File › Info › Version History, or click the file name in the title bar and choose Version History. The Version History pane lists each version with its date, time and author.

• Click Open version to open an older version in a separate, read-only window.
• In that window, click Restore to make that version the current one. The version you replaced stays in the history, so restoring can be undone.
• To copy only part of an old version, open it, copy what you need, and paste it into the current workbook.

8.2.2 AutoRecover and unsaved workbooks

For files stored on your computer, AutoRecover saves a recovery copy every few minutes so you lose little work if Excel crashes. Its settings are in File › Options › Save:

• Save AutoRecover information every n minutes sets how often.
• Keep the last AutoRecovered version if I close without saving keeps a copy when you close without saving.
• AutoRecover file location is the folder where the copies are kept.

To get back work you didn't save, choose File › Info › Manage Workbook › Recover Unsaved Workbooks, or open File › Open and click Recover Unsaved Workbooks at the bottom of the recent files list. Open the file and save it with Save As.

8.3 Protecting worksheets and ranges

8.3.1 How protection works

Protecting a worksheet takes two steps:

1. Decide which cells people may change. Every cell starts out locked. Locking has no effect until the sheet is protected. Unlock the cells people should be able to edit, such as input cells.
2. Protect the sheet. Locked cells can no longer be changed. Unlocked cells can.

To unlock cells, select them, open Format Cells (Ctrl+1), go to the Protection tab and uncheck Locked. (You can also use Home › Format › Lock Cell, which toggles the setting.) On the same tab, Hidden hides a cell's formula in the formula bar once the sheet is protected. The result still shows in the cell.

8.3.2 Protecting a sheet

Choose Review › Protect Sheet (or Home › Format › Protect Sheet). In the dialog box:

• Password to unprotect sheet is optional. Without one, anyone can unprotect the sheet. With one, they need the password. If you forget it, you can't unprotect the sheet.
• Allow all users of this worksheet to lists what people may still do, even to locked cells. By default only Select locked cells and Select unlocked cells are checked. Others include Format cells, Format columns, Format rows, Insert rows, Delete rows, Sort, Use AutoFilter, Use PivotTable & PivotChart and Edit objects.

To remove protection, choose Review › Unprotect Sheet and enter the password if there is one.

8.3.3 Allowing edits to specific ranges

Allow Edit Ranges lets particular people edit a range of a protected sheet, or lets anyone who knows a password edit it. Set it up before protecting the sheet:

1. Choose Review › Allow Edit Ranges (in some versions, Review › Protect › Allow Edit Ranges).
2. Click New. Give the range a Title, set Refers to cells, and optionally type a Range password.
3. Click Permissions to choose people who can edit without the password (on a network that supports it).
4. Click OK, then click Protect Sheet in the same dialog box to turn on protection.

When someone tries to edit that range, Excel asks for the range's password, not the sheet's.

8.4 Protecting workbook structure and restricting editing

8.4.1 Protecting workbook structure

Protecting a worksheet controls its cells. Protecting the workbook structure controls its sheets: once it's on, nobody can add, delete, rename, move, copy, hide or unhide worksheets.

Choose Review › Protect Workbook, make sure Structure is checked, type an optional password and click OK. Choose the command again to remove the protection.

8.4.2 Other ways to restrict editing

File › Info › Protect Workbook gathers several more ways to control what others can do:

Option	What it does
Always Open Read-Only	Asks people to open the workbook read-only, so they must save changes under a new name. They can still choose to edit.
Encrypt with Password	Requires a password to open the workbook at all.
Protect Current Sheet	The same as Review › Protect Sheet.
Protect Workbook Structure	The same as Review › Protect Workbook.
Add a Digital Signature	Signs the workbook so others can tell whether it changed after signing.
Mark as Final	Makes the workbook read-only and tells readers it's finished. Anyone can click Edit Anyway, so it's a signal, not security.

Save As offers two more passwords. Choose File › Save As › More options, then Tools › General Options in the Save As dialog box:

• Password to open works like Encrypt with Password.
• Password to modify lets anyone open the workbook read-only but requires the password to save changes to it.
• Read-only recommended suggests opening read-only, like Always Open Read-Only.

8.5 Calculation options

Normally Excel recalculates every formula that's affected by a change as soon as you make it. In a very large workbook that can be slow, so Excel lets you control when it calculates.

8.5.1 Choosing a calculation mode

Choose Formulas › Calculation Options:

• Automatic, the default, recalculates after every change.
• Automatic Except for Data Tables recalculates everything except what-if data tables, which can be slow.
• Manual recalculates only when you ask. The status bar shows Calculate when results are out of date.

In Manual mode, recalculate with these commands:

• Calculate Now (Formulas › Calculate Now, or F9) recalculates the whole workbook.
• Calculate Sheet (Shift+F9) recalculates only the active sheet.

8.5.2 More calculation settings

File › Options › Formulas has the same choices under Workbook Calculation, plus:

• Recalculate workbook before saving, available in Manual mode, makes sure saved results are current.
• Enable iterative calculation allows formulas that refer to their own result (circular references) to calculate repeatedly. Maximum Iterations and Maximum Change decide when to stop. Turn it on only when a model is designed to use a circular reference on purpose.

8.6 Practice