The following tutorial explains the common uses in detail: Combine data from multiple data sources (Power Query).If you are looking for a quick way to match and merge data from two worksheets, then you can either employ the Excel VLOOKUP function or embrace the Merge Tables Wizard.The tutorial covers two most common scenarios: consolidating numeric data (sum, count, average, etc.) and merging sheets (i.e. The quickest way to consolidate data in Excel (located in one workbook or multiple workbooks) is by using the built-in Excel Consolidate feature. Supposing you have a number of reports from your company regional offices and you want to consolidate those figures into a master worksheet so that you have one summary report with sales totals of all the products.As you see in the screenshot below, the three worksheets to be consolidated have a similar data structure, but different numbers of rows and columns: To consolidate the data in a single worksheet, perform the following steps: As you see, the Excel Consolidate feature is very helpful to pull together data from several worksheets. In particular, it works for numeric values only and it always summarizes those numbers in one way or another (sum, count, average, etc.) If you want to merge sheets in Excel by copying their data, the consolidation option is not the way to go.
After experimenting a bit, you will see how useful all the features are.
A fully functional 15-day trial version of the Merge Tables wizard is available here.
And the 15% off coupon code I've provided for the Consolidate Worksheets Wizard works for this add-in as well :) This is how you consolidate data and merge sheets in Excel.
If you are a power Excel user and feel comfortable with macros and VBA, you can combine multiple Excel sheets into one by using some VBA script, for example this one.
Please keep in mind that for the VBA code to work correctly, all of the source worksheets must have the same structure, the same column headings and same column order.