In Microsoft Excel, a group can work on the same workbook. Multiple people can work on the same workbook and merge that after editing. In this way, you can see the changes that others made. It helps people to visualize the changes and create a conclusion. In this tutorial, you will learn to merge and compare workbooks in Excel. We are providing you with three easy and simple steps with proper illustrations.
Why Do We Need to Compare and Merge Workbooks?
Now, you may wonder why we need to merge workbooks. If you are working with a team in the same workbook, you have to merge all the changes there. You can call it a master workbook or the primary workbook. Every member will get a copy of the master workbook. After that, everyone will edit that. Finally, we will merge those workbooks and compare all the changes.
Now, take a look at the following dataset:
As you can see, this is the Master file. Or you can see the main file. Now, we have a workbook that will include the sales amount. Here, we have two salespersons, Max and Elizabeth.
We shared the file among them. This is the workbook of Max:
As you can see, Max has given some data in his workbook.
On the other hand, this is the workbook of Elizabeth:
Elizabeth also has given input in her workbook.
Now, when we merge the workbooks, it will give us the following output:
As you can, we have merged the two workbooks. Now, you can compare these two workbooks in the master workbook in Excel.
How to Compare and Merge Excel Workbooks: 3 Steps
Now, in the following sections, we will provide you with three main steps to merge and compare workbooks in Excel. We recommend you follow these steps carefully and practice them in your workbook. Surely, it will develop your Excel knowledge.
We are using the same dataset from the previous section:
Now, this is our Master file. We will merge all the workbooks and see the changes here.
Remember, compare and merge workbooks in Excel only work for shared workbooks. So, it is quite essential to share the workbook first so that you can merge it later.
Now, you can find the Share Workbook option in the Review tab. But, most of the time, this button is hidden. So, we will add this button to the Quick Access Toolbar.
📌 Steps
- First, click on the File
- Then, click Options (If you are seeing More, then click More > Options)
- Now, select Quick Acess Toolbar from Excel Options.
- Now, from Choose commands from dropdown menu, select All Commands. And from Customize Quick Acess Toolbar dropdown, select the Master.
- Now, select Share Workbook (Legacy) from All Commands. Then Click on After that, it will add this command on the right.
- Next, click on OK. After that, you can see the Share option in the Quick Acess Toolbar.
- Now, click on the Share Then, mark the checkbox and click on OK.
Finally, you have created a sharing option. Now, others can edit the copy of this file and you can merge it later.
Step 2: Enable the Compare And Merge Workbooks Command in the Quick Access Toolbar
Now, before you merge and compare workbooks in Excel, you have to enable the Compare and Merge Workbooks command in the quick access toolbar. Here, we are showing you the steps on how to enable this in your Excel workbook.
📌 Steps
- First, click on the File
- Then, click Options (If you are seeing More, then click More > Options)
- Now, select Quick Acess Toolbar from Excel Options.
- Now, from Choose commands from dropdown menu, select All Commands. And from Customize Quick Acess Toolbar dropdown, select the Master.
- Now, select Compare and Merge Workbooks from All Commands. Then click on After that, it will add this command on the right.
- Next, click on OK. After that, you can see the Share option in the Quick Acess Toolbar.
As you can see, you enabled the Compare and Merge workbooks command in Excel to your Quick Acces Toolbar. Now, you can use this command to merge and compare workbooks.
Read More: How to Combine Multiple Workbooks to One Workbook in Excel
Step 3: Merge and Compare Workbooks in Excel
Now, we have reached the final step. Here, you have to merge the workbooks.
Before we do that, let’s see the other workbooks that we will merge.
This is the workbook of Max that we saved as Max.xlsx:
As you can see, Max has given his input into the workbook.
Now, this is the workbook of Elizabeth which is saved as Elizabeth.xlsx:
Let’s merge these workbooks into the Master workbook.
📌 Steps
- First, click on the Compare and Merge Workbooks button in the Quick Access toolbar.
- Now, click on OK.
- Now, select Max.xlsx and Elizabeth.xlsx.
- Then, click on OK.
Finally, we are successful in merging our workbooks into one. Now, you can compare this to the Master workbook in Excel. It will show all the changes if you have made any.
Read More: How to Merge Excel Files into One Using CMD
💬 Things to Remember
✎ You can not perform this if you haven’t shared your Excel workbook.
✎ The other workbooks must be saved in the same folder as the Master workbook.
✎ You must save the other workbooks with different names.
Download Practice Workbook
We are providing you with all the workbooks that we used to demonstrate. Download these practice workbooks.
Conclusion
To conclude, I hope this tutorial has provided you with a piece of useful knowledge to merge and compare workbooks in Excel. We recommend you learn and apply all these instructions to your dataset. Download the practice workbook and try these yourself. Also, feel free to give feedback in the comment section. Your valuable feedback keeps us motivated to create tutorials like this.
Keep learning new methods and keep growing!
Further Readings
- How to Merge Excel Files into Word Document
- How to Merge Excel Files Based on Column
- How to Merge Multiple Excel Files Into One Sheet by VBA
- Combine Multiple Excel Files Into One Workbook with Separate Sheets
- How to Consolidate Data from Multiple Workbooks in a Single Worksheet
- How to Consolidate Data in Excel from Multiple Workbooks
