When you do, Excel will summarize your data using the function you specified.
Don't bother including a source range's category labels when you specify the range—Excel won't include the labels in the destination.If you want to include the labels in the destination range, you'll have to copy them or enter them manually.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.Since the source ranges are the same size, you don't have to specifically select the entire destination range. command from the Data menu to open the dialog box shown in Figure B. Once you've selected the statistic you want Excel to calculate, you should specify the source ranges.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.
To begin, select Sheet4's tab and click on cell B2.
When you move to a different source worksheet, Excel will, by default, "suggest" the same range that you highlighted in the previous worksheet.
Therefore, if the data in each source range occupies the same cells, you don't have to highlight each range—you can simply click Add after activating the appropriate worksheet.
However, linking formulas may consume more memory than you want—especially when you're dealing with several large ranges of data.
Another way to summarize and manipulate data is by creating an Excel pivot table.
Although you can type the references, the easiest way to specify the source ranges is by selecting them.