Shared Workbooks

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

microsoft excel docs

This will display the "Share Workbook" dialog box.

microsoft excel docs

It is possible to share a workbook without enablying track changes.
The name that appears when you (or anyone else) uses shared files is taken from your (Tools > Options)(General tab, "User Name").
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.

microsoft excel docs

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

Restricting Access

Add a password to modify the file
Then add the share workbook
This will prompt you to enter the password when you open the shared workbook depending on whether you want to make changes or not.

Advanced Options

You can change some aspects of the default behaviour by using the Advanced tab.
Each shared workbook user can set these options individually.
The first section specifies whether to keep track of the changes and the length of time you want to keep the changes for.

microsoft excel docs

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.

This section controls when you will receive any updated changes.

microsoft excel docs

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.

microsoft excel docs

Ask me which changes win -
The changes being saved win - The last changes which are saved. All conflicts are resolved in favour of the last user to issue the save command.

The last two options allow you to save any personal print settings and/or any views set using the AutoFilter or Advanced Filter.
Each user that has the workbook open can have different print and filter settings which are saved the next time they open the workbook.
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

microsoft excel docs

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.


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

© 2024 Better Solutions Limited. All Rights Reserved. © 2024 Better Solutions Limited TopPrevNext