The tutorial demonstrates different ways to combine sheets in Excel depending on what result you are after - consolidate data from multiple worksheets, combine several sheets by copying their data, or merge two Excel spreadsheets into one by the key column.Today we will tackle a problem that many Excel users are struggling with daily - how to merge multiple Excel sheets into one without copying and pasting.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.However helpful it is to check and view data parts individually, we sometimes need to combine them to create a Pivot Table, chart, or report in Excel.
After finishing the settings, click Finish button to combine the worksheets, and then you can open the combined workbook directly by clicking Yes. And you will be asked if you want to save this scenario, If you want to save this scenario, please click Yes, and enter the name of the scenario, (see following screenshot), and when you want to use this scenario, you just only need to click Scenario button to choose the name of the scenario you need on step2 wizard.Data consolidation in Microsoft Excel is arguably one of the most challenging and time-consuming tasks.But you can do it hands-down with Consolidate Worksheets Wizard.Today, I will talk about some quick tricks for you to solve this problem. Merge all worksheets of active workbook into one worksheet with VBA code Merge worksheets or workbooks into one worksheet with Kutools for Excel The following VBA code can help you to get data from all worksheets of active workbook together into a new single worksheet.