We can share a workbook in many easy ways. In this tutorial, we will learn how multiple users update an excel spreadsheet at the same time. We will explore the many ways to share and unshare excel files automatically. We will also explore the many ways to control user access and resolve changes among multiple users.
Figure 1 – How to Share Excel Workbook for Multiple Users and Unshare Files
How to share a file
- We will go to the Reviews tab and click on Share Workbook in the Changes group. If we are using Excel 2016, we will just select Share from the menu.
Figure 2 – How to share a workbook
- Next, in the share workbook dialog box, we will navigate to the Editing tab and click on Allow changes for more than one user at a time. This also allows workbook merging.
Figure 3 – Share workbook dialog box
- We can also go to the Advanced tab to select settings for tracking changes. Lastly, we will click OK.
Figure 4 – Share workbook dialog box
- Now we will save the file. If we handled it correctly, we will find the word “Shared” at the top of our Sheet
Figure 5 – Shared document
How to Share Workbook and protect changes tracking
If we do not wish for anyone to turn off change history or remove workbook from the shared mode, we can take these additional steps:
- In the review tab, we will click on Protect and Share Workbook in the Changes group
Figure 6 – Click on Review tab
- In the Protect Share Workbook dialog, we will tick sharing with track changes
Figure 7 – Protect Shared Workbook dialog box
- We can enter a password in the Password (Optional) box, retype the password and click OK.
Warning: We may turn this feature on without a password, but the passwords ensure that no one without a password can remove the workbook sharing mode
The Protect and Share Workbook option only prevents anyone from changing the share mode. However, it does not prevent editing, modifying or deleting of contents. To do that, we must use worksheet protection.
How to resolve conflicting changes in a shared file
When two or more users are editing the same workbook at the same time, some entries may conflict. By default, Excel keeps the changes in the person who saves the workbook first. When another user wants to save differently, Excel will display the figure below
Figure 8 – Resolve conflicts dialog box
To resolve conflicts, we may take one of the following steps:
- If we want to keep all of our changes, we will click Accept All Mine
- If we want to keep a change, we will click Accept Mine
- If we want to keep the other user’s changes, we will click Accept Other
- When we wish to keep all of the other user’s changes, we will click Accept All others
Note: We may save a different version under a different name, so we can merge changes later.
Overriding previous changes automatically
When we wish to override the changes made by other users without seeing the Resolve changes dialog box, we may:
- Click Share Workbook found in the Changes group within the Review tab
- Next, we will switch to the Advanced tab and select The changes being saved win under conflicting changes between users and select OK.
Figure 9 – Share workbook dialog box
How to unshare an Excel file
- We will click on the Reviews Tab, Changes group and select Share Workbook
- Under the Editing tap, we will unmark Allow changes by more than one user at the same time. Next, we will select OK.
Shared Workbook Limitations
When we share files, users may not have all the features in their versions of the Excel file including:
- Sorting and filtering by format
- Subtotals
- Slicers and Sparklines
- Array formula
- Macros
- Charts and Pictures
- Data Validation
- Merging Cell
- Conditional formatting
- Grouping or outlining data etc.
After unsharing, we will be able to carry out these entire Excel functions.
Note
Excel may not share some workbook if it has any of these reasons alongside:
- We cannot share workbooks containing XML maps or tables. Before sharing, we must convert our table to ranges and remove XML maps
- If we have enabled privacy settings, we will have to disable it before sharing. To do this:
1. We will go to File
Figure 10 – Click on File
2. Next, we will click Options, and then Trust Center
Figure 11 – Options dialog box
3. In the Trust Center dialog box, we will select the Trust Center Settings
4. Under the Privacy Option, we will unmark Remove personal information from files properties on save. We will click OK
Figure 12 – Trust center Dialog box
Instant Connection to an Excel Expert
Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.
Leave a Comment