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.
However, even when the data entries are laid out differently in each spreadsheet, Excel can still consolidate them provided that you’ve used the same labels to describe the data entries in their respective worksheets.
That way, Excel will update the value for you whenever you open the workbook.
Using the program’s Consolidate command button on the Data tab of the Ribbon, you can easily combine data from multiple spreadsheets.
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.
You can always copy data from one worksheet to another, but if the original value were to change, that change would not be reflected in the cell range to which you copied the data.
Rather than remembering which cells you need to update when a value changes, you can create a link to the original cell.
Most of the time, you want to total the data that you’re consolidating from the various worksheets.
By default, Excel uses the SUM function to total all the cells in the worksheets that share the same cell references (when you consolidate by position) or that use the same labels (when you consolidate by category).
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.
To do this select an empty sheet in the workbook - add a new one if necessary - and click in it.