If the ranges are in different workbooks, you must use book, sheet, and cell references.
If the source ranges are named, you can simply specify the ranges' names.
Common methods to consolidate in Excel include consolidating by position, by category, by formula or by using Excel’s Pivot Table feature.
Enabling this option tells Excel to copy all the data from the source ranges into the destination range in addition to entering the summary statistics.(We'll discuss that check box more in a moment.) At this point, click OK.If the ranges are in the same worksheet, you can simply specify the cell addresses.If the ranges are in the same workbook but in different worksheets, you must use sheet and cell references.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.
That is, you use it to count the values in multiple ranges or to find the sum, product, minimum, maximum, variance, standard deviation, or average of the values in a group of ranges.
In this article, we'll show you the simplest way to use the Consolidate... In a future article, we'll demonstrate a more complex use of the Consolidate... Consolidating by position Creating links to the source data Specifying multiple-cell destination ranges Conclusion You can consolidate by position if all the source ranges (the ranges containing the data you want to consolidate) are the same size and the data is arranged in the same order.
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.
The ranges can exist in the same worksheet, in different sheets in the same workbook, or in different workbooks.