|
Page 1 of 2
Collaborate on spreadsheets: how to share Excel files with others on your network Article Courtesy of Paul Goldwater.
Would you like to be able to gather spreadsheet data from people throughout your organization, automatically producing detailed reports or summaries that then can be shared with colleagues? Or would you like a team of users to be able to work on a spreadsheet report simultaneously, sharing information and coordinating the results?
If so, Microsoft's Excel has just the thing for you--a built-in function called Share Workbook. It makes no difference whether the team members are in offices down the hall or on other continents. The only requirement: They must be on a shared network. Follow along and we'll show you how to create a workbook that as many as 256 users can share at the same time.
Begin by clicking on Tools, Share Workbook, evoking the Share Workbook dialog box, as shown in exhibit 1, at right.
Click on the Editing tab and then place a check next to Allow changes by more than one user at the same time. If others are using the file, their names and their log-on times will appear under Who has this workbook open now. Note that as the originator of the Share Workbook function, you have the power to remove a user by clicking on the name and then on the Remove User button. Next click on the Advanced tab (see exhibit 2, page 62).
This box offers several options: The first, under Track changes, lets you keep a history of all changes made to the workbook for up to 10,000 days. Be sure to enter a large enough number to cover the period you want because Excel permanently erases the change history for any days beyond that deadline, including discarded changes.
The second option lets you fix a common frequency for automatically seeing and updating changes other users made--from as short as 5 minutes to as long as 1,440 minutes. If you choose to automatically see other users' changes, then you have two more options: Save my changes and see others' changes (in which case Excel will save your changes and will incorporate those that other users made) or Just see other users' changes (Excel will save others' changes in your workbook but not your changes). Excel won't incorporate your changes in the shared workbook until you manually save (Ctrl+S) the workbook.
However, be aware that users are allowed to create their own settings for the second option, so we suggest your group agrees on a common policy. If you let different users change the same cells in your workbook, you are bound to run into conflict.
The third option, under Conflicting changes between users, allows you to choose between Ask me which changes win or The changes being saved win. In order to avoid error, we suggest you choose Ask me which changes win. With this option, when two or more users update the same cells and then save the workbook, the Resolve Conflicts dialog box will appear (see exhibit 3, below). Each user can then choose to accept his or her changes or the other user's changes by clicking on the appropriate button.
The fourth option allows participants to set their own personal view of the worksheet. Each person using the shared workbook is allowed to set his or her own1 printer or filter settings. Thus, each person can choose to have their information printed in landscape and with grid lines without affecting the printing options of any other user. Similarly, each user can set their own filter settings (a quick and easy way to find a subset of data that an individual person or department may want to work with), and this setting also will not affect what any other user will see.
When you've finished your selections, click on OK, and the Save As dialog box appears to let you know you need to save the workbook on a network drive where others can gain access to it. Once you save it, the bracketed word [Shared] appears in the file's title bar (see exhibit 4, below) whenever anyone opens the workbook.
If you decide you no longer want the workbook to be shared, uncheck Allow changes by more than one user at the same time on the Editing tab of the Share Workbook dialog box (exhibit 1). If anyone happens to be using the shared workbook when you deselect this option, they automatically will lose their changes. In addition, by turning off the shared workbook, Excel automatically erases the history.
TRACK CHANGES
Revisions by any participant can be marked and tracked. You also can mark changes from a certain time and in a certain area of a worksheet. Changes are color coded by user, and when you rest the pointer over a colored cell, the cell displays the name of the author, the time of the change and the original and changed value of the cell. To enable tracking of changes, click on Tools, Track Changes and Highlight Changes (see exhibit 5, page 63).
That will evoke the Highlight Changes dialog box (see exhibit 6, page 63).
To set the filters for the changes you want to track, first clear the checks next to When, Who and Where. If you had selected a time period in exhibit 2 for saving history, you now will be able to see that history. Now click on OK.
As participants make future changes to the workbook, a small colored triangle will appear in the corner of the changed cell (see B11 in exhibit 5). When a user moves the cursor over this cell, a small flag will indicate the change, who made it and when.
Important: For Track Changes to be effective, you must save your workbook with these settings on your network drive before you allow other users access.
|
