Merge Data in Excel from Multiple Worksheets (3 Methods)

We often need to merge data in excel from multiple worksheets to get a better understanding of the data. It helps to summarize data. It also helps to avoid working with large amounts of different types of data in a single worksheet. This article shows 3 different ways to merge data in excel from multiple worksheets easily. The following picture gives an idea of the purpose of this article.

Merge data in excel from multiple cells


Download Practice Workbook

You can download the practice workbook from the download button below.


3 Methods to Merge Data in Excel from Multiple Worksheets

Here, I am going to illustrate 3 methods for you to merge data in excel from multiple worksheets. Let’s begin!


1. Merge Data in Excel from Multiple Worksheets Using Consolidate Feature

Suppose you have two worksheets for the sales made by your employees in the first two months of this year. Now you want the total of their sales in another worksheet.

Then, follow the steps below to easily do that using the Consolidate feature in excel.

📌 Steps

  • First, select cell C5 as follows.

  • Then, click on the Consolidate icon from the Data You will find it in the Data Tools group as shown in the following picture.

  • Next, keep the Sum option selected in the Function field. After that, select the upward arrow in the Reference field as shown below.

  • Then, toggle back to the first worksheet and select the first range of cells as shown below. Next, click on the downward arrow in the input box for Reference.

  • After that, click on the Add button. Then, you will see a reference added in the All references field. Next, click on the upward arrow again in the Reference field.

  • Next, repeat the same procedure to input the cell reference from the second worksheet.

  • After that, make sure that you have added the references properly. Then, hit the OK button.

  • Finally, you will see the data merged from the two worksheets in the desired worksheet as follows.

Merge data in excel from multiple cells using the Consolidate tool


Similar Readings


2. Merge Data in Excel from Multiple Worksheets Using Power Query

Imagine you have two worksheets containing sales data in tables for two of your shops. You want to merge the data to the following format in another worksheet.

Then, follow the steps below to easily do that using the Power Query tool in excel.

📌 Steps

  • First, select Data >> Get Data as shown below.

  • Then, choose From Other Sources >> Blank Query. This will open the Power Query Editor.

  • Now, enter the following formula in the formula box in the Power Query Editor.
=Excel.CurrentWorkbook()
  • This is a case-sensitive formula. So, be careful to enter it properly. After that, you will see the tables listed as follows. Then, select the double-sided arrow.

Merge data in excel from multiple cells using Power Query

  • After that, uncheck Use the original column name as a prefix. Then, hit the OK button.

  • After that, you will see the data merged as below. You can delete the column for source tables now or later.

Merge data in excel from multiple cells with Power Query

  • Next, select File >> Close & Load To as shown in the following picture. This will open a new dialog box.

  • After that, select Existing Worksheet and then cell B4. Next, hit the OK

  • Finally, you will see the data from the worksheets merged in the new worksheet as follows.

Merge data in excel from multiple cells using Power Query


3. Merge Data in Excel from Multiple Worksheets Using VBA

Consider, you have two worksheets containing sales data for two different months. You want those data merged in a different worksheet to be able to easily compare those data.

Then, follow the steps below to be able to do that using VBA in excel.

📌 Steps

  • First, press ALT+F11 (on Windows) or Opt+F11 (on Mac) to open the Microsoft Visual Basic for Applications
  • Then, select Insert >> Module. This will create a blank module.

  • After that, copy the following code. Then, paste it on the blank module.
Sub MergeData()

Worksheets("VBA").Range("B4:C17").Value = Worksheets("Sales1").Range("B4:C17").Value

Worksheets("VBA").Range("D4:D17").Value = Worksheets("Sales2").Range("C4:C17").Value

End Sub
  • Then, run the code using the Run icon or the Run tab as shown below.

Merge data in excel from multiple cells with Excel VBA

  • Finally, you will see the data perfectly merged as shown in the following picture.

Merge data in excel from multiple cells using VBA


Things to Remember

  • The Consolidate tool is also helpful to get averages, maximums, minimums, products, etc. of data from multiple worksheets.
  • Formulas in Power Query are case-sensitive. Make sure to enter the formula properly.
  • You may change the ranges in the VBA code according to your dataset.

Conclusion

Now you know how to merge data in excel from multiple worksheets in 3 different ways. Please use the comment section below for further queries or suggestions. You can also visit our Exceldemy blog to learn more and more about excel.


Further Readings

Md. Shamim Reza

Md. Shamim Reza

Hello there! This is Md. Shamim Reza. Working as an Excel & VBA Content Developer at ExcelDemy. We try to find simple & easy solutions to the problems that Excel users face every day. Our goal is to gather knowledge, find innovative solutions through them and make those solutions available for everybody. Stay with us & keep learning.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo