When you do, Excel will add that range's address to the All References text box.
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.
When you do, Excel will summarize your data using the function you specified.
The resulting destination will be the same size and shape as the source ranges.
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.
For example, you can use the addition formula to find the average of those values.
Summarizing information by using formulas is handy because you can update the summary calculation simply by recalculating the worksheet.
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.
Using the Pivot Table Wizard makes this task fairly easy, but a pivot table might be overkill if all you want is a simple summary of data from multiple worksheets. This command—located on the Data menu—is quite versatile.