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 same products 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 sale 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 to 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 in the 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 name 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 introduce 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 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 its 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 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 to 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 power query editor. Click on to 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.

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 attitude to grow continuously. Continuous improvement and life-long learning is my motto.