Section 23.4. Sharing Your Workbook | Excel 2007[c] The Missing Manual

23.4. Sharing Your Workbook

Change tracking is really just one part of a larger feature known as workbook sharing . Workbook sharing makes it possible for more than one person to modify an Excel document at the same time. In order for this sharing to work, the workbook file needs to be placed somewhere that both people can access. (The most typical location is somewhere on a company network.)

Workbook sharing is a little risky (as you'll soon see), but it gives you some unique collaboration abilities that you wouldn't have otherwise . To get some perspective on how workbook sharing works, you need to understand what happens when two people fight over a workbook file that isn't shared. Read on.

23.4.1. Multiple Users Without Workbook Sharing

Ordinarily, only one person can open an Excel workbook file at a time. Excel enforces this restriction to prevent problems that can occur when different people try to make conflicting changes at the same time and someone's changes get lost.

Figure 23-16 shows what happens when you try to open an Excel workbook that someone else is already using. Excel warns you about the problem but gives you the chance to open a copy of the document that you can save with a new name . If you need to update the version that's currently in use, you can ask Excel to notify you when your collaborator closes it and the workbook becomes available again.

Figure 23-16. Top: If you try to open a workbook that someone else is using, you receive the message shown here. You can click Read Only to work with a copy of the file, which you'll need to save with a new name. If you do so, Excel opens a copy of the file and lets you start working with it.
Bottom: Once the first person closes the workbook, Excel alerts you with a new message. At this point, you can click Read-Write to open the file for editing (at which point no one else will have access to the file). Excel automatically closes the copy you were previously working with and applies all the changes you made in your copy to the current version.

Taking turns editing a file works well enough, but it has one significant problem: You never know when the file will become available. If someone opens a workbook and then leaves for a two-week vacation without shutting off his computer, you'll be completely locked out of the workbook. Worst of all, you have no way to know when the workbook will finally be available.

And if you make changes to a copy while waiting for someone else (using the same file) to release the original workbook, more problems occur. The problem is that when you make your changes, you're actually working with a separate copy of the workbook file. If the other person makes any changes to the original workbook file, you'll find yourself in a sticky situation. Excel forces you either to save your copy as a separate file or discard all your hard work before allowing you to open the original copy (see Figure 23-17). You have only two solutionsdiscard your changes completely, or save the workbook with a new name and then open the other person's version.

Figure 23-17. While waiting for PartyPlanning.xlsx to become available, the person using this file made changes to the copy. Now that PartyPlanning.xlsx is available, Excel notices that the first person also changed the original PartyPlanning.xlsx file at the same time.

For all of these reasons, it usually makes more sense to use workbook sharing if you know more than one person needs to access or change the same workbook at the same time.

23.4.2. Turning On Workbook Sharing

It's quite easy to turn on workbook sharing. In fact, it takes only a couple of steps:

Select Review Changes Share Workbook .

The Share Workbook dialog box appears.
Turn on the "Allow changes by more than one user at the same time" checkbox .
Select the Advanced tab .

The Advanced tab is shown in Figure 23-18.

If you don't want to use change tracking, turn off the checkmark next to the "Don't keep change history" option .

When you share a workbook, Excel assumes you want to use change tracking. That way, if a conflict occurs and one person overwrites another person's changes, you can still find the discarded change in the log if you need it. If you don't want Excel to save changes, take this opportunity to turn change tracking off.

Figure 23-18. The Advanced tab lets you configure whether change tracking is used, when separate workbook copies are synchronized, and how Excel handles conflicting changes. The bottom two checkboxes ("Print settings" and "Filter settings") indicate that Excel stores some private information that it doesn't share with the documentnamely, your personal printer settings and any view settings (like the position of the window at the current zoom magnification). These settings can't be synchronized because they may apply only to your computer.

In the "Update changes" section, choose how often you want to be informed about changes .

When workbook sharing is switched on, everyone editing the workbook has a separate copy of the workbook. The difference is that each person can save changes to the original workbook file. When you save your copy of the workbook, Excel examines the changes that your collaborators have saved and then refreshes your copy of the workbook accordingly .

Alternatively, you can tell Excel to save your workbook periodically to make sure it stays up to date. To switch on this behavior, change the "When file is saved" option to "Automatically every 15 minutes". (If you want to update more or less frequently, you can change the number of minutes.) Then, select the "Save my changes and see others' changes" option.

Finally, you can choose to refresh your document periodically without actually saving your changes. Simply change the "When file is saved" option to "Automatically every 15 minutes", and select the "Just see other users' changes" option.
If you wish, select one of the settings for "Conflicting changes between users" setting .

Here's your chance to tell Excel what to do if the changes from more than one person conflict with each other. Keeping the standard "Ask me which changes win" setting usually makes sense because Excel explains any conflicts and show you the changes that were made earlier. If you choose "The changes being saved win", Excel automatically overwrites conflicting changes without even warning you that important data could be lost or alerting you that a conflict occurred.
Click OK .

Excel saves the workbook automatically. You can tell that it's shared because the word [Shared] appears in the title bar.

When you're working with a shared workbook, Excel keeps track of the number of people involved. To see who's working on the workbook at any given moment, select Review Changes Share Workbook. Figure 23-19 shows an example.

Figure 23-19. In this example, two people have the workbook open at the same time. One of Excel's oddest features is the ability to stop people from saving a shared workbook if you select them in the list and click the Remove User button. This feature has no limits, which means that, in this example, Matthew can remove Faria and, similarly, Faria can remove Matthew if she gets to the setting first. When people are removed from the list, they can't save their changes to the shared workbook. Instead, Excel lets them save a copy only under a new file name. This removal process is temporary, however. If Matthew removes Faria, there's no reason that Faria can't open the workbook after Matthew closes it. This anything-goes attitude can make life a bit chaotic if you let multiple people edit the same workbook at once.

Note: Remember, once you turn on workbook sharing, Excel completely disables certain features (like inserting charts or pictures). For the full list, see the box in Section 23.3.2.

23.4.3. Workbook Sharing in Action

When workbook sharing is switched on, there's no limit to how many people can open the same workbook at once. However, each person actually has a separate copy of the workbook. Excel automatically synchronizes your copy when you save changes (or at fixed intervals, depending on the "Update changes" option).

This approach works well when people change different portions of the workbook. However, it's entirely possible that two people could choose to edit the same cell at once. Sooner or later, both people will try to save their changes.

Imagine that Faria changes cell A1 at the same time that Ricardo modifies A1. Faria saves her changes and doesn't experience any problem. Shortly after, Ricardo tries to save his changes. At this point, Excel warns Ricardo that his changes conflict with the changes someone else already made. Ricardo now needs to decide whether to overwrite Faria's changes or discard his own. Figure 23-20 shows an example of the message you may see in this situation.

Figure 23-20. In this example, Ricardo has just tried to save his copy of a shared workbook. However, one change conflicts with a change Faria made. Ricardo can choose Accept Mine to discard Faria's change or click Accept Other to leave the workbook as it is.

Here's a clear problemthe last person to make changes gets to make decisions about someone else's work. There's no way to avoid this limitation of shared workbooks. Excel doesn't provide any way to stop one person from obliterating a co-worker's edits.

In fact, even if changes don't directly conflict, simultaneous edits can still clash . Imagine you have two cells (A10 and B10) that don't quite match up the way they should. (A10 may have the price of a product and B10 may have a discounted price, whichoddly enoughis higher.) One person tries to fix the problem by modifying A10. At the same time, another person tries to resolve the same problem by modifying B10. The end result? Both A10 and B10 get changed, Excel doesn't complain, but they still don't match up. These kinds of things make workbook sharing a high-stakes, life-in-the-fast-lane approach.

Note: Workbook sharing works best when multiple people need to read a workbook and only one person needs to edit it. In this situation, the chance of conflicting changes is low, and people who just want to print the data aren't inconvenienced while it's in use. As a rule of thumb, try to separate the workbook so that different people will update the data on different worksheets, which won't cause a conflict.