Formulas You can use 3-D references in formulas for any type or arrangement of data. This is the recommended method.
Position If you're planning to combine data that's in the same cell in each of several ranges, you can consolidate by position.
Category If you have several ranges with different layouts, and you're planning to combine data from rows or columns that have matching labels, you can consolidate by category.
Consolidate the data with 3-D references or formulas
For example, to combine the data in cell B3 from worksheets Sheet 2 through Sheet 7 inclusive, you could type =SUM(Sheet2:Sheet7!B3). If the data to consolidate is in different cells on different worksheets, enter a formula such as this: =SUM(Sheet3!B4, Sheet4!A7, Sheet5!C5). To enter a reference such as Sheet3!B4 in a formula without typing, type the formula up to the point where you need the reference, click the worksheet tab, and then click the cell.
Consolidate by position or category
If you're consolidating by category, make sure the labels for columns or rows that you want to combine have identical spelling and capitalization.
If you're consolidating by category, select the check boxes under Use labels in that indicate where the labels are located in the source ranges: either the top row, the left column, or both. Any labels that don't match up with labels in the other source areas result in separate rows or columns in the consolidation.
Note Other ways to consolidate data include creating a PivotTable report from multiple consolidation ranges.