In the office, you’d be usually connected to the Local Access Network (LAN), working on shared folders or server copies of a workbook. What if you and your colleagues need to work on the same workbook at the same time? MS Excel allows you to share your workbook to give limited access to multiple users to update the file.
Just follow these simple steps:
- Load the workbook you want to share.
- Choose Share Workbook from the Tools menu. Excel displays the Share Workbook dialog box. (Click here to see a related figure.)
- Choose the Allow Changes check box.
- Click on OK.
To me, this feature is both a boon and a bane.
Here, many users can update the workbook in a short span of time without needing to wait and take turns, boosting the productivity of the team greatly.
However, the temptation may arise to bring this productivity boost one step further and try to build a model through the collaborative capability of a shared workbook.
This is possible if the model is simple and easy to follow. But when the model becomes too complex and bloated, you may run into resource issue:
“Excel cannot complete this task with available resources” error message when you try to view the tracked changes in Excel 2002.
If you are able to download and install the hotfix, good for you. But, if like me, you are in an IT Dept controlled PC where you cannot install anything without permission, then, good luck to you.
The Shared Workbook features is for multiple users to easily update information (template filing), it is not optimised for heavy duty editing, but you can still work around if you are aware of the limitations.
Some preventive measures:
Know what can/cannot be used in a shared workbook:
These features are disabled when you share the workbook:
- Pivot Table: after sharing, users can view the report, but not change it
- Conditional Formatting
- Data Validation
- VBA and Macro
- few other features that can be found in the Help function
MS Office Support offers a good write-up on how to share your workbook. Link here.
IMO the notes are great.