The clever reader might notice that in this simple example you could just as easily have used a VLOOKUP to achieve the same, but this serves as a general introduction and example to using Power Query in Excel.
Power Query is far superior to mere functions and features when it comes to manipulating very large sets of data, especially from sources outside of Excel.
If there are just a couple of workbooks need to be combined, you can use the Move or Copy command to manually move or copy worksheets from the original workbook to the master workbook. Open the workbooks which you will merge into a master workbook. Select the worksheets in the original workbook that you will move or copy to the master workbook. After selecting the needed worksheets, right click the sheet tab, and then click Move or Copy from the context menu. Then the Move or Copy dialog pops up, in the To book drop-down, select the master workbook you will move or copy worksheets into.
Select move to end in the Before sheet box, check the Create a copy box, and finally click the OK button.
In this guide, I will walk you through how to consolidate data from two different Excel workbooks.
One of the workbooks (the one called ‘Products.xlsx’) contains sample product data from a company that sells furniture.
In my consolidation spreadsheet, I'd like to pull the same cell (i.e. If your question is resolved, mark it SOLVED using the thread tools 3.B4) from each of the spreadsheets (same sheet title 'Sheet2' in all spreadsheets) i.e. in consolidation spreadsheet, there will be 100 cells, each linking from B4 of each of the 100 spreadsheets. If there are multiple workbooks need to be merged into one, you can apply the following VBA codes to quickly achieve it. In the Microsoft Visual Basic for applications window, click Insert "" Workbooks. Open Filename:=x Str Path & x Str FName, Read Only:=True x Str AWBName = Active Workbook. Open Filename:=Path & Filename, Read Only:=True For Each Sheet In Active Workbook.
Now you’ve entered the actual ‘Power Query Editor’ and you might notice that the design and layout don’t look much like Excel, but more like a “real” database (e.g. The data that is loaded into the editor now consists of the product numbers and the product names. In step 2 and 3 you select which ‘identifier’ the merging will follow.