Share in Excel: Setting up spreadsheets for collaboration

I work with dozens of highly certified and skilled engineers who interact frequently with the businesses contracting our company for managed I.T. services. Most of these clients have staff using Microsoft Office applications—and most Office users, sooner or later, have had some odd experiences within these applications. Generally, our Remote Systems Engineers (RSEs) get to be the heroes. Whether onsite at the client’s office or by phone or messaging from our remote help desk, RSEs field a wide array of questions about Microsoft Word, Excel, Outlook, PowerPoint, OneNote, and the rest of the Office suite.

Occasionally, though, an RSE will pull me aside to ask about an issue he or she hasn’t encountered before. A recent question fielded to me was about a client needing options for sharing a workbook in Excel that would allow multiple users on her executive team to input data simultaneously.

To share or not to Share: Excel documents

Every version of Microsoft Office Excel has included an option to share a workbook that allows multiple users to open the workbook and edit its data. (Note that, by default, workbooks are not shared. Unless the spreadsheet user activates sharing, multiple users attempt to open the same workbook on a shared drive at the same time will receive an error message. Excel will prompt the user that the workbook is already in use by another user.)

Set up workbook for sharing

(Steps below apply to Excel versions 2007-2016)

Click the Review tab, select the Share Workbook option from the Changes Group.

On the Editing tab, select the option to Allow changes by more than one user at the same time. This also allows workbook merging.
The field Who has this workbook open now: will display any user(s) with an open instance of the workbook.

Checking the Advanced tab, you will find default settings in place that will allow for 30 days’ worth of tracking history, will update changes whenever the file is saved, and will prompt when conflicting changes occur.
Press OK.

To ensure data doesn’t get overridden in the process, select Protect and Share Workbook.
1. This tool will allow you to utilize a process called “track changes.” The track changes functionality allows you to approve changes through this process and protect against inadvertent changes.
2. Select Sharing with track changes.
3. Passwords are optional, but omitting one may defeat the purpose of protecting the data. If you opt to use a password, note that the Protect Shared Workbook window shields your password as you type. Make sure to type carefully and record your password selection.
4. Press OK.
In the Save As dialog box, save the workbook in a network location where all users have rights to access

Sharing Excel documents: The process

Let’s pause to discuss what’s really occurring in the background to allow simultaneous changes. This process is actually merging—that is, combining—data. The data merge process involves two steps:

Track Changes—Microsoft Office Merging uses the track changes process to look for changes within the document, comparing the new version to the last saved version. Track changes identifies how data has changed from cell to cell as users add or modify the data. As multiple users work in the spreadsheet, it’s important to be able to see who made specific changes—and to control which data will be accepted.
Merge the data—Merging data will automatically override any existing data once saved, unless step 6 (see above) is enforced. Without protecting the spreadsheet, you could lose valuable data.

In a shared workbook, I can use the Share Workbook tool to see who is in the workbook and what time they entered the workbook. From this dialog box, I am also able to remove other users from the shared workbook.

In the scenario depicted below, several users at the office are responsible to input records of all employees’ time off. One workbook contains the remaining months for 2016. Notice that a letter is keyed into the date(s) from the provided legend. The occurrences of each letter are then counted for a given employee (on a row) and for a total of employees out per day (columns).

At this time, two users are in the workbook making changes to the data.

First check to see that your workbook is shared. The title bar will display [Shared].

Note: Shared workbooks may not support all features of Excel. The program will indicate unsupported features.

It is necessary to save often when sharing a workbook. Every time I click to save, Excel tells me if any changes have been made.

However, with this method it becomes easy to override data. Often, users will forget to protect the workbook to preserve the data as changes are being made and utilize the track changes. Once the workbook is protected, though, the track changes functionality is enabled—so, if other users are in the workbook and editing data simultaneously, track changes will show only my changes by default.

Using Track Changes

To see changes by all users, we must take a look at the track changes feature.

By default, the program wants to find all tracked changes from the last instance the document was saved. You have the option to find changes based on particular users or everyone. This box also states that, by default, it will highlight the cells impacted by changes, but for workbooks that may be large or shared by many, it may be better to select list changes on a new sheet to review all changes and maintain records of those changes for reference at a later date.

Hovering over the indicated cells produces a pop-up describing who made the change, what change was made, and to what value.

To merge the data officially, you will need to accept or reject the tracked changes. Acceptance or rejection may be indicated either by user or one-by-one, and you are able to select a specific date for any changes made, if desired. Select the track changes and choose Accept or Reject Changes

Through the acceptance and rejection process, the user has full control of changes.

All changes must be either accepted or rejected for the merge of data to occur.

Once the document has been saved, the merge of data has occurred and is a final acceptance. The document will no longer highlight any cells that were changed. Users may still alter the data, but it is through the acceptance/rejection process where the merge of those changes considered as finalized.

Stop Sharing

When sharing of the Excel file is no longer needed, you may stop sharing it by following the steps below.

Select the Share Workbook
1. Notify users to save their work and exit the file.
2. If you attempt to Remove a user, you must be advised that forcing a user out of a file will prevent any added data from being shared.
Select Unprotect Shared Workbook to disable protection. (Note that this process is required—you will not be able to remove this file as a shared if this step is missed.)
Select Share Workbook and uncheck Allow changes by more than one user at the same time.
Removing the file as a Shared Workbook will also remove the track change history.