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