TIP 17 : MS Excel : Merge excel workbooks with single mouse click action

As a team lead you might need to collaborate the data of your team members by requesting data in different sheet and then merging it back in single sheet. Manually merging the details takes lot of time and is a very time consuming activity. In this blog we explain how to create a shared worksheet and merge the data from different sheets with single mouse click.

Let us create a worksheet to merge leave details of team members.

• Create a sheet with leave details template format and save the excel workbook.

• Make this workbook as shared workbook. Go to review tab and click on the share workbook button.

• It will open up Share Workbook dialog window. Select the check box “Allow changes by more than one user at the same time. This also allows workbook merging” and press OK button.

• There will be prompt to save the workbook, press OK to continue.

• You will observer that your sheet now changed to shared sheet as we have shared keyword added next to file name in the header bar.

• This workbook is now ready to be shared among team members. They can fill the details and can save the workbook with different names as well. We can easily merge the sheets using compare and merge feature of excel from quick access toolbar.

Now, share the leave details sheet with all the five employees so that we can have five different sheets to be merged into one single sheet.

Leave_Details_1.xlsx

Leave_Details_2.xlsx

Leave_Details_3.xlsx

Leave_Details_4.xlsx

Leave_Details_5.xlsx

• Add compare and merge option by customizing the quick access toolbar. Click on the Customize Quick Access Toolbar icon and then select More Commands.. from the list or alternatively you can access it from File tab then click on options and select Quick Access Toolbar from the left hand side panel.

• Now select “Commands not in Ribbon” from the Choose commands drop down and then scroll down to select “Compare and Merge Workbooks…” option.  

• Add the Compare and Merge Workbooks… to the toolbar by clicking on the Add button and press OK.

• You will find the Compare and Merge Workbooks option added to the workbook header.

• Click on the Compare and Merge Workbooks icon it will open up file manager window. Select the files to be merged.  

• We can see all files are merged into single file in fraction of seconds.