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.

[ILLUSTRATION OMITTED]

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

[ILLUSTRATION OMITTED]

That will evoke the Highlight Changes dialog box (see exhibit 6, page 63).

[ILLUSTRATION OMITTED]

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.

LIMITING USERS

Not only can Share Workbook determine who sees and edits a spreadsheet, it also can selectively hide specific worksheets. Protection for a shared workbook is applied in layers--and only by the person initially creating the worksheet or someone who has the person's password: First you protect the worksheet, then the workbook, and last you designate the capability for sharing and change tracking. All these actions must be taken before users open the workbook for sharing.

To engage protection click on File, Save As, producing the Save As dialog box (see exhibit 7, page 64).

[ILLUSTRATION OMITTED]

Now click on Tools, General Options, opening the Save Options dialog box (exhibit 8, page 64) where you can enter a password.

[ILLUSTRATION OMITTED]

Then click On OK, evoking a Confirm Password dialog box, which asks you to reenter your password and click on OK. If you select two passwords, you must confirm twice. Finally, click on Save in the Save As dialog box. The same password can be used to open and modify a spreadsheet.

If you don't want users to see specific worksheets in the shared workbook, click on the worksheet you want to hide and then on Format, Sheet and Hide. Repeat these steps for each worksheet you want to hide.

If you check Always create backup, Excel will automatically create a backup of the spreadsheet exactly as it appeared when opened, naming it "Backup of XXX." This backup is saved when you save the spreadsheet with new changes incorporated.

If you check the Read-only recommended box, Excel will not automatically override the password to modify the worksheet. When a user enters the correct password to modify, a second user form comes up asking how the user wants to open the spreadsheet, as read-only or modify. If no password is used but the read-only box is checked, the user can open up the spreadsheet, view it and make changes but is not allowed to save it under the original name.

After you hide worksheets, you must protect the workbook to keep other users from redisplaying the hidden worksheets. To do that, click on Tools, Protection and Protect Workbook, bringing up the Protect Workbook dialog box (see exhibit 9, at right). Check the Structure box, enter your password and click on OK. When a Confirm Password dialog box appears, reenter your password.

[ILLUSTRATION OMITTED]

The simplest way to restrict participants' ability to edit worksheets is to limit them to their own worksheets--that is, have a separate worksheet for each person or group. To allow only authorized users to change a specific worksheet, you first need to go back and unprotect the workbook. Then click on Tools, Protection and Allow Users to Edit Ranges, evoking the dialog box by that name (see exhibit 10, page 65).

[ILLUSTRATION OMITTED]

Now click on New, bringing up the New Range dialog box (see exhibit 11, page 65).

[ILLUSTRATION OMITTED]

In the Refers to cells box, type a range of cells the user will be allowed to change. Then enter the password in Range password and click on OK, and you'll be asked to confirm the password, after which you'll be taken back to the Allow Users to Edit Range (exhibit 10). Click on Protect Sheet, evoking the Protect Sheet box (see exhibit 12, page 65).

[ILLUSTRATION OMITTED]

Enter your password, click on OK and you'll be asked to reenter your password. When the authorized users want to change the information in the allowable cells, they simply double-click on those cells, bringing up the Unlock Range box (see exhibit 13, at right), and enter the password they were given.

[ILLUSTRATION OMITTED]

Because shared history is lost if a workbook is unshared, you may want to restrict the ability to unshare the workbook. To do this, click on Tools, Protection, Protect and Share Workbook, bringing up the Protect Shared Workbook dialog box (see exhibit 14, at right). Check Sharing with track changes and enter your password.

[ILLUSTRATION OMITTED]

As you can see, Share Workbook is a very powerful and useful program. However, don't be put off by its seeming complexity. Yes, there are a lot of steps to setting up the workbook, but after you go through the exercise once, you'll immediately see that they are clearly defined and quite intuitive.

In today's multiple-enterprise environments, a fast and efficient way to collaborate is a must, and the Share Workbook feature is a perfect tool for that.

PAUL GOLDWATER, PhD, CMA, is an associate professor at the University of Central Florida's School of Accounting, Orlando. His e-mail address is [e-mail]. LOIS S. MAHONEY, CPA, PhD, CMA, is an assistant professor at the university. Her e-mail address is [e-mail].