It has always been possible to share Excel files on a network. You just had to be sure you coordinated your efforts to avoid having more than one person open a file at the same time. With recent versions of Excel, however, two or more people can work on the same workbook simultaneously. In Excel 2010, click the Review tab, and then click Share Workbook in the Changes group on the ribbon to open the Share Workbook dialog box shown in Figure 25-3.
Figure 25-3. Select the Allow Changes By More Than One User At The Same Time check box to share the workbook.
When you select the Allow Changes By More Than One User At The Same Time check box and click OK, Excel displays a confirmation prompt and then saves the workbook. This step is necessary because the workbook must be saved as “sharable” before another user can open it. After you save the workbook, [Shared] appears in the title bar whenever anyone opens the workbook, and it remains until you turn off sharing.
Note
To change the name users see in the Share Workbook dialog box when they work with a shared file, click the File tab, and then click Options to open the Excel Options dialog box. In the General category, edit the User Name box.
Of course, there are inherent risks when people work in the same file at the same time. Conflicts can arise when several people are making changes that affect the same cells. When someone saves changes, Excel not only saves the workbook but also updates it if other users save any changes. A dialog box informs you that Excel has incorporated your changes. After you save, Excel outlines changes that have been made by other people with a colored border and adds a special cell comment to explain who did what when. When you point to the cell, a comment box displays this information, as shown in Figure 25-4.
Note
When a shared file has been edited by another user, the triangular comment indicators specifying changed cells appear in the upper-left corner of the cell instead of in the upper-right corner, as they do for standard cell comments.
Note
Change tracking, which determines whether outlines and comment boxes appear in your worksheet, is turned on separately. You can control change tracking by clicking the Review tab, clicking Track Changes, and then clicking Highlight Changes. In the Highlight Changes dialog box, select the Track Changes While Editing check box. Be sure this check box is selected before you save the worksheet for sharing if you want to track and review changes later. For more information, see Tracking Changes on page 865.
When you save a shared file, Excel looks for conflicts and determines whether any mediation is necessary. Usually, a dialog box appears after you save the file to inform you that Excel has incorporated changes made by other users. However, if others’ changes involve any of the same cells you changed, the mediator arrives in the form of the Resolve Conflicts dialog box shown in Figure 25-5.
Figure 25-5. If more than one person changes the same cells, the last person to save changes might get to decide which ones to keep.
Note
When setting up a multiuser workbook, establish some working guidelines, and design the workbook for maximum safety. For example, each person could have a separate named worksheet in the workbook, with each worksheet reflecting a specific area of responsibility. Then you could create a separate consolidation worksheet that pulls together all the relevant data from the personal worksheets to present it in the necessary format. For more information, see Consolidating Worksheets on page 280.
For each conflict identified, the Resolve Conflicts dialog box specifies the cells involved and lets you decide whose changes to keep. You can resolve conflicts individually or use the buttons at the bottom of the dialog box to accept all the changes entered by you or others. You must resolve the conflicts to save the workbook. If you click Cancel, Excel will not save the workbook.
Note that conflicts can exist only between the last saved version and the version you are trying to save. If more than two users have made changes to the same cells, each person who saves the workbook gets to decide who wins the conflict of the moment. You can, however, revisit all the conflicts and accept or reject them individually later.
Note
For more information about accepting or rejecting changes individually, see Reviewing Changes on page 868.
You can change some aspects of the default behavior of shared workbooks. To do so, click the Review tab, and then click Share Workbook to open the Share Workbook dialog box. Click the Advanced tab, shown in Figure 25-6. (The options on this tab are not available if you haven’t selected the Allow Changes By More Than One User At The Same Time check box on the Editing tab.) Each shared workbook user can set these options individually. Use the first area on the Advanced tab to specify the length of time you want to keep track of changes or whether you want to track them at all. Excel keeps the change history for only the number of days you specify. If you need to track changes but are unsure how long you want to track them, set a high number (such as 999 days).
Note
Turning off change tracking detracts from your ability to merge workbooks. For more information, see Combining Changes Made to Multiple Workbooks on page 869.
In the Update Changes area, select when you want updates to occur. Ordinarily, when a user saves a file, Excel saves the changes and also updates the copy of the workbook with any changes made by others. The Automatically Every option is handy, letting you specify how often updates occur automatically. When you choose automatic updating, the usual procedure is as described previously: Excel saves your changes and incorporates changes made by others into your copy. You also can select the Just See Other Users’ Changes option, which gives you the ability to hold your changes back until you decide to save them, while at the same time updating your file at regular intervals with any changes recorded by others. This is a good workbook management technique, particularly if your team includes users who aren’t in the habit of regularly saving their changes.
As mentioned previously, when conflicts arise, the Resolve Conflicts dialog box shown in Figure 25-5 appears. However, if you select The Changes Being Saved Win in the Conflicting Changes Between Users area on the Advanced tab in the Share Workbook dialog box, Excel essentially resolves all conflicts in favor of the last user to issue the Save command. Click OK to dismiss the Resolve Conflicts dialog box and return to the Share Workbook dialog box.
With the Include In Personal View check boxes, you can change the print settings and any views set with the Filter or Advanced Filter command on the Data tab. With these check boxes selected, each person who has a shared workbook open can have different print and filter settings, which are recalled the next time that person opens the shared workbook.
Tip
INSIDE OUT Password-Protect Workbooks Before You Share
You can use the standard Excel password-protection options with shared workbooks, but you must apply the password before sharing. Click the File tab, and then click Save As. Click the small arrow next to Tools, and then click the General Options command. In the File Sharing area, you can type a password for opening the workbook and another password for modifying the workbook. Click OK to save your changes. Then you can disseminate the necessary passwords to members of your workgroup. For more information about file protection, see Protecting Files on page 57.
Change tracking in Excel is closely linked with shared workbooks. To turn on change tracking, click the Review tab, click Track Changes, and then click Highlight Changes to display the dialog box shown in Figure 25-7. In the Highlight Changes dialog box, select the Track Changes While Editing check box, which puts your workbook into shared mode and saves it, just as if you had clicked the Share Workbook command. Even if you select the Don’t Keep Change History option on the Advanced tab in the Share Workbook dialog box, shown in Figure 25-6, you can still turn on change tracking by using the Track Changes commands.
Tip
INSIDE OUT Track Changes Without Sharing
You don’t have to share a workbook to track the changes you make. Just turn on change tracking and save the workbook in an unshared folder on your own hard disk instead of in a shared network location.
You control which changes you want Excel to highlight. Use the When list to select whether you want to see all the changes made since the workbook was first shared, only those changes you haven’t yet reviewed, those changes that have been made since the last time you saved, or those changes that have been made since a date you specify. The Who options include Everyone, Everyone But Me, and the name of every individual who has made changes to the shared workbook. If you want, you can type a specific cell or range in the Where box. If you select the check box next to the Where option, you can drag to select the cells directly on the worksheet while the dialog box is still open.
Ordinarily, the changes are highlighted on the screen with cell borders and cell comments are attached. Clear the Highlight Changes On Screen check box to turn off this option. You can also create a history worksheet detailing all the changes made. To do so, select the List Changes On A New Sheet check box (which is unavailable until you have actually made some changes). The resulting worksheet is inserted after the last worksheet in the workbook, as shown in Figure 25-8.
Figure 25-8. You can choose to create a history worksheet detailing the changes made to a shared workbook.
The history worksheet is a special locked worksheet that can be displayed only when a worksheet is in shared mode. The worksheet disappears when you turn off change tracking. If you subsequently restart a shared workbook session, the history starts fresh, and any changes recorded in previous sharing sessions are lost.
If you want to ensure that Excel records every change made during a sharing session, click the Review tab, and then click Protect Shared Workbook. (The command is labeled Protect And Share Workbook if the workbook is not already shared.) The Protect Shared Workbook dialog box, shown in Figure 25-9, appears.
If you select the Sharing With Track Changes check box and then click OK, no one in your workgroup can directly turn off change tracking for the shared workbook. However, anyone can turn off the protection by turning off sharing for the workbook. To eliminate this possibility, you can type a password in the Protect Shared Workbook dialog box. But you must do this when the workbook is not in shared mode. Anyone who tries to turn off protection must type the identical, case-sensitive password.
Note
Successfully typing a password to turn off sharing protection not only turns off protection but also removes the workbook from sharing. Note that this isn’t the case unless the workbook has a password. When you remove a workbook from sharing, you cut off anyone else who has the workbook open, and Excel erases the change history.
You can decide at any time to go through each change that users have made to the shared workbook, provided that you selected the Track Changes While Editing check box in the Highlight Changes dialog box when you first saved the worksheet for sharing. Clicking the Review tab and then clicking Track Changes, Accept/Reject Changes on the ribbon saves the workbook and displays the Select Changes To Accept Or Reject dialog box shown in Figure 25-10. The When, Who, and Where lists are similar to those in the Highlight Changes dialog box, except that in the When list, the only options available are Not Yet Reviewed and Since Date.
Figure 25-10. Use the Select Changes To Accept Or Reject dialog box to specify which changes you want to review.
When you click OK, the Accept Or Reject Changes dialog box shown in Figure 25-11 appears, and on the worksheet, Excel highlights the first change that meets the criteria you specified in the Select Changes To Accept Or Reject dialog box. (If the cell in question has been changed more than once, Excel lists each change for that cell in the dialog box, and you can select one to accept.) The dialog box describes the change, who made it, and the time it was made. At this point, you can accept or reject the change, or you can accept or reject all changes. After you accept or reject all the changes, you cannot review them again. You can, however, still display the history worksheet.
You can discontinue the sharing session at any time by clearing the Allow Changes By More Than One User At The Same Time check box on the Editing tab in the Share Workbook dialog box. (Anyone else using the shared workbook can also do this; no one “owns” the right to enable or disable sharing.) Doing this has several effects. First, the change history is lost. If you subsequently start a new sharing session, the history starts fresh. Second, any other users who still have the shared workbook open won’t be able to save their changes to the same file. They’ll be in read-only mode, but Excel won’t inform them of that until they attempt to save, at which time the Save As dialog box appears. Even if you turn sharing off and then turn it back on while another person still has the file open, the person won’t be able to share the file until he closes or reopens it.
You can click the Remove User button on the Editing tab in the Share Workbook dialog box if you want to disconnect someone from the sharing session manually. Doing this maintains the change history for the master workbook. You’ll probably want to warn the person you’re disconnecting, of course.