If you need a group of people to review a workbook, you can save it for sharing, distribute a copy of the workbook to each member of the group, collect the workbooks after changes are made, and then merge the changes into a single workbook. In order to merge changes, you must distribute copies of the same original workbook to all reviewers, and the workbook must be shared. In addition, all users of the shared workbook must:
– Save a copy of the original workbook with a unique file name (adding their own name to the file name, for example).
– Add their changes to their own copy of the original workbook.
– Save all the copies in the same folder as the original (or send them to the owner to place in the original folder).
Tip When you merge workbooks, be sure that multiple reviewers are not editing the same cells; if so, you’ll need to carefully review each merged workbook to ensure that the correct #data is collected, and that subsequent reviewers’ edits do not overwrite the data.
Before you begin, you need to add the Compare And Merge Workbooks command to the Quick Access Toolbar or to the ribbon. If you work with shared workbooks frequently, you might add this command to a custom group on the Review tab.
See Also For more information about customizing the ribbon and the Quick Access Toolbar, see #section 1.4, “Customize options and views for worksheets and workbooks” in MOS 2013 Study Guide for Microsoft Excel by Joan Lambert (Microsoft Press, 2013).
Tip When you distribute separate copies of a workbook to a group, base the workbook
on a template. For more information, see section 1.1, “Manage multiple workbooks.”
To begin, open the original workbook that was used to make the distributed copies, then click Compare And Merge Workbooks. Select the first copy of the file you want to merge in the Select Files To Merge Into Current Workbook dialog box, click Open, and then repeat these steps for each version (or press Ctrl+click to select multiple files at once).
After you merge workbooks, you can display all changes, and accept or reject them, as you do with other shared workbooks.
See Also For the steps you follow to review track changes, see “Reviewing Changes” earlier in this chapter.
➤ To add the Compare and Merge Workbooks button to the Quick Access Toolbar
1. In the Backstage view, click Options.
2. In the Excel Options dialog box, display the Quick Access Toolbar page.
3. In the Choose Commands From list, select All Commands.
4. In the commands list, select Compare and Merge Workbooks, and then click the Add button.
➤ To set up your workbooks for distribution and merging
1. Open the workbook you want to distribute.
2. On the Review tab, in the Changes group, click Share Workbook.
3. In the Share Workbook dialog box, on the Editing page, select the Allow changes by more than one user at the same time check box.
4. On the Advanced page, in the Keep change history for box, specify the time span that users have to review the file. Then click OK to save the shared workbook.
5. In the Backstage view, use the Save As command to create a separate copy of the workbook for each reviewer. Use the original workbook you shared as the master.
6. Pass out the copies to reviewers. Be sure to let them know when the files are expected back.
➤ To merge changes
1. Open the master shared workbook.
2. On the Quick Access Toolbar, click the Compare and Merge Workbooks button to display the Select Files to Merge into Current Workbook dialog box. (If the Merge And Compare Workbooks button is not visible, you’ll need to add it to the Quick Access Toolbar.)
3. Select the files you want to merge, and then click Open.
4. On the Review tab, in the Changes group, click Track Changes, and then click Accept/Reject Changes.
5. In the Accept or Reject Changes dialog box, move from revision to revision and accept or reject each one, or click either Accept All or Reject All.