Combining Changes Made to Multiple Workbooks

Another way to share a workbook is to make a separate copy of the workbook for each person in your workgroup. This is a good option if not everyone in your group has access to the same network server, if some users need to work on the workbook when they are on the road, or no network or Internet file-sharing options are available to you. In this scenario, after all the distributed copies have been updated with each person's changes, someone collects the copies and merges everyone's work into a master workbook.

You can merge workbooks that were created equal-that is, a set of workbooks created from the same master. When you merge workbooks, all changes made to the merged workbooks are merged into the master workbook. Merging workbooks, like change tracking, is closely linked with the shared workbooks feature; you can merge only workbooks that have been saved with sharing turned on.

The command to merge workbooks, Compare And Merge Workbooks, is well hidden in Excel 2007, but you can easily add it to the Quick Access Toolbar. Click the Microsoft Office Button, and then click the Excel Options button. In the Excel Options dialog box that appears, select the Customize category. In the Choose Commands From list, select Commands Not In The Ribbon. Select Compare And Merge Workbooks in the list of commands, and then click the Add button to add it to the Quick Access Toolbar. In the Customize Quick Access Toolbar list, select whether you want to add the Compare And Merge Workbooks command to the Quick Access Toolbar for all workbooks or just for a specific workbook.

For more information about customizing the Quick Access Toolbar, see "Customizing the Quick Access Toolbar" on page 83.

The following procedure explains how to set up your workbooks for distribution and eventual merging:

1. Open the workbook you want to distribute.

2. Click the Review tab, and then click Share Workbook.

3. On the Editing tab in the Share Workbook dialog box, select the Allow Changes By More Than One User At The Same Time check box.

4. Click the Advanced tab, and make sure a sufficient number of days appears in the Keep Change History For box for all the members of your workgroup to finish their edits and for you to collect and merge the workbooks. If you are unsure about how long to specify, type a large number, such as 999. If this time limit is exceeded, you will not be able to merge workbooks.

5. Click OK to save the workbook in shared mode.

6. Click the Microsoft Office Button, and then click Save As. Save additional copies of the workbook under different names-one for each person in your distribution list. Save one extra copy to use as a master workbook. Because you have turned on sharing, each copy you save is also in shared mode.

7. Distribute the copies to the members of your group.

After you have prepared, distributed, and collected the edited workbooks, you are ready to merge by following these steps:

1. Open the workbook you want to use as the master workbook. All the changes made to the other workbooks will be replicated in the master workbook. You must have saved this master workbook from the same original shared workbook, just as the workbooks you distributed were.

2. Make sure the other workbooks you want to merge aren't open, and then click Compare And Merge Workbooks on the Quick Access Toolbar to display the Select Files To Merge Into Current Workbook dialog box shown in Figure 24-12.

3. Select the files you want to merge.

Figure 24-12: When the master workbook is already open, select the other workbooks to merge.

Note 

Although merging workbooks combines all changes from a set of workbooks, consolidation combines values only from a set of worksheets. (These worksheets can be in different workbooks.) The Consolidate command can assemble information from as many as 255 supporting worksheets into a single master worksheet. For more information about the Consolidate command, see "Consolidating Worksheets" on page 258.

Excel merges the workbooks you select in the Select Files To Merge Into Current Workbook dialog box one by one, in the order in which they appear in the dialog box. Excel takes all the changes made to the merged workbooks and makes them in the master workbook. You can accept and reject changes and display the history worksheet, just as you can with shared workbooks, as described in "Tracking Changes" on page 794.