Merging Different Copies of a Shared Workbook
Instead of sharing a single workbook with other users on the network and then doing a review in which you accept or reject their changes when you save the file or at some predefined time interval, you can distribute copies of a shared workbook and then merge the changes made by different people into one version.
Distributing the copies
The key elements for successfully merging different copies of a shared workbook are that each copy must have the Change History log turned on and each copy must be saved under a different filename. This means that in order to create the copies of the workbook you want to distribute, you need to follow these steps:
- Turn on the Change History log in the original by opening the Share Workbook dialog box ( Tools → Share Workbook) and then selecting the Allow Changes by More Than One User at the Same Time check box.
- Create copies of the original shared workbook by saving them under slightly different filenames ( File → Save As).
You may want to append numbers or the initials of the people doing the review to the filenames. - Distribute these different copies to the intended users - usually by attaching the workbook files to e-mail messages.
These users can then make their edits and save their changes to the shared copy of the original workbook that they receive from you (which they can then return to you as e-mail attachments).
All editing in the copies you distribute must be made before the time period for keeping the Change History log expires (30 days by default). If you have any doubt that 30 days is enough time, increase the number of days in the Keep Change History For text box on the Advanced tab of the Share Workbook dialog box before you save copies of the original workbook and distribute them to your users.
Merging the changes
When you've received the edited copies you distributed and are ready to merge the changes they contain into one version, follow these steps:
- Open the original shared workbook into which you want to merge changes from the other copies.
Note that the copies of the original workbook whose changes will be merged into the original workbook must not also be open in Excel and they must have different filenames. - Choose Tools → Compare and Merge Workbooks.
Doing this opens the Select Files to Merge Into Current Workbook dialog box, where you indicate the workbook files to merge. - Select the folder that contains the workbook(s) to be merged and then select the files.
Ctrl+click to select more than one file. - Click OK to close the Select Files to Merge into Current Workbook dialog box.
After you click OK, Excel merges the disk version(s) of the selected workbook(s) into the version you have open in Excel (without prompting you to review or accept or reject any of the updates). All changes in the disk version(s) are merged to the workbook open on your screen. You can then save this single, updated version of the workbook under the same name (Ctrl+S) or under a new filename (File → Save As).
Merging changes from copies of the same shared workbook involves no conflict resolution. If users change the same cell in the copies, the program simply replaces the value in the original shared workbook in turn with the value in each merged copy. Don't use this feature if you want to be able to decide which value to accept and which to reject in such conflicts.
