Collaborate on Spreadsheets: How to Share Excel Files with Others on Your Network

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. [ILLUSTRATION OMITTED] 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). [ILLUSTRATION OMITTED] 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. [ILLUSTRATION OMITTED] 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. …