How to Merge Excel Worksheets Without Copying and Pasting

The problem that occurs while copying different elements of a worksheet into another worksheet is that you may not get the actual formatting of the elements that you require. Copying and pasting a large number of elements into a worksheet in Microsoft Excel can take a lot of time as well. We will get to know the process of how Power Query combines multiple excel files and how to consolidate data from multiple worksheets into a single worksheet. In this article, I will show you how to merge Excel worksheets without copying and pasting.


Download Practice Workbook

You can download the free Excel workbook here and practice on your own.


3 Easy Ways to Merge Excel Worksheets Without Copying and Pasting

In this article, you will see three easy ways to merge Excel worksheets without copying and pasting. In the first method, I will consolidate data from multiple sheets into one sheet. In my second approach, I will take the help of the Power Query tool of Excel. Lastly, I will apply a VBA code to merge multiple Excel worksheets.

To show all the procedures, I will use the following two data sets in two different sheets.

3 Easy Ways to Merge Excel Worksheets Without Copying and Pasting


1. Consolidating Data from Multiple Worksheets to Merge Without Copying and Pasting

Suppose you have two worksheets with the same product name. The number of sales in three months is given and you want to find the total amount of sales of each product in each month. That’s when you will need the Consolidate feature of Excel to perform this task. See the following steps to understand this feature.

Step 1:

  • First of all, take an empty worksheet in the same workbook and select a cell from where you want to see the result.
  • Then, go to the Data tab of the ribbon and choose Consolidate, which is under the Data Tools group.

Selecting Data Tab for Consolidating Data from Multiple Worksheets as a Easy Way to Merge Excel Worksheets Without Copying and Pasting

Step 2:

  • Secondly, you will see the Consolidate dialog box.
  • Here, from the Function drop-down select Sum.
  • Then, in the Reference type box, enter the data range from the first data set.
  • Thirdly, mark both options under the Use labels in the header.
  • Lastly, press Add.

Inserting Data Set from First Workhseet for Consolidating Data from Multiple Worksheets as A Easy Way to Merge Excel Worksheets Without Copying and Pasting

Step 3:

  • Thirdly, again in the Reference type box, insert the data range of the second data set.
  • After insertion, press Add.
  • Lastly, click OK.

Inserting Data Set from Second Workhseet for Consolidating Data from Multiple Worksheets as A Easy Way to Merge Excel Worksheets Without Copying and Pasting

Step 4:

  • Fourthly, after pressing OK. You will see that some data is inserted in your new worksheet that contains different product names with the monthly sales.
  • In this worksheet, the monthly sales amount is added together.

Step 5:

  • Fifthly, in the new worksheet, you will see the Product Name heading is missing.
  • That’s because it was in the B4 cell in both of the worksheets, and in the Consolidate feature, this cell is taken as the reference cell.
  • So, after you perform the Consolidate feature, the reference cell text is not displayed.
  • Consequently, format the new worksheet with the newly added data.

Showing Final Result for Consolidating Data from Multiple Worksheets as a Easy Way to Merge Excel Worksheets Without Copying and Pasting

Read More: Using VLOOKUP to Merge Two Sheets in Excel (with Easy Steps)


2. Utilizing Power Query Tool to Merge Excel Worksheets

Excel introduces us to its new feature, Power Query which is capable of doing multiple things that couldn’t be used in the previous versions. With the help of Power Query, it is quite easy to combine multiple data from different worksheets into a single worksheet. Also, using this feature, you can merge two Excel sheets based on the same column. So, to learn more about this procedure, follow the below-given steps.

Step 1:

  • First of all, I will transform both data sets into tables.
  • For that, select the data range B4:E8 of the first data set.
  • Then, go to the Insert tab of the ribbon, and from there select Table.

Inserting Table into Data Range for Utilizing Power Query Tool as a Easy Way to Merge Excel Worksheets Without Copying and Pasting

Step 2:

  • Secondly, you will see the Create Table dialog box.
  • Here, input the proper data range and then select OK.

Step 3:

  • Thirdly, you will see that your data range will convert into an Excel table after the previous steps.
  • Moreover, transform the second data set into a table by following the above steps.

