Using Excel in

a Workgroup

Most people who use a computer in an office connect to others via a network. In fact, networks are also common in homes. By enabling users to easily share data (and peripheral devices), networks make

it much easier for you to work together with several people on projects. Excel has a number of features that facilitate this type of cooperation, and those features are the subject of this chapter.

Note

If you’re working on a corporate network, you may need to consult with your network administrator before using any of the features described in this chapter. n

Using Excel on a Network

A computer network consists of two or more PCs that are linked electronically. You can perform these tasks on a network:

•Access files on other systems.

•Share files with other users.

•Share resources, such as printers, scanners, and fax modems.

•Communicate with each other electronically.

Excel has tools that enable you to work cooperatively with other Excel users on a project.

CHAPTER

IN THIS CHAPTER

Using Excel on a network

Understanding file reservations

Using shared workbooks

Tracking changes in a workbook

627

Part IV: Using Advanced Excel Features

Understanding File Reservations

Networks provide you with the ability to share information stored on other computer systems. Sharing files on a network has two major advantages:

•It eliminates the need to have multiple copies of a file stored locally on user PCs.

•It ensures that the file is always up to date. For example, a group of users can work on a single document, as opposed to everyone working on his or her own document and then merging them.

Note

Some networks — generally known as client-server networks — designate specific computers as file servers. On these types of networks, the shared data files are typically stored on the file server. Excel doesn’t care whether you’re working on a client-server or a peer-to-peer network (where all the PCs have essentially equal functions). n

Some software applications are multiuser applications. Most database software applications, for example, enable multiple users to work simultaneously on the same database files. One user may be updating customer records in the database, while another is extracting information for a report. But what if two users attempt to change a particular customer record at the same time? Multiuser database software contains record-locking safeguards that ensure that only one user at a time can modify a particular record.

Excel, however, is not a multiuser application. When you open an Excel file, the entire file is loaded into memory. If the file is accessible to other users, you wouldn’t want someone else to change the stored copy of a file that you’ve opened. If Excel allowed you to open and change a file that someone else on a network had already opened, the following scenario could happen.

Assume that your company keeps its sales information in an Excel file that is stored on a network server. Esther wants to add this week’s data to the file, so she loads it from the server and begins adding new information. A few minutes later, Jim loads the file to correct some errors that he noticed last week. Esther finishes her work and saves the file. Later, Jim finishes his corrections and saves the file. Jim’s file overwrites the copy that Esther saved, and her additions are gone.

This scenario can’t happen because Excel uses a concept known as file reservation. When Esther opens the sales workbook, she has the reservation for the file. When Jim tries to open the file, Excel informs him that Esther is using the file. If he insists on opening it, Excel opens the file as read-only. In other words, Jim can open the file, but he can’t save it with the same name.

Figure 30.1 shows the message that appears if you try to open a file that is in use by someone else.

628

Chapter 30: Using Excel in a Workgroup

FIGURE 30.1

The File in Use dialog box appears if you try to open a file that someone else is using.

The File in Use dialog box has three choices:

•Click Cancel, wait a while, and try again. You may call the person who has the file reservation and ask when the file will be available.

•Click Read Only. Open the file to read it, but you cannot save changes to the same filename.

•Click Notify. This opens the file as read-only. Excel later pops up a message that notifies you when the person who has the file reservation is finished using the file.

Figure 30.2 shows the message that appears when the file is available. If you open the file as ReadWrite, you receive another message if you makes any changes to this read-only version. You will have an opportunity to discard your changes or to save his file with a new name.

FIGURE 30.2

The File Now Available dialog box pops up with a new message when the file is available for editing.

Sharing Workbooks

Although Excel isn’t a true multiuser application, it does support a feature known as shared workbooks, which enables multiple users to work on the same workbook simultaneously. Excel keeps track of the changes and provides appropriate prompts to handle conflicts.

Caution

Although the ability to share workbooks sounds great in theory, it can be confusing if more than a few users are sharing a single workbook. Also, be warned that this feature has been known to cause problems, and it’s certainly not 100-percent reliable. Therefore, use caution and make frequent backup copies of your workbooks. n

629

Part IV: Using Advanced Excel Features

Understanding shared workbooks

You can share any Excel workbook with any number of users. Here are a few examples of workbooks that work well as shared workbooks:

•Project tracking: You may have a workbook that contains status information for projects. If multiple people are involved in the project, they can make changes and updates to the parts that are relevant to them.

•Customer lists: With a customer list, records are often added, deleted, and modified by multiple users.

•Consolidations: You may create a budget workbook in which each department manager is responsible for his or her department’s budget. Usually, each department’s budget appears on a separate worksheet, with one sheet serving as the consolidation sheet.

If you plan to designate a workbook as shared, be aware that Excel imposes quite a few restrictions on the workbook. For example, a shared workbook cannot contain tables (created with Insert Tables Table).

In addition, you can’t perform any of the following actions while sharing the workbook. You can tell that these actions are not allowed because the relevant commands are disabled on the Ribbon.

•Delete worksheets or chart sheets.

•Insert or delete a blocks of cells. However, you can insert or delete entire rows and columns.

•Merge cells.

•Define or apply conditional formats.

•Change or delete array formulas.

•Set up or change data validation restrictions and messages.

•Insert or change charts, pictures, drawings, objects, or hyperlinks.

•Assign or modify a password to protect individual worksheets or the entire workbook.

•Create or modify pivot tables, scenarios, outlines, or data tables.

•Insert automatic subtotals.

•Write, change, view, record, or assign macros. However, you can record a macro while a shared workbook is active as long as you store the macro in another unshared workbook (such as your Personal Macro Workbook).

Tip

You may want to choose Review Protect Sheet to further control what users can do while working in a shared workbook. n

630

Chapter 30: Using Excel in a Workgroup

Caution

If you save an Excel 2010 shared workbook to an earlier version file format (such as *.xls), sharing is turned off, and the revision history (if any) is lost. n

Designating a workbook as a shared workbook

To designate a workbook as a shared workbook, choose Review Changes Share Workbook. Excel displays the Share Workbook dialog box, shown in Figure 30.3. This dialog box has two tabs: Editing and Advanced. On the Editing tab, select the Allow Changes check box to allow changes by multiple users and then click OK. Excel then prompts you to save the workbook.

When you open a shared workbook, the workbook window’s title bar displays [Shared]. If you no longer want other users to be able to use the workbook, remove the check mark from the Editing tab of the Share Workbook dialog box and save the workbook.

Tip

Whenever you’re working with a shared workbook, you can find out whether any other users are working on the workbook. Choose Review Changes Share Workbook, and the Editing tab of the Share Workbook dialog box lists the names of the other users who have the file open, as well as the time that each user opened the workbook. n

FIGURE 30.3

Use the Share Workbook dialog box to control the sharing of your workbooks.

631

Part IV: Using Advanced Excel Features

Sharing a Workbook with Yourself

If you plan to use shared workbooks, spend time experimenting with the various settings to ensure that you understand how sharing works. You don’t need to enlist a colleague to help you — you can share a workbook with yourself. Just launch a second instance of Excel and then open a shared workbook in both instances. Make changes, save the file, adjust the settings, and so on. Before long, you’ll have a good understanding of Excel’s shared workbooks.

Controlling the advanced sharing settings

Excel enables you to set options for shared workbooks. Choose Review Changes Share Workbook and click the Advanced tab of the Share Workbook dialog box to access these options (see Figure 30.4).

FIGURE 30.4

Use the Advanced tab of the Share Workbook dialog box to set the advanced sharing options for your workbook.

Tracking changes

Excel can keep track of the workbook’s changes: its change history. When you designate a workbook as a shared workbook, Excel automatically turns on the Change History option, enabling you to view information about previous (and perhaps conflicting) changes to the workbook. You can turn off change history by selecting the Don’t Keep Change History button. You can also specify the number of days for which Excel tracks change history.

632