Excel 2000 in a Nutshell

Name

Data → Consolidate

Synopsis

The Data → Consolidate option provides the ability to merge data from multiple workbooks. For example, if you have multiple workbooks that contain the sales figures for the year, you can consolidate those into one file.

When you select Data → Consolidate the Consolidate dialog displays as shown in Figure 9-25. Before selecting this option make sure you have selected the top-left cell on the worksheet where you want the consolidation data to be placed. On the dialog you need to specify the workbook ranges that need to be consolidated and the type of consolidation that should be done, as outlined in the descriptions that follow.

Figure 9-25. Consolidate Dialog

Function

Select the type of consolidation that you want performed. Excel provides the different types of consolidation functions that can be selected. Typically the Sum option is selected. For example, if you consolidate two different workbooks that contain sales figures for the year, if Excel finds the sales figures for the same salesperson in both workbooks, those figures will be summed.

Reference

Specify the source of the data that you want to consolidate. You can either type the range in the field, use the Collapse Dialog button and select the range from an open workbook, or select the Browse button and locate the workbook that contains the data. As shown in Figure 9-25, you must specify the entire ...