Leading the way in Microsoft Office Development

What is Shared Workbook ?

It is possible to allow two or more people to work on the same workbook at the same time.

If you are going to share a workbook amoing several users it is worth spending a bit of time laying out the workbook in a sensible format.

Try and give all the users a specific worksheet and then have a separate worksheet which pulls together all the relevant data into the necessary format.

Conclicts can arise when different users make changes to the same cells.

When someone saves their changes, Excel will also update the workbook with changes that have been saved by other users.

A dialog box is displayed to let you know that these changes have been incorporated.

Any changes that have been saved by other users are outlined with a cell border with a corresponding comment.

Sharing a Workbook

To allow any number of users on a network to open and save a workbook simultaneously you can share the workbook (Tools > Share Workbook).

This will display the "Share Workbook" dialog box.

The name that appears when you (or anyone else) uses shared files is taken from your (Tools > Options)(General tab, "User Name").

SS - change with Russell Proctor

Is there a confirmation prompt ?

Select the Editing tab and tick the box labelled "Allow changes by more than one user at the same time".

Press OK

This will save the workbook in a "sharable" format.

Everytime you open a workbook which can be shared between users the text [Shared] is appended to the file name in the title bar.

The text [shared] will always appear until the sharing is removed.

Resolving any Conflicts

When you save a shared file Excel will check for any conflicting changes.

If you have made a change to a cell that has been changed by another user the Resolve Conflict dialog box will be displayed automatically.

SS

This dialog box specifies the cells involved and allows you to decide which change to keep.

Conflicts can only exist between the last saved version and the version you are trying to save.

Advanced Options

Keep change history for X days - The number of days you want to keep the changes for. Any history older than this is permanently deleted.

Don’t keep change history - You can still track the changes on the workbook by selecting to highlight the changes on screen.

When file is saved -

Automatically every X minutes - Allows you to specify how often updates will occur automatically.

Save my changes and see other's changes -

Just see other users' changes - Allows you to see any changes saved by other users while holding your changes back until you save the workbook.

Ask me which changes win -

The changes being saved win - The last changes which are saved

Print Settings - This allows each user to change and store their own print settings for this workbook.

Filter Settings - This allows each user to change and store their own filter options for this workbook.

If you anticipate more than one person will use a document at one time, you can allow for multi-user editing and see who else is currently working on a file you have opened.

The window will display a list of a list of who else is currently working on the file.

Tracking Changes

When you share a workbook the track changes will be switch on by default

To find out more about Tracking changes please refer to the Tracking Changing page.

You can track changes that are made in a Workbook by sharing it ??

You can track and audit revisions in a shared workbook (Tools > Track Changes) click Highlight Changes.

When a workbook is set up to be shared. Excel maintains a trail of changes to data so you can resolve conflicts or revert back to previously existing values.

This trail is only maintained for 30 days by default. It can be changed by Tool > Share Workbook and click the Advanced tab and change the number in the “Keep Change History For “

Cancel Shared Workbook

You can cancel a shared workbook at any time by clearing the "Allow Changes by More than one user" option on the Editing tab of the Share Workbook dialog box

Anyone using the shared workbook can do this unless the workbook has been password protected.

No individual owns the rights to enable or disable sharing.

Several things happen when you remove a workbook from shared mode.

1) The change history is lost.

2) Any users who currently have the file open will not be able to save their changes to the same file. They will have to use a different file name.

3) You will not be able to place the workbook back into shared mode until all the copies have been closed.

The Remove User button will disconnect someone from the sharing session.

This will maintain the change history for the master workbook.

Things to Remember

Removing a workbook from shared mode will cut off anyone who has the workbook currently open and the change history will be lost.