Consolidating information

To select the first source range, activate its workbook, click the tab for the appropriate sheet, highlight the range by dragging over it, and click the Add button.

For example, the upper-left cell of the destination range will contain the statistic for the values in the upper-left cell of the source ranges, the cell in the second row and first column of the destination range will contain the statistic for the values in the second row and first column of the source ranges, and so forth.

To demonstrate this technique, let's use it to consolidate the values in the worksheets we showed you in Figure A.

Specifically, we'll consolidate into cells B2: F6 in Sheet4 the data in cells B2: F6 in the 1994 Sales worksheet with the values in cells C3: G7 of the 1995 Sales worksheet and cells D4: H8 in the 1996 Sales worksheet.

As you can see, the data appears in the same order in ranges of the same size; however, the ranges begin in different cells: A1 in the 1994 Sales sheet, B2 in the 1995 Sales sheet, and C3 in the 1996 Sales sheet.

Consolidating by position involves a few easy steps: First, you click on the upper-left cell of the range in which you want Excel to place the summary information (we'll call this range the range). However, you can choose from a variety of other statistics (Count, Average, Max, Min, Product, and so forth) if you want.

Leave a Reply