Md. Shamim Reza
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.
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.
Read More: How to Merge Data from Multiple Workbooks in Excel (5 Methods)
Similar Readings
- [Fix:] Excel Unable to Merge Cells in Table
- Merge Cells in Excel Table (7 Ways)
- How to Merge Text from Two Cells in Excel (7 Methods)
- Merge Text Cells in Excel (9 Simple Methods)
- How to Merge and Center Cells in Excel (3 Easy Methods)
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.
- 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.
- 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.
Read More: How to Merge Cells in Excel with Data (3 Ways)
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.
- Finally, you will see the data perfectly merged as shown in the following picture.
Read More: VBA to Merge Cells in Excel (9 Methods)
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.
Hi,
1. Do you teach one to one. (to consolidate data from report food panda/grabfood/shopee)
2.I try to merge multiple worksheet (diff type of report) into one workbook into separate sheets.
Hello Siti,
First of all thanks for your feedback. You can follow the below article to solve your problem. Go through it and you will get the solution hopefully.
You can also use the following VBA code to merge multiple sheets into one and into separate sheets. But you have to put all the files into a specific folder and paste the folder path into the code. (mentioned in the article).
Sub ConsolidateFiles()Dim FileList, CurrentF As VariantDim nFiles, nSheets As IntegerDim cSh As WorksheetDim cWB, sWB As WorkbookFileList = Application.GetOpenFilename(FileFilter:="Microsoft Excel Workbooks (.xls;.xlsx;.xlsm),.xls;.xlsx;.xlsm", Title:="Choose Excel files to merge", MultiSelect:=True)If (vbBoolean <> VarType(FileList)) ThenIf (UBound(FileList) > 0) ThennFiles = 0nSheets = 0Application.ScreenUpdating = FalseApplication.Calculation = xlCalculationManualSet cWB = ActiveWorkbookFor Each CurrentF In FileListnFiles = nFiles + 1Set sWB = Workbooks.Open(Filename:=CurrentF)For Each cSh In sWB.SheetsnSheets = nSheets + 1cSh.Copy after:=cWB.Sheets(cWB.Sheets.Count)NextsWB.Close SaveChanges:=FalseNextApplication.ScreenUpdating = TrueApplication.Calculation = xlCalculationAutomaticEnd IfEnd IfEnd SubI hope you can get what you want after using the code. thanks again and keep supporting us.
Regards,
Fahim Shahriyar Dipto
Excel & VBA content Developer.