Consolidating Data by Position

You consolidate worksheets by position when they use the same layout (such as those created from a template). When you consolidate data by position, Excel does not copy the labels from the source areas to the destination area, only the values.

To consolidate worksheets by position, follow these steps:

1. Open all the workbooks with the worksheets you want to consolidate. If the sheets are all in one workbook, open that file in Excel.
2. Create a new worksheet to hold the consolidated data.
   If you're consolidating the data in a new workbook, you need to open it (File → New). If you're consolidating worksheets generated from a template, use the template to create the new workbook.
3. Select the cell at the beginning of the destination area in the new worksheet or select the cell range if you want to limit the destination area to a particular region.
   If you want Excel to expand the size of the destination area as needed to accommodate the source areas, just select the first cell of this range.
4. Choose Data → Consolidate to open the Consolidate dialog box.
5. (Optional) Select the function you want to use in the Function drop-down list if you don't want the values in the source areas summed together.
6. Select the cell range or type the cell references for the first source area in the Reference text box.
   When you select the cell range by pointing, Excel minimizes the Consolidate dialog box to the Reference text box so that you can see what you're selecting. If the workbook is not visible in a window, choose it on the Window menu and then select the cell selection as you normally would. (Remember that you can move the Consolidate dialog box - the one that's been minimized to the Reference text box - by dragging its title bar.)
   If the source worksheets are not open, click the Browse button to select the filename in the Browse dialog box to enter it (plus an exclamation point) into the Reference text box. Then you can type in the range name or cell references you want to use. If you prefer, you can type in the entire cell reference including the filename. Remember that you can use the asterisk (*) and question mark (?) wildcard characters when typing in the references for the source area.
7. Click the Add button to add this reference to the first source area to the All References list box.
8. Repeat Steps 6 and 7 until you add all the references for all the source areas that you want to consolidate.
9. Click the OK button to close the Consolidate dialog box and have Excel consolidate the designated data.