How to Merge Excel Worksheets without Copying & Pasting

In this article, we will be familiarized with the topic which is “Merge Excel worksheets without Copying & Pasting”. The problem that occurs while copying different elements of a worksheet in another worksheet is that you may not get the actual formatting of the elements that you require. Copy and pasting a large number of elements in a worksheet can take a lot of time also. We will get to know the process of how power query combines multiple excel files and how to consolidate data from multiple worksheets in a single worksheet.

How You Can Consolidate Data from Multiple Worksheets in a Single Worksheet

Suppose you have two worksheets with the same product name. The number of sales in 3 months are given and you want to find the total amount of sales of each product in each month. Look at the picture below to see the two worksheets containing products name and monthly sales of which we will be working on.

You can use the Excel Consolidate feature to combine the two worksheets data in a single worksheet to perform this kind of task. The procedure is given below.

• In a new worksheet click on the Consolidate feature under the Data Tools option of Data

• In the Consolidate dialogue box, Select Sum in the Function drop-down option. And in the Reference section write Sheet1!$A$1:$D$5. Put tick marks on Top row and Left column option below and then press Add.

• Now in the Reference section write down Shee4!$A$1:$D$5 to attach the data from Sheet4 and press Add. After this Press OK.

• After you press OK. You will see that some data are inserted in your new worksheet which contains different product names with the monthly sales. In this worksheet, the monthly sales amount is added together.

How Power Query Combine Multiple Excel Files (Power Query Append Feature)

Excel introduces us to its new feature Power Query which can capable of doing multiple things that couldn’t be used in the previous versions. With the help of the power query, it is quite easy to combine multiple data from different worksheets to a single worksheet.

How to Load Different Worksheets in Power Query

Let`s say we have some products name along with their monthly sales in different worksheets. We want to combine some of the data in a single worksheet. Before that, we need to load the worksheets in the power query first. The process of loading the worksheets in power query is given below.

• First, from the Data tab, open your worksheet in Power Query like the picture below.

• Select your working file and in the Navigator, dialogue box put a tick mark on the Select multiple items and select all the items. Press on to the Load option to begin the power query.

• You will see the workbook query after this on the right side of your worksheet.

Excel Power Query Append Feature

• Let`s say you want to combine the data from Sheet1 and Sheet5. Just look on to the data of these sheets first that you want to combine.

• Click on the Append option under the Combine Queries option of the New Query of the Data

• In the Append dialogue box do the following and press OK.

• You will get to see the preview with the power query editor. Click on the Close & Load option in the left corner of the query editor to continue the process.

• After this, you will get to see the merging in a new worksheet like the picture below.

Download The Working File

Conclusion

The above two procedures are the best to combine/merge multiple worksheets into a single worksheet without copy and pasting. You can use VBA codes to combine multiple worksheets also but while doing that your data may get distorted or disoriented.

Hope this article suits your interest. Happy Excelling.