Sharing Workbooks on a Network
It has always been possible to share Excel files on a network. You just had to make 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 2007, 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 24-3.
Figure 24-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 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 Microsoft Office Button, and then click the Excel Options button to open the Excel Options dialog box. In the Popular category, edit the User Name box. |
Of course, there are inherent risks when people work at the same time in the same file. 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 24-4. Note that the triangular comment indicator appears in the upper-left corner of the cell instead of in the upper-right corner, as it does for standard cell comments.
Figure 24-4: Excel outlines cells changed by others in a shared workbook and attaches a comment.
| 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 that appears, select the Track Changes While Editing check box. Make sure this check box is selected before you save the worksheet for sharing if you want to be able to track and review changes later. For more information, see "Tracking Changes" on page 794. |
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 24-5.
Figure 24-5: If more than one person changes the same cells, the last person to save changes might get to decide which ones to keep.
 |
You can edit shared workbooks using Excel 97 and newer versions only. Older versions, such as Microsoft Excel 7 for Windows 95, don't support shared editing.
When you open a workbook for sharing, you can type text and numbers; change cell formatting; edit formulas; and copy, paste, and move data by dragging. You can insert columns and rows, but you can't insert blocks of cells. You can't merge cells, insert charts or other objects, create hyperlinks, assign passwords, insert automatic subtotals, create outlines, or create data tables or PivotTables. You can't do anything with macros except run them, although you can record macros if you store them in a separate, nonshared workbook. The Conditional Formatting, Scenarios, and Data Validation commands are not available for a workbook in shared mode (although you can still see their effects); most of the buttons on the Drawing toolbar aren't available either.
|
| 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 258. |
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.
For more information about accepting or rejecting changes individually, see "Reviewing Changes" on page 797.
Using Advanced Sharing Options
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 (see Figure 24-6). (The options on this tab are unavailable 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 select. If you need to track changes but are unsure how long you want to track them, set a high number (such as 999 days).
Figure 24-6: Use the Advanced tab to determine the way Excel handles changes.
Turning off change tracking detracts from your ability to merge workbooks. For more information, see "Combining Changes Made to Multiple Workbooks" on page 798.
In the Update Changes area, select when you want updates to occur. Ordinarily when a user saves a file, Excel saves the changes, and it 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 it 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 24-5 appears. 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, however, 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 using 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.
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 Microsoft Office Button, 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 59.
|
Tracking Changes
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. In the Highlight Changes dialog box, select the Track Changes While Editing check box. Selecting this check box puts your workbook into shared mode and, as the option indicates, saves your workbook (see Figure 24-7)-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, as shown in Figure 24-6, you can still turn on change tracking by using the Track Changes commands.
Figure 24-7: Use the Highlight Changes command to show what has been done in a shared workbook.
Inside Out-Track Changes Without Sharing
|
You don't have to share a workbook to be able 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 highlighted. 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 attached cell comments. 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 24-8.
Figure 24-8: You can choose to create a history worksheet detailing the changes made to a shared workbook.
| Note | Formatting changes aren't recorded in the change history. |
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.
| Note | To keep track of the change history after discontinuing the sharing session, copy the contents of the locked history worksheet and paste them into another worksheet, or copy the worksheet. |
Protecting the Change History
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 Protect Shared Workbook dialog box, shown in Figure 24-9, appears.
Figure 24-9: You can ensure that change tracking is protected in a shared workbook.
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 the 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. |
Reviewing Changes
You can decide at any time to go through each change that users have made to the shared workbook, provided 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 24-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 24-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 24-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 the changes. After you have accepted or rejected all the changes, you cannot review them again. You can, however, still display the history worksheet.
Figure 24-11: Each change is highlighted and described, and you can accept or reject it.
Canceling the Shared Workbook Session
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 will appear. 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 they close or reopen 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.
EAN: 2147483647
Pages: 260