The Compare and Merge Button
Before we get too far, we have to add a command button to the Quick Access Toolbar. The button we will add is called “Compare and Merge Workbooks" and will allow us to do just that.
In Excel 2013, click the Quick Access menu drop down box and select More Commands.
In the dialog box that opens, select All Commands under the heading Choose Commands From.
In the list of commands, scroll down to Compare and Merge Workbooks.
Press the Add button to move it to the right side of the window (Figure 1).
Setting up the Workbook
In order to merge workbooks, you must be using a shared workbook and the other copies to be merged in must be copies of the original workbook.
To share out your workbook, select the Review tab and click Share Workbook. Check the box to Allow Changes by More Than One User… and click OK.
For my sample workbook, I have a grading sheet that teachers can pass around to fill in student grades. Once the semester is up, they can merge all grades into a single workbook. As you can see in Figure 2, I have the workbook set up as described with no grades entered.
Each contributor will make a copy of this document and fill in the grades for their class. When done, we will merge the changes back into the master document.
At the end of our semester, we will get the copies of our spreadsheet back. In Figure 3, we can see one teacher has filled in the stats for his class (Class 1) and a second teacher has filled in the data for her class (Class 2).
With the master document open, we will click the Compare and Merge button that was set up earlier. Select one or more workbooks that you want to merge and click Open. After a moment, all of the data entered in the two separate worksheets will appear in your master document (Figure 4).
Be sure to make changes in the copied sheets – if you start making changes in the master copy and later want to merge changes from the copy again you will run into some odd errors.
After you are done merging changes, you can easily disable sharing so you can focus on just keeping the master document up to date.
If you already have different documents you need to merge, the shared workbook method may not work for you. In this case, the most efficient way of merging data will be to either move worksheets between files or use copy and paste to move data between spreadsheets.