Before you start: if your worksheets are identical, it's probably easier to create 3D-references (if you have one workbook) or External References (if you have multiple workbooks) to consolidate your data. However, the beauty of the Consolidate feature is that it can easily sum, count, average, etc this data by looking at the labels.
Because our worksheets are not identical, we want Excel to sum cells that have the same labels.
In your Excel worksheets, you may often need to merge two or more cells into one large cell.
However, it requires that all the cells to be merged reside in one area in one column.
For example, you can use the Consolidate command to total all budget spreadsheets prepared by each department in the company or to create summary totals for income statements for a period of several years.
If you used a template to create each worksheet you’re consolidating, or an identical layout, Excel can quickly consolidate the values by virtue of their common position in their respective worksheets.
By Greg Harvey Excel 2016 allows you to consolidate data from different worksheets into a single worksheet.
Using the program’s Consolidate command button on the Data tab of the Ribbon, you can easily combine data from multiple spreadsheets.To begin consolidating the sheets in the same workbook, you select a new worksheet to hold the consolidated data.