In most office settings there is a shared drive where teams can store common files for everyone to use. Document sharing can lead to greater efficiency and collaboration, but can also be a headache when multiple users are accessing and adapting the same content – especially when it comes to Microsoft® Excel®.
With that in mind, here are a few tips for using Excel to make office collaboration smoother and smarter.
Sharing workbooks
If you share documents, the following warning might be familiar to you: “The document [file name] is locked for editing by another user.”
This appears because someone else already has the file open. But sometimes you need to have multiple users editing a file at once. Maintaining just one workbook can result in less confusion and an up-to-date, easily accessible version of the file for all users.
To enable a shared workbook, do the following:
- On the Review tab, in the Changes group, click Share Workbook.
- On the Editing tab, select the Allow changes by more than one user at the same time. This also allows workbook merging check box.
- On the Advanced tab, select the options that you want to use for tracking and updating changes, and then click OK.
- Do one of the following:
- If this is a new workbook, type a name in the File name box.
- If this is an existing workbook, click OK to save the workbook, click File tab, and then click Save As.
Now your workbook is shared and can be edited by multiple people at the same time. When someone edits the file and saves it, everyone will see the updates immediately. There is an advanced tab in the dialogue box as well, but it is mostly for personalisation preferences, such as how changes are tracked and implemented into the file. It’s also a good idea to take a look at the advanced options to get a sense of how the shared workbook operates.
Tracking changes
You can also track changes in a shared workbook to see what other people have done to it.
To turn on change tracking for a workbook:
- On the Review tab, in the Changes group, click Share Workbook.
On the Editing tab, select the “Allow changes by more than one user at the same time” check box. - Click the Advanced tab.
- Under track changes, click “Keep change history for” and, in the days box, type the number of days of change history that you want to keep.
- Click OK and, if you are prompted to save the workbook, click OK.
To view tracked changes:
- On the Tools menu, point to Track Changes, and then click Highlight Changes.
If the “Track changes while editing” check box is not selected, Microsoft Excel has not recorded any change history for the workbook.
Some features not included
It’s very important to note that Excel automatically turns off some features in shared workbooks. This is to simplify the workbook and avoid confusion since multiple people can be working on the file at once. For example, shared workbooks don’t allow merging cells, conditional formatting, or inserting pictures or graphs – and some formatting changes are not tracked by the Track Changes feature.
If you’re the owner of the shared workbook, you can manage it by removing users from the shared workbook and resolving conflicting changes. And, when all changes have been made, you can stop sharing the workbook.
Comparing and merging workbooks
When it’s time to verify the updates made by other users and compile the final version of the document, the workbook owner can compare and merge workbooks using the command of the same name. This command is not available with other shared workbook commands on the Review tab in the Changes group, but you can add it to the Ribbon Toolbar.
To get the “compare and merge workbooks” button, do the following:
- Click the File tab, and then click Excel Options.
- In the Customize Ribbon, in the Choose commands from list, click All Commands.
- In the list, click Compare and Merge Workbooks, click Add, and then click OK.
* Commands can only be added to custom groups so you’ll need to create at least one of those somewhere to proceed. First you must add a custom group to a default tab or to a new, custom tab. - Open the copy of the shared workbook into which you want to merge the changes.
- On the Ribbon Bar, click Compare and Merge Workbooks.
- If prompted, save the workbook.
- In the “Select files to merge into current workbook” dialog box, click a copy of the workbook that contains the changes that you want to merge, and then click OK.
All users of the shared workbook must save a copy of the shared workbook that contains their changes, and use a unique file name that differs from the original workbook. All copies the shared workbook should be located in the same folder as the shared workbook.
Remember, you can only merge a shared workbook with copies of that workbook that were made from the same shared workbook.
Sharing for productivity and profit
While sharing a workbook seems a bit of a chore, a little up-front preparation can make it easy and save you and your team headaches when it comes to consolidating and finalising a workbook. And, with intelligent use of the track changes feature, you can make sure that nothing gets by your review process.
With tips like these, it’s easy to ensure quick and consistent collaboration of Excel documents within any office team.
Also read: Excel for Business Intelligence