Transforming Data Range into Table for Utilizing Power Query Tool as a Easy Way to Merge Excel Worksheets Without Copying and Pasting

Step 4:

  • Fourthly, for merging, open a new worksheet and go to the Data tab of the ribbon.
  • Then, from the Get & Transform Data group, select Get Data.

Selecting Data Tab for Utilizing Power Query Tool as a Easy Way to Merge Excel Worksheets Without Copying and Pasting

Step 5:

  • Fifthly, from the previous command, select From File.
  • Then, choose From Excel Workbook to load all the worksheets for merging.

Step 6:

  • After that, browse to the location where the workbook is placed that contains the sheets from merging.
  • Then, choose the workbook and select Import.

Selecting Excel Workbook with Multiple Worksheets for Utilizing Power Query Tool as a Easy Way to Merge Excel Worksheets Without Copying and Pasting

Step 7:

  • Then, after importing the workbook, you will see the Navigator dialog box.
  • Here, mark the option. Select multiple items for merging.
  • Then, select all the tables from the workbook that you want to merge.
  • Lastly, press Load.

Step 8:

  • After that, you will see the Queries & Connections window pane after loading the tables.

Step 9:

  • Afterward, again go to the Get Data command from the Data tab of the ribbon.
  • This time, from the command dropdown, select Combine Queries.
  • From the second dropdown, choose Merge.

Applying Merge Command for Utilizing Power Query Tool as a Easy Way to Merge Excel Worksheets Without Copying and Pasting

Step 10:

  • Here, you will see the Merge window for selecting the sheets.
  • From there, first, select the tables that you want to merge.
  • Then, select the column that is the same for both tables.
  • Lastly, press OK.

Step 11:

  • Afterward, you will see the loaded data sets merged together.
  • Here, the second table is not fully expanded.
  • To expand the table, click on the icon that is in the upper right corner of the heading.

Step 12:

  • From there, a new dialog box will appear.
  • Here, deselect the header of the column that you want to be the same in the new worksheet.
  • Then, deselect the mark from Use original column name as prefix.
  • Lastly, press OK.

Step 13:

  • After that, you will see the newly merged sheet in the Power Query.
  • To transfer it into the worksheet, choose Close & Load.

Step 14:

  • Finally, you will see the data from the merged worksheets in one based on the same column.

Showing Final Result for Utilizing Power Query Tool as a Easy Way to Merge Excel Worksheets Without Copying and Pasting

Step 15:

  • Additionally, if you want to append the worksheets, that means merging the worksheets based on rows, then after converting both the data sets into tables, open a new worksheet.
  • Then, again go to the Get Data command from the Insert tab and select Combine Queries.

Applying Append Command for Utilizing Power Query Tool as a Easy Way to Merge Excel Worksheets Without Copying and Pasting

Step 16:

  • Here, you will see the Append window.
  • Firstly, select the table number that you want to append.
  • Then, select all the tables according to their serials.
  • Lastly, press OK.

Step 17:

  • After that, the Power Query will append both the tables from the worksheets into a single one.
  • Here, select Close and Load to transfer this merged table into the main worksheet.

Step 18:

  • Consequently, the final result will look like the following image.

Showing Final Result for Appending Multiple Workhseets through Utilizing Power Query Tool as a Easy Way to Merge Excel Worksheets Without Copying and Pasting

Read More: How to Merge Excel Sheets into One Workbook (4 Suitable Ways)


3. Applying VBA Code to Merge Excel Worksheets Without Copying and Pasting

In this last procedure, I will use a VBA code for merging multiple worksheets without copying and pasting. For that, I won’t need any features or tools from Excel. I will insert the needed sequence and command in the code, and after running, it will do the merging automatically. Go through the following steps for a better understanding.

Step 1:

  • Firstly, open a new worksheet and then go to the Developer tab of the ribbon.
  • Then, from the Code group, select Visual Basic.

Selecting Devloper Tab for Applying VBA Code as a Easy Way to Merge Excel Worksheets Without Copying and Pasting

Step 2:

  • Secondly, you will see the VBA window after selecting the previous command.
  • Then, from the Insert tab, select Module.

Step 3:

  • Thirdly, copy the following code and paste it into the module.
  • After copying, save the code.
'Giving name of sub procedure
Sub Merge_Excel_Worksheets()
'Declaring variables
Dim x As Integer
'Enabling error handling
On Error Resume Next
'Seleting a new worksheet for showing result
Sheets(1).Select
Worksheets.Add
'Naming the new workhseet
Sheets(1).Name = "Merged"
'Selecting first sheet for merging
Sheets(2).Activate
'Addressing the cell range
Range("B4:E8").EntireRow.Select
'Selecting the destination for new cell range
Selection.Copy Destination:=Sheets(1).Range("B4:E8")
'Starting the for loop
For x = 2 To Sheets.Count
'Activating x number of sheets for merging
Sheets(x).Activate
'Selecting the range for merging
Range("B4:E8").Select
'Selecting the current region
Selection.CurrentRegion.Select
'Selecting the tables without selecting the headers
Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select
'Merging all data in the new sheet
Selection.Copy Destination:=Sheets(1).Range("B65536").End(xlUp)(5)
Next
End Sub

Writing Code for Applying VBA Code as a Easy Way to Merge Excel Worksheets Without Copying and Pasting

VBA Breakdown

  • Firstly, name the function or sub procedure.
Sub Merge_Excel_Worksheets()
  • Secondly, declare variables.
Dim x As Integer
  • Thirdly, Enable error handling.
On Error Resume Next
  • Fourthly, select a worksheet for showing the result.
Sheets(1).Select
Worksheets.Add
  • Fifthly, name the newly created worksheet.
Sheets(1).Name = "Merged"
  • Then, select the worksheet with the first data set for merging.
Sheets(2).Activate
  • After that, giving the cell range of the first data set
Range("B4:E8").EntireRow.Select
  • Additionally, selecting the destination cell range after merging.
Selection.Copy Destination:=Sheets(1).Range("B4:E8")
  • Then, start the for a loop.
For x = 2 To Sheets.Count
  • Afterward, activating all of the worksheets in the workbook for merging and selecting their data range.
Sheets(x).Activate
Range("B4:E8").Select
  • After that, select the data range of those worksheets and merge them all in the Merged worksheet.
Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select
Selection.Copy Destination:=Sheets(1).Range("B65536").End(xlUp)(5)

Step 4:

  • Fourthly, press F5 or the Run button to run the code for performing the task.
  • Before pressing, keep the cursor in the module.

Step 5:

  • Finally, after running this code, you will see a new worksheet with all the merged data in raw format.

Step 6:

  • Consequently, add the headers and format the sheet as per your choice.

Showing Final Result for Applying VBA Codeas a Easy Way to Merge Excel Worksheets Without Copying and Pasting


Using Power Query to Merge Multiple Excel Files from Folder

In the above discussion, you saw the procedure to merge Excel worksheets without copying and pasting them into one. Now, I will demonstrate how to merge multiple Excel files from a folder. I will again take the help of the Power Query of Excel for that. See the below-given steps for a better understanding.

Step 1:

  • Firstly, take the following workbook containing the sales data for January.
  • Then, select the data range B4:C18 and click on the Insert tab of the ribbon.
  • After that, from the Tables group, select Table.

Step 2:

  • Secondly, in the Create Table dialog box, after checking all the criteria, select OK.

Step 3:

  • Consequently, this command will turn the data set into a tabular format.
  • Then, save this workbook in a specific folder.

Step 4:

  • Fourthly, like the following image, store all the workbooks that you will merge into the same folder, and turn all their data ranges into the tabular format following the previous steps.

Step 5:

  • Fifthly, open a new worksheet, and from the Data tab, select Get Data.
  • Then, from the drop-down choose From File, and after that select From Folder.

Step 6:

  • Sixthly, you will see a new window containing all the workbooks.
  • From there, select Combine and Transform Data.

Step 7:

  • Afterward, you will see the Combine Files window.
  • Here, see the sample files and the data set along with it.
  • While setting the Parameter, select Table instead of the sheet name.
  • Lastly, press OK.

Step 8:

  • Afterward, you will see all the workbooks have been merged into one.
  • To transfer it into the new worksheet, select Close & Load.

Step 9:

  • Finally, the new worksheet with the merged workbooks will look like the following image.


Using CMD to Merge Excel Files Into One

In the last discussion of this article, I will show you the procedure of merging multiple Excel files into one using  CMD . For doing that, we have to transform our workbook from .xlsx format to .csv format. See the following steps to do that.

Step 1:

  • First of all, take the same three workbooks shown in the previous discussion.
  • Then, from any of the workbooks go to the File tab of the ribbon.

Step 2:

  • Secondly, from the Home window, select Save As and then Browse.

Step 3:

  • Thirdly, create a folder in the C: drive on your PC as the saving destination of these workbooks.
  • Then, in the saving type, select CSV as the format.
  • Lastly, press Save to save the file.

Step 4:

  • Fourthly, in the following image, you will see all the workbooks in the specified folder.

Step 5:

  • Filthy, we will need to copy the path of the folder Sales File CSV in which we have stored our Excel files in CSV
  • To have the Copy path option, you need to expand the ribbon by clicking on the dropdown symbol in the top right corner.
  • Then, select the folder Sales File CSV and click on the Copy path option.

Step 6:

  • Afterward, press the Windows key + R altogether to open the Run wizard.
  • In the Open type box, press cmd and click OK.

Opening Run Wizard for Using CMD to Merge Excel Files Into One

Step 7:

  • After that, by entering the previous command, you will open the command prompt.
  • Here, type cd and give a space and paste the path of the folder copied in Step 5.
  • Then, press Enter.

Step 8:

  • After pressing Enter, you will see the directory from the previous line.
  • Then type, copy *.csv Merged.csv.
  • Here, you must provide space after the copy word.
  • Also, I chose Merged as the new file name.

Writing Code in CMD for Using CMD to Merge Excel Files Into One

Step 9:

  • Then after pressing Enter, this code will automatically merge all the workbooks.

Step 10:

  • After that, come back to the folder where all the CSV files are saved, and you will see the newly merged files.

Step 11:

  • Then open the file and you will find all the data in the raw format.

Step 12:

  • As there are data from multiple worksheets, scroll down to see the remaining data.

Step 13:

  • Then, again, save this file in the .xlsx format.

Step 14:

  • Finally, open the Excel file and format the sheet like the following image.

Step 15:

  • Consequently, scroll down to see the whole sheet for a large data set.


Conclusion

That’s the end of this article. I hope you find this article helpful. After reading the above description, you will be able to merge Excel worksheets without copying and pasting. Please share any further queries or recommendations with us in the comments section below.

The ExcelDemy team is always concerned about your preferences. Therefore, after commenting, please give us some moments to solve your issues, and we will reply to your queries with the best possible solutions ever.


Related Articles

Siam Hasan Khan

Siam Hasan Khan

Hello! Welcome to my Profile. Here I will be posting articles related to Microsoft Excel. I have completed my BSc in Electrical and Electronic Engineering from American International University-Bangladesh. I am a diligent, goal-oriented engineer with an immense thirst for knowledge and an attitude to grow continuously. Continuous improvement and life-long learning are my mottoes.

2 Comments
  1. thanks a lot for this!! really helps!!! my problem is this: my sis sent me a bunch of excel files with different column headers, different sheet names with different data per sheet. how do I combine them into 1 big database? amalgamate them!! thanks again Siam!!!

    • Hi Paulino!

      It is not clear from your comment whether you want all data consolidated in a single worksheet or to create a master workbook containing all worksheets from those files. I am assuming you want to do the latter as the datasets are completely different. Then follow the steps below.

      First, open a new workbook and save it.

      Then open any one of those files. Select the first sheet tab. Hold the SHIFT key and select the last sheet tab. This will select all sheets in that workbook.

      Next, align the two workbooks side by side.

      Then, drag the selected sheets to the master workbook. Now hold the CTRL key and drop the sheets beside the sheet tabs of the master workbook. After that, all the sheets from the file will be copied to the master workbook.

      Now, open the other files one by one and repeat the procedures. Finally, you will get the master workbook containing all the sheets from those files.

      Please let us know if you got that done by following the above steps. Thank you for being with us.

      Regards
      Md. Shamim Reza(Exceldemy Team)

Leave a reply

ExcelDemy
Logo