Excel Share Workbook

This Excel tutorial explains how to use Share Workbook function to allow multiple users to open the workbook at the same time.

Excel Share Workbook

Share Workbook function allows multiple users to open the workbook at the same time.

Assume that we have a staff list workbook that is placed in the network share drive.

excel share workbook 01

 

Click on REVIEW > Share Workbook

excel share workbook 02

 

Check the box “Allow changes by more than one user at the same time. This also allows workbook merging” > OK

excel share workbook 03

 

Now you can see the workbook name has [Shared] in the suffix

excel share workbook 04

Modify Share Workbook by others

Lets say the computer we just used to share workbook is called Workstation1, now we are going to open the workbook from another network computer called Workstation2.

Delete row 2 (Mary), modify Peter’s salary, add one more row of data for Joe in the last row. Save the workbook.

excel share workbook 05

 

Go back to workstation1, save the workbook, now you can see the workbook is updated with values changed by workstation2. Changes are also highlighted in blue color with comments inserted.

excel share workbook 06

Review changes of Share Workbook made by others

Hover the mouse over the comments to see the old value and new value change.

excel share workbook 07

 

Click on REVIEW > Track Changes > Accept/Reject Changes

excel share workbook 10

Click on Accept to accept the change the those highlighted changes will be gone.
Click on Reject to change back to the original value.

If you cannot see the highlighted changes or you want to see the historical changes, click on REVIEW > Track Changes > Highlight Changes

excel share workbook 11

 

Now you can see the previous changes again

excel share workbook 07

Other options of Share Workbook

Click on REVIEW > Shareworkbook

You can see who are currently opening the workbook. — USER is workstation2 in our case.
You can click on Remove User to kick the user out of the share workbook. Once the user being kicked save the workbook, a notification will pop up telling them no changes will be made to the the share workbook and suggest them to save the workbook as another file.

excel share workbook 08

 

Click on the Advanced tab to see how you would like to keep the change history, and also how to deal with conflicting changes when users edit on the same cells.

excel share workbook 09

Conflicting Changes for option “Ask me which changes win”

excel share workbook 05

Lets say Workstation1 changes B4 value to 100000, then save the workbook. Then Workstation2 changes the value to 200000, save the workbook, a Windows will pop up in Workstation2 asking if you want to kept your change or accept other’s change.

excel share workbook 12

Stop Share Workbook

Click on REVIEW > Shareworkbook > uncheck the box > OK

excel share workbook 03

Disconnected from Share Workbook

No matter whether you are disconnected due to network issue or being kicked out, in that case you need to know what changes you have made but not updated in the Share Workbook.

Read my another post to compare workbook in order to find out the updates you need to move to the Share Workbook.

Excel VBA compare worksheets

Excel Compare Worksheets using Compare File

Features not supported in Share Workbook

I refer to the Microsoft documentation in the below section.

Not all features are supported in a shared workbook. If you want to include any of the following features, you should add them before you save the workbook as a shared workbook: merged cells, conditional formats, data validation, charts, pictures, objects including drawing objects, hyperlinks, scenarios, outlines, subtotals, data tables, PivotTable reports, workbook and worksheet protection, and macros. You cannot make changes to these features after you share the workbook.

Features that are not supported in a shared workbook

In a shared workbook, you cannot But you may be able to do the following
Create an Excel table None
Insert or delete blocks of cells You can insert entire rows and columns.
Delete worksheets None
Merge cells or split merged cells None
Add or change conditional formats Existing conditional formats continue to appear as cell values change, but you can’t change these formats or redefine the conditions.
Add or change data validation Cells continue to be validated when you type new values, but you can’t change existing data validation settings.
Create or change charts or PivotChart reports You can view existing charts and reports.
Insert or change pictures or other objects You can view existing pictures and objects.
Insert or change hyperlinks Existing hyperlinks continue to work.
Use drawing tools You can view existing drawings and graphics.
Assign, change, or remove passwords Existing passwords remain in effect.
Protect or unprotect worksheets or the workbook Existing protection remains in effect.
Create, change, or view scenarios None
Group or outline data You can continue to use existing outlines.
Insert automatic subtotals You can view existing subtotals.
Create data tables You can view existing data tables.
Create or change PivotTable reports You can view existing reports.
Write, record, change, view, or assign macros You can run existing macros that don’t access unavailable features. You can record shared workbook operations into a macro stored in another nonshared workbook.
Add or change Microsoft Excel 4 dialog sheets None
Change or delete array formulas Existing array formulas continue to calculate correctly.
Use a data form to add new data You can use a data form to find a record.
Work with XML data, including:

  • Import, refresh, and export XML data
  • Add, rename, or delete XML maps
  • Map cells to XML elements
  • Use the XML Source task pane, XML toolbar, or XML commands on the Data menu
None

Wyman W
Wyman is a Human Resources professional based in Hong Kong, specialized in business analysis, project management, data transformation with Access and Excel.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist in Access / Excel
- Microsoft Specialist in MS Project
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

Leave a Reply

Your email address will not be published. Required fields are marked *