Show All

Hide All

Performing accounting functions, such as financial reporting, budgeting, and analysis, usually requires a great deal of collaboration. For example, people from different departments often need to submit budget requests for the coming year. How do you consolidate all that information?

If you have everyone fill out a separate worksheet and submit it to you, the results have to be compiled before you can use them. This process is time-consuming, and it can also introduce errors.

As an alternative, you can have people enter the information directly into your workbook. But if you pass an unprotected workbook around the group, you might find some surprises in your formulas and other items by the time the workbook gets back to you — assuming you even get it back in time to meet your deadlines.

How can you let multiple users enter information into the same workbook at the same time, protect formulas from unintended or unauthorized changes, and keep track of the changes made by each person? Easy. Use a shared (shared workbook: A workbook set up to allow multiple users on a network to view and make changes at the same time. Each user who saves the workbook sees the changes made by other users.) workbook.

Shared workbooks

In Microsoft Office Excel 2003, you can set up a workbook to be shared so that multiple users on a network can work on it simultaneously. You decide who can access the workbook, what level of access each user has, and what they can see and change.

Create a workbook

First, create the workbook that you want to share. Set up tables, create formulas, and do as much as possible before you share the workbook. For example, this budget request workbook is set up and is ready for team input.

Some features, such as merged cells and PivotTable® reports, can't be changed when a workbook is shared, so make sure to create them before you share the workbook. If you need to use these features after you share the workbook, you can save and unshare the workbook, and then work as usual.

Protect your workbook from unintended or unauthorized changes

You have several options for setting limits on how users can view or change your workbook. You can restrict access at the workbook level or the worksheet level, you can choose which cells users can change, and you can even let certain users make changes and other users only view the workbook.

Protect at the workbook level

Protect at the worksheet level

Share your workbook with other users

Next, save your workbook to a network location that the other users have access to.

1. On the Tools menu, click Share Workbook, and then click the Editing tab.
2. Select the Allow changes by more than one user at the same time check box.
3. Click the Advanced tab.
4. Under Track changes, click Keep change history for, and in the Days box, type the number of days of change history that you want to keep.

    Important   Excel permanently erases any change history older than the value you set here, so make sure to specify the longest change history period you will ever need.

5. Click OK, and if prompted to save the file, click OK.
6. On the File menu, click Save As, and then save the workbook on a network location accessible to the intended users. Use a shared network folder, not a Web server.

Work in a shared workbook

After you open a shared workbook, you can enter and change data as you would in a regular workbook. When you save the shared workbook, you're updated with any changes that others have saved since the last time you saved the workbook. If you want to keep the shared workbook open to monitor progress, you can set Excel to notify you with changes automatically, at timed intervals that you specify, with or without saving the workbook yourself.

1. On the Tools menu, click Share Workbook.
2. Click the Advanced tab, and under Update changes, select the options you want.

Resolve conflicts

When two or more people are working simultaneously in the same workbook, sooner or later they're going to change the same cell. When this happens, the Resolve Conflicts dialog box opens so that you can decide which changes to keep. In this example, both you and Nancy changed cell C3.

1. Read the information about each change and the conflicting changes made by others.
2. To save the change you want and go to the next conflicting change, click Accept Mine or Accept Other.
3. To keep all of your remaining changes or all of the other users' changes, click Accept All Mine or Accept All Others.
4. To have your changes override all other changes and never see the Resolve Conflicts dialog box again, turn this feature off.

   How?

   1. On the Tools menu, click Share Workbook, and then click the Advanced tab.
   2. Select the The changes being saved win check box.
   3. Click OK.
5. To see how you or others resolved past conflicts, view this information on the History worksheet.

   How?

   1. On the Tools menu, point to Track Changes, and then click Highlight Changes.
   2. In the When box, click All.
   3. Clear the Who and Where check boxes.
   4. Select the List changes on a new sheet check box, and then click OK.
   5. On the History worksheet, scroll to the right to view the Action Type and Losing Action columns.

      Conflicting changes that were kept show an Action Type of Won. The Losing Action column identifies the rows with information about the conflicting changes that weren't kept, including any deleted data.

 Note   To save a copy of the workbook with all your changes, click Cancel in the Resolve Conflicts dialog box, click Save As on the File menu, and then type a new name for the file.

For more information, click "About resolving conflicting changes in shared workbooks" in the See Also section of this article.

Keep personal views and settings

Excel saves a custom view of the shared workbook for you that includes information such as which worksheet you have displayed and your zoom settings. By default, your view includes any filter and print settings you make, or you can specify that you want to use the original filter and print settings. Each time you open the shared workbook, Excel displays it with your view in effect.

Sharing workbooks makes it easier to collaborate on accounting functions

As this example shows, you can have multiple users enter information in the same workbook at the same time — without giving up control of your formulas or workbook structure. You won't have to worry about users unintentionally or intentionally wreaking havoc on your workbook. And the people from whom you need input can add it when their schedules allow — not when a file is passed to them.