For example, to specify the data in range B4: R21 on Sheet1 in a workbook named In this example, Excel consolidates the range A2: R21 in Sheet1 of all versions of the workbooks that use “CG – Music – 20” in the main file when this name is followed by another two characters (be they 12, 13, 14, 15, and so on).When you consolidate data, Excel uses only the cells in the source areas that contain values.By Greg Harvey Excel 2016 allows you to consolidate data from different worksheets into a single worksheet.
If the source worksheets are open in Excel, you can specify the references of the source areas by pointing to the cell references (even when the Consolidate dialog box is open, Excel will allow you to activate different worksheets and scroll through them as you select the cell references for the source area).
If the source worksheets are not open in Excel, you must type in the cell references as external references, following the same guidelines you use when typing a linking formula with an external reference (except that you don’t type =).
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.
The ranges can exist in the same worksheet, in different sheets in the same workbook, or in different workbooks.
Before you begin, it's a good idea to open the workbook(s) containing the ranges you want to consolidate.
For example, the ranges shown in Figure A are on different worksheets in the same workbook.
If you want Excel to use a particular range in the worksheet for all consolidations you perform in a worksheet, assign the range name Consolidate_Area to this cell range.
Excel then consolidates data into this range whenever you use the Consolidate command.
However, even when the data entries are laid out differently in each spreadsheet, Excel can still consolidate them provided that you’ve used the same labels to describe the data entries in their respective worksheets.
Most of the time, you want to total the data that you’re consolidating from the various worksheets.
(This range is called the destination area.) If you select a single cell, Excel expands the destination area to columns to the right and rows below as needed to accommodate the consolidated data